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!
200 comments
I have 4 machines, which run for few hours in a shift,I note down the number of hours of breakdown/ idle time (In hours) for each machine individually. At the end i want to calculate run time of "all the machines combined" by subtracting the " SUM of idle times of all machines" from 32 hours.
Please suggest a suitable formula/ technique for the same
Hi I am trying to create a time sheet that displays elasped time between start time and end time. I have times that cross over 24 hours, where the end time is smaller than the start time. I have been using =mod(end time - start time,1). However this does not work when subtracting a smaller end time from a start time on another date. Is there one formula that I could use for all types of subtraction of time that would also capture the smaller end time - larger start time crossing over 24 hours?
I am trying to show my kid when he improves in his swimming times. It is all good when he improves. Formula works great. But, if he did not improve the formula does not work. For example: last month it took him 00:36.55 (milliseconds) to swim 50 yards. This month it took him 00:35.70. So, he improved 00:00.85.
But, if the numbers are opposite and he did worse the formula will not give me the negative number I am looking for. 00:35.70 - 00:36.85 = -00:00.85
All cells are formatted mm:ss.00;@
Is there a way to show a negative number in time when deducting?
When I use custom format to represent times greater than 24 hours, the [h]:mm:ss displays 575:00:00.
Why the addition of 551 hours to the total?
Because the [h]:mm actually requires that you convert the total hours to DAYS.fraction. So take (calculated hours)/24. to display with [h]:mm format
this is a time related query.
when i do 23:59 - 23:30, i get an answer as 29 minutes formula=(b1-a1)*1440
when i do 00:15 - 23:59, i get a negative with same formula, this is the problem.
solution required = how do i get the number of minutes? which should be 16 minutes.
Look forward to getting a solution, thanking everyone for their inputs!
:)
I have a question, If I have 01:40am and i want to minus 20:55:00pm what is the correct formula I need in order for it to see I have gone into the next day? I have tried and it will give me a -19... I need it to see that it is a 24hr period
Hi, I had a similar problem working with time. For 24 hours Excel stores this as 1 day, 0 hours. My solution was to convert everything to seconds and work from there. If you have formatted as days, multiply by 86400 (seconds in a day), if you have formatted as hours, multiply by 3600 (seconds) in an hour, etc.
Hi, i need help please. I have data that auto loads daily at 5pm. The process is broken into 2 parts (ETL1 and ETL2). ETL1 starts at 5pm till 11pm and ETL2 starts at 11 till 4 am. at 7 am i run a script to check if everything ran. each row has a start and end date time. i would like to flag all data after 5pm yesterday as todays data. Currently when i filter on today, i only see the rows where the date is after 00:00
I got it
Hi, I am trying to produce an hourly blood pressure chart in Excel. How do I enter and how do I display the hourly entry of each observation please?
Hi,
I am trying to get a series of value for a column with datetime datatype showing seconds over 60 seconds. For Instance, take the example as "23-06-2019 14:30:59". When I am trying to use the custom format as "dd-mm-yyyy hh:mm:[s]" then it is giving error and throwing some decimal number for "23-06-2019 14:30:60" or "23-06-2019 14:30:61". Can anyone please help me out to get the correct logic for the same
Thanks!
Im really confused because this all seems like it should be easy but I just dont get the correct result.
Example: Start time is 08:40 (A1)
Current time is 10:45 (B1)
Time difference 02:05 (C1)
in D1 I have =C1*1440 but the result is 62,860,445
and I know thats not how many minutes are between 8:40 and 10:45
My Current time cell uses =NOW(), could that be the issue?
Many thanks
Hi,
I am trying to get the difference of the total breaks that my agent is taking. Can anyone tell me here the exact formula in google sheets for this pleaseee 60 minutes minus 00:55:00+00:15:23+00:24:56.
Hi Romel,
if I get it correctly, I believe you can enter these values to your cells (for example, 00:60:00 to A1; 00:55:00 to A2; and so on), apply Duration format to them, and create a simple formula like this: =(A2+A3+A4)-A1
Then set Duration format to the resulting cell as well and you'll see a difference of 00:35:19.
Hi,
I have this two time duration in text format(HH:MM:SS). I am trying to add both and find the total time duration of the activity. However the values are not reflecting correctly. It is showing as 0:00 only.
01:40:00
00:05:00
Can anyone please me in understanding the issue.
how to calculate total running hours
ex:
last month running Hrs. xxxx (format cell - [H]:mm)
but if I enter more then 4 digit total running Hrs. cell show #VALUE!
Last Month R/HRS 1358:06
TOTAL R/HRS 1358:06
TOTAL ACCUMULATED RUNNING HRS 1358:06 [=SUM(B31+B32)]
But if enter 5 digit show #VALUE!
Last Month R/HRS 13580:06
TOTAL R/HRS 13580:06
TOTAL ACCUMULATED RUNNING HRS #VALUE! [=SUM(B31+B32)]
Please help me to resoles this error
I am trying to figure out how to add a remainder of time to a total.
Example: time in 8:00 am, time out 5:00 pm = total time 9:00 hours. Allowed 0:30 minute paid break but went over (0:47). How do I add the 0:17 minutes to the total time if allowed time goes over 0,30,0
Thank you
Hi, i have a questions,
Start date 31/1/2019 10:00:00am and End Date 01/02/2019 11:00:00am
i tried to minus =End date-Start date, but it shows me #value! anyway to solve this?
The cell i need it formula to be [h]:mm:ss
I searched all over youtube for a formula to show lapsed time from start time and couldn't find the answer. I knew there must be some simple formula, and you had it. Great directions, down to the important details. Many thanks, I took a couple hours trying to find the answer and in five minutes you showed me how. THANK YOU Svetlana for your help. Excel angel!
if i create 1st date column 2nd, 3rd time column and 4th column again date. if we add 2nd and 3rd column times and get less than 24hrs then 4th column should reflect same date as of 1st column if it is more than 24hrs then 4th column date should be one more tha 1st column. please suggest any formula
thanks in advanvce.
I am trying to subtract talk time from one year over the next to see if there was a reduction or increase. It works fine if the time decreases but if there is an increase I get the below. 15:01-17:32
give me ######## How do I get it to subtract and give me a negative result