How to add & subtract time in Excel to show over 24 hours, 60 minutes, 60 seconds

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:

  1. Select the sell(s) you want to format.
  2. Right click the selected cells and then click Format Cells, or press Ctrl + 1. This will open the Format Cells dialog box.
  3. 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:
Display a time interval over 24 hours

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:

(End time - Start time) * 24

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).

(End time - Start time) * 1440

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).

(End time - Start time) * 86400

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:
Calculating time difference in hours, minutes, or seconds

Notes:

  1. For correct results, the formula cells should be formatted as General.
  2. 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:

Start time + (N/24)

Add over 60 minutes:

Start time + (N/1440)

Add over 60 seconds:

Start time + (N/86400)

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:
Add over than 24 hours, 60 minutes, 60 seconds

To subtract times in Excel, use similar formulas but with the minus sign instead of plus:

Subtract over 24 hours:

Start time - (N/24)

Subtract over 60 minutes:

Start time - (N/1440)

Subtract over 60 seconds:

Start time - (N/86400)

The following screenshot shows the results:
Subtract over than 24 hours, 60 minutes, 60 seconds

Notes:

  1. If a calculated time displays as a decimal number, apply a custom date/time format to the formula cells.
  2. 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!

You may also be interested in:

21 Responses to "How to add & subtract time in Excel to show over 24 hours, 60 minutes, 60 seconds"

  1. larry says:

    I believe you have an error in the section "How to add / subtract more than 24 hours, 60 minutes, 60 seconds".
    "Here're a few real-life formula examples:
    To add 45 hours to the start time in cell A2:
    =A2+(30/24)"

    the formula should be =A2 + (45/24)

    the other info was helpful

  2. Imran Masud says:

    Thanks, Svetlana.

  3. rohan says:

    Hi Svetlana, This is very useful and very informative. I have some doubt if have time 12:30 and i apply formula as A2+(2/25) then it decreases the time by 5 Minutes and gives 14:25 time and if apply A2+(2/23) then it increases the time by 5 Minutes and gives 14:35. Could please give me clarity on this how does it calculate?

    Thanks, Svetlana again!!

    • Hi Rohan,

      I don't know the reasoning behind your calculations, but the results are correct. As you probably know, in internal Excel system, times are stored as decimal fractions, e.g. 00:00:00 is stored as 0.0 and 23:59:59 is stored as 0.99999. So, in terms of times, 2/25 (0.08) is 1:55, when added to 12:30, it gives you 14:25.

      For more information, please see Excel time format.

  4. rohan says:

    Thanks Svetlana

  5. Indra says:

    Hi there!

    How should i add running clock in excel?

  6. Isaiah says:

    Thank you so very much. Much appreciated

  7. pravin says:

    how to without mention date calculation hours check for example 23:00PM -05:30 =06:30

  8. MrsH says:

    Hi, I need to take a monthly decimal total number of hours worked (i.e. 160.833333) and display as "X hours and Y minutes" which should be 160 hours and 50 minutes, where 49.998 minutes is rounded to the nearest whole minute. I am tearing my hair out.
    thanks.

  9. v thyagaraj says:

    I need to subtract for ex,
    10:10 hours from 24 hours,
    what formula should i need to use,

    Warm regards,
    Thyagaraj V

  10. Lee Y C says:

    suppose I need to add another 30mins to 17.40 hrs. The answer should be 18.10 hrs. Can help me to create a fomula

  11. Craig says:

    I am compiling a running total of generator running time. I have start and end times in Columns B & C, with format: mm/dd/yy hh:mm. I subtract C from B with formula:

    =INT($C4-$B4)&" : "&HOUR(MOD($C4-$B4,1))&" : "&MINUTE(MOD($C4-$B4,1))

    Results go into Col. D with results formatted as dd: hh: mm. So far so good, but then I add the individual running times to get cumulative running times in Col. E, and even this works until I get to where the hours exceed 24. They don't add to the days. Example:

    2: 23: 43 (d: hh: mm) plus 30 minutes displays as 2: 24: 13 but it should be 3: 0: 13. What do I do to get the correct display?

    Thanks,

    Craig

  12. BB says:

    Say my start time is 18:00 hours today and my end time will be 12:00 hours next day. Then how to I calculate the total hours duration (hh:mm format) and also how to get it in decimal format for other calculations (may be y*24).

    Many thanks for your kind help.

  13. BB says:

    I got my answer i.e. 24+(24*(endtime-starttime) = no. of hours difference. I hope I am right in this.

    Thanks

  14. Khin Lae says:

    I'm calculating staff OT for 5PM onward and just wanna show 30minute and above in the cell. Which function can be useful?

    If end time is 8:30 PM, i wanna see in the cell is 3:30 as OT and If the end if only 8:15PM. I wanna see in the cell is 3:00 a OT

  15. kapil says:

    Sir,
    I want to add 1 hour 30 minutes + 2 hour 50 min in XL sheet.please help me by the formula.

  16. Natty says:

    When I was doing the formula to add how many hours working in hours:minutes, for example 1.17 one day and next day 0.59 total should be showing 2.26 but formula is showing 1.76. How to make a formula if anything over 60 minutes should become a hour?

  17. nandu says:

    plz solve this problem.
    how to calculate 24hours working format....

    the labours check in 10-09-2018 5:44AM and they check out next morning 11-09-2018 4:58AM....

    BREAK HOUR 2:30Hr
    NORMAL WORKING HOUR 08:00Hr....

  18. Jolyon Ticer-Wurr says:

    Thank you. I figured out how to sum times in Excel, and I've almost figured out how to merge them into a Word document. I say "almost" because Word doesn't seem to be able to handle summed times greater than 24 hours (I'm summing total hours worked).

    I've tried every "switch" I can think of, and once the hours:minutes (32:12) goes beyond 24 hours, it will not longer display properly. Is it possible to merge such information to Word and have them display as they display in Excel? (I've not tried using a DDE link because it seems that Microsoft is moving away from this so I didn't want to get attached to something that will be phased out).

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
40
Ultimate Suite 2018.5 for Excel
40
Christmas sale
Best Price of the Year. Dec. 18 – 28