The article shows a few tips to calculate and display times that are greater than 24 hours, 60 minutes, 60 seconds.
When subtracting or adding time in Excel, you may sometimes want to display the results as the total number of hours, minutes or seconds. The task is a lot easier than it may sound, and you will know the solution in a moment.
How to display time over 24 hours, 60 minutes, 60 seconds
To show a time interval of more than 24 hours, 60 minutes, or 60 seconds, apply a custom time format where a corresponding time unit code is enclosed in square brackets, like [h], [m], or [s]. The detailed steps follow below:
- Select the cell(s) you want to format.
- Right click the selected cells and then click Format Cells, or press Ctrl + 1. This will open the Format Cells dialog box.
- On the Number tab, under Category, select Custom, and type one of the following time formats in the Type box:
- Over 24 hours: [h]:mm:ss or [h]:mm
- Over 60 minutes: [m]:ss
- Over 60 seconds: [s]
The following screenshot shows the "over 24 hours" custom time format in action:
Below are a few other custom formats that can be used to display time intervals exceeding the length of the standard time units.
Description | Format code |
Total hours | [h] |
Hours & minutes | [h]:mm |
Hours, minutes, seconds | [h]:mm:ss |
Total minutes | [m] |
Minutes & seconds | [m]:ss |
Total seconds | [s] |
Applied to our sample data (Total time 50:40 in the screenshot above), these custom time formats will produce the following results:
A | B | C | |
---|---|---|---|
1 | Description | Displayed time | Format |
2 | Hours | 50 | [h] |
3 | Hours & minutes | 50:40 | [h]:mm |
4 | Hours, minutes, seconds | 50:40:30 | [h]:mm:ss |
5 | Minutes | 3040 | [m] |
6 | Minutes & seconds | 3040:30 | [m]:ss |
7 | Seconds | 182430 | [s] |
To make the displayed times more meaningful to your users, you can supplement the time unites with the corresponding words, for example:
A | B | C | |
---|---|---|---|
1 | Description | Displayed time | Format |
2 | Hours & minutes | 50 hours and 40 minutes | [h] "hours and" mm "minutes" |
3 | Hours, minutes, seconds | 50 h. 40 m. 30 s. | [h] "h." mm "m." ss "s." |
4 | Minutes | 3040 minutes | [m] "minutes" |
5 | Minutes & seconds | 3040 minutes and 30 seconds | [m] "minutes and" ss "seconds" |
6 | Seconds | 182430 seconds | [s] "seconds" |
Note. Although the above times look like text strings, they are still numeric values, since Excel number formats change only the visual representation but not the underlying values. So, you are free to add and subtract the formatted times as usual, reference them in your formulas and use in other calculations.
Now that you know the general technique to display times greater than 24 hours in Excel, let me show you a couple more formulas suited for specific situations.
Calculate time difference in hours, minutes, or seconds
To calculate the difference between two times in a specific time unit, use one of the following formulas.
Time difference in hours
To calculate hours between the start time and end time as a decimal number, use this formula:
To get the number of complete hours, utilize the INT function to round the decimal down to the nearest integer:
=INT((B2-A2) * 24)
Time difference in minutes
To calculate minutes between two times, subtract the start time from the end time, and then multiply the difference by 1440, which is the number of minutes in one day (24 hours*60 minutes).
Time difference in seconds
To get the number of seconds between two times, multiply the time difference by 86400, which is the number of seconds in one day (24 hours*60 minutes*60 seconds).
Assuming the start time in A3 and end time in B3, the formulas go as follows:
Hours as a decimal number: =(B3-A3)*24
Complete hours: =INT((B3-A3)*24)
Minutes: =(B3-A3)*1440
Seconds: =(B3-A3)*86400
The following screenshot shows the results:
Notes:
- For correct results, the formula cells should be formatted as General.
- If the end time is greater than the start time, the time difference is displayed as a negative number, like in row 5 in the screenshot above.
How to add / subtract more than 24 hours, 60 minutes, 60 seconds
To add a desired time interval to a given time, divide the number of hours, minutes, or seconds you want to add by the number of the corresponding unit in a day (24 hours, 1440 minutes, or 86400 seconds), and then add the quotient to the start time.
Add over 24 hours:
Add over 60 minutes:
Add over 60 seconds:
Where N is the number of hours, minutes, or seconds you want to add.
Here're a few real-life formula examples:
To add 45 hours to the start time in cell A2:
=A2+(45/24)
To add 100 minutes to the start time in A2:
=A2+(100/1440)
To add 200 seconds to the start time in A2:
=A2+(200/86400)
Or, you can input the times to add in separate cells and reference those cells in your formulas like shown in the screenshot below:
To subtract times in Excel, use similar formulas but with the minus sign instead of plus:
Subtract over 24 hours:
Subtract over 60 minutes:
Subtract over 60 seconds:
The following screenshot shows the results:
Notes:
- If a calculated time displays as a decimal number, apply a custom date/time format to the formula cells.
- If after applying custom formatting a cell displays #####, most likely the cell is not wide enough to display the date time value. To fix this, expand the column width either by double-clicking or dragging the right boundary of the column.
This is how you can display, add and subtract lengthy time intervals in Excel. I thank you for reading and hope to see you on our blog next week!
196 comments
I have a column of total hours worked in a year and want to add them together but excel keeps auto reformatting it even if I change it manually.
e.g. 762:55:00 gets reformatted to 1/31/1900 6:55:00 or 18:55:00.
Is it possible to add the total times without it changing the format?
Thanks!
Hello Dillon! Have you tried the methods described in this blog post? Use custom time format
[h]:mm:ss
I have a sheet with about 500 rows containing in one column values such as "32 minutes" or "6 hours 44 minutes". I need to calculate the sum of these values.
Please help
Hi! Text that does not have a common pattern cannot be converted to time using a single formula. You need 2 different formulas for your data.
Hello,
I wish to create a table which contains: A cell with minutes (e.g. 45min) to be added to another cell containing the time (e.g. 10:30) and in another cell it will calculate the answer as new time being the 45min passed after 10:30 (e.g. 11:15), is this possible?
Thanks and regards
Godwin
Hi! If I understand your task correctly, this article may be helpful: Calculate time in Excel: time difference, add, subtract and sum times.
Hi
I have two cells that consist of two different times consisting of five digit hours and two digitv minut and I want to sebtract them but it doesn't work.
A1=10507:54
B1=9962:01
I need A1-B1
Hi! A1 is text. Excel cannot write a number of hours greater than 9999.
I have exported data into a sheet where the the TIME reads Hours[space]mins:sec:?? example: 602 59:00:00
However I would like it to remove the space and delete the extra :00 off the end, to read [hh]:mm:ss only
Please help :-)
Hi! Set the custom time format you want. You can see examples in the article above and also here: Excel time format: 12/24 hour, custom, default. Try to use the time format that you wrote in your question - [hh]:mm:ss
I want to do conditional formatting on cells with time. I have a column that advances time by one minute using this formula =B22+(1/1440). I have another cell that calculates 3 hours from another time entered elsewhere, so that E5 is manually entered as a time by the user, and G5 is =E5+(3/24) to advance G5 3 hours. I want my B column to highlight the minute that matches G5. I can make it work, but for some minutes it is not true. I have compared the values and it seems the code for the time is off for calculating the minute sometimes. For example 0.17430555555555500000 0.174305555555556000 FALSE,
both represent 04:11 in B137 and G5 respectively, but as these are not equal the conditional formatting will not apply.
I have tried advancing time in G5 using +(180/1440) to see if it would improve accuracy and will still have a problem with some cells formatting. I have changed 180 to various lengths of time to see the formatting occur and will notice that it fails at some point in time. Is there a way to overcome or fix this time code discrepancy that seems to be causing the formatting to fail?
Hi! Compare hours and minutes in the conditional formatting formula instead of decimals. Extract the desired data from the number using the HOUR and MINUTE functions.
=AND(HOUR(B22)=HOUR($F$5),MINUTE(B22)=MINUTE($F$5))
This should solve your task.
How to convert 47:00:00
Having the cell's format: [hh]:mm:ss;@
To 47
Having the cell's format: number or text
Hi! To convert time to number of hours, multiply by 24. For more detailed instructions, see this article: How to convert time to decimal number, hours, minutes or seconds.
Reply is Respected
Hi, I have cell G6 with this formula
=HOUR(F6-E6)&"hours,"&MINUTE(F6-E6)&"minutes,"&SECOND(F6-E6)&"seconds"
and cell P6 with this formula
=HOUR((O6-N6)+(M6-L6)+(K6-J6)+(I6-H6))&"hours,"&MINUTE((O6-N6)+(M6-L6)+(K6-J6)+(I6-H6))&"minutes,"&SECOND((O6-N6)+(M6-L6)+(K6-J6)+(I6-H6))&"seconds"
I want to subtract G6-P6 to get remaining hours but I get #VALUE! How do I solve this?
For context cell in G6 formula is for wake up time minus sleep time , that displayed date & time eg 2/3/23 1:30 AM.
While cell P6 total time of deep sleep time happens during sleep. (Deep sleep happens multiple times, in this case 4 times, hence the lengthy formula)
How do I make the formula for total sleep minus deep sleep? That will display hours, minutes & seconds?
I’ve tried TIME(G6,G6,G6) - TIME(P6,P6,P6) but I get #VALUE!
Hello! Your cells G6 and P6 contain text. Therefore, you cannot perform mathematical operations with these values.
I have been trying and trying to subtract a time in a 24 hour period.
I need to subtract 7:14 from 24:00.
Steve
Hello! The answer to your question can be found in this article: Calculate time in Excel: time difference, add, subtract and sum times.
What formula I need to used for this?
Start time Stop Time total
1230 1600 3.5
To convert the time to a decimal number, multiply it by 24. Pay attention to the following paragraph of the article above: Calculate time difference in hours, minutes, or seconds.
how can i add them in hh:mm:ss
00:01:13
00:01:12
00:00:02
00:00:02
we can say they are call duration by a given candidate and we want to add total time
Hi!
The answer to your question can be found in this article: Adding and subtracting time in Excel.
Hi!
But the actual problem is that the excel is not taking it as time or anything instead it is taking it like count.
Even after using format cell option i'm not able to solve it using it.
Though I solved it by changing it into seconds first, then again changed it into hh:mm:ss format then it worked
Hi! If your data is written as text, use the TIMEVALUE function to convert it to time.
Thanks a lot that helped in most smooth way!
I wants to convert Time column which is in 1 hr, 2hr, 3hr, etc and wants to convert it to 12:00, 12:05, 12:10, 12:15 like in 24 hours format.
in short to convert the number of hours to time measurement in 24 hour format.
Hi! If I understand your task correctly, the following tutorial should help: How to convert numbers to time format in Excel.
Hi everybody
I have an excel template to calculate enzyme activity. This is the formula they used to convert enzyme activity measured activity in 30 minutes to activity per hour:
=C51*2*Scale/concentration/dilution factor/A/24
where A is the difference between the starting time and the measuring time
1:31:44 PM-1:00:30 PM=00:31 (A=0:31 minutes)
my question is related to this part /A/24
why they divided the values by 24?
I would be very thankfull if someone can help me.
Bests
Hello!
If A is the number of hours, then dividing by 24 converts to the time format. See here for more details: How to convert numbers to time format in Excel.
A is 30 minutes
Hi All,
At the excel sheet, I want to change time format, for example, from 1:00PM of 12 hour format in to 13:00:00 of 24 hour format. I tried all the options at "format cells" but i could not succeeded. Can any body remind me if i missed something or help me please?
Thanks!
Hi!
We have a special tutorial on this. Please see: Excel time format: 12/24 hour, custom, default.
I hope it’ll be helpful.
1M
1M
45M 10S
2H 28M 21S
-
-
3H 20M 14S
I want an average sheet like the above data in excel (for full column)
Hi, can please assist if i got the below info
07:27alt enter18:04 in one excel
how can i separate it in to excel,
i wish to separate it out to two excel in time format so i can subtract it to calculate the working time and calculate any OT allowance
Hello!
Please have a look at this article: How to split string by line break in Excel.
Then use the TIMEVALUE function to get the time.
=TIMEVALUE(LEFT(A1,SEARCH(CHAR(10),A1,1)-1))
=TIMEVALUE(RIGHT(A1,SEARCH(CHAR(10),A1,1)-1))
I hope this will help.
Total activation duration (minutes)
18504:06
which formula to do use to convert this unto hours, minutes & seconds ?
Hi!
I recommend reading this guide: How to convert time to decimal number, hours, minutes or seconds in Excel.
Try this formula:
=INT(LEFT(A1,SEARCH(":",A1)-1)/1440) & " days, " & HOUR(LEFT(A1,SEARCH(":",A1)-1)/1440) & " hours, " & MINUTE(LEFT(A1,SEARCH(":",A1)-1)/1440) & " minutes and " & RIGHT(A1,2) & " seconds"
The [h]:mm:ss fix doesn't work, completely. While it displays correctly in the cell, if I double click it to edit it it reverts back to "1900-01-02 00.32:02" and it says so in the form editor window above the sheet section before and after double clicking. This is only on cells that have more than 24 hours. The rest look fine either way.
Hello!
Unfortunately, I was unable to replicate your issue. Maybe this article will be helpful: Excel AutoCorrect: how to customize or turn off.
To replicate this, do not use the custom number format on cells with formula. Use it on blank cells and then enter the value manually in those cells.
For example, let's say column C is where the user is required to enter the number of hours and minutes spent on a task - manually. So if I spent 35 hours and 15 minutes on that task, I will enter 35:15.
Now, we want that to be entered exactly as such and to stay in number format (so sum function should work in the end to calculate the total of column C) in hours and minutes format. So here text will not work and numbers will also not work as that will not convert the minutes into hours properly. Decimals will also not work here.
So is there a way to do this in excel?
Hello!
When editing a cell, Excel automatically changes the format to date-time. However, you can manually revert the format to [h]:mm:ss. This does not affect the calculation of the sum of time and the sum is calculated correctly.
Hello. I am experiencing the exact same issue as Meet detailed and is not resolved by changing the format to [h]:mm:ss.
I've entered 71:48:00 into a cell. Excel shows this as 23:48:00, even when manually changing the formatting. This means all calculations based on the time are calculating on 23 hours, not 71. Are there any other tips to force Excel to show the actual time rather than 23:48:00?
Hello!
I can't guess why this [h]:mm:ss format is not working for you. Try using Format cells - Time, and select the format 37:30:55. Hope that helps.
I need to work out a formula to count down a 24hours clock example
Start time end time time elapsed Time remaining Time clock 24 clock runs out
19/10/22 - 00.55hrs 19/10/22 - 12:05hrs 12hrs 00 mins 12 hrs0 mins 20/10/22 - 00.55hrs
28/10/22 - 1500hrs 28/10/22 - 2100hrs 6hrs 00 mins 6 hrs 0 mins 28/10/22 - 2100hrs
30/10/22 - 1700hrs 30/10/22 - 2100hrs 4hrs 00 mins 2 hrs 0 mins 30/10/22 - 1100hrs
31/11/22 - 1700hrs 2 hr 0 mins 31/10/22 - 1900hrs
This will continue for a further 4 lines of code to.
Each entry is linked to the previous entry to count down the 24hrs clock
Does anyone know how to write this formula please
thanks
Hi!
Unfortunately, I can't understand what you want to count. Describe in detail what result you want to get from your data. As it's currently written, it's hard to tell exactly what you're asking.
i'd like to start a count down of hours left - 3000 hours minus what cell B2 shows. Can you please share how to do this?
much appreciated.
Hi!
Excel stores times as numbers. Use normal value subtraction.
I recommend reading this guide: Calculate time in Excel: time difference, add, subtract and sum times.
thank you!! :)