Show time over 24 hours, 60 minutes, 60 seconds in Excel

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 cell(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:

  • 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:

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:

  • 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

  1. 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!

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

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

  4. ‌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

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

  6. 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?

  7. How to convert 47:00:00
    Having the cell's format: [hh]:mm:ss;@
    To 47
    Having the cell's format: number or text

  8. 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!

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

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

  11. 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!
        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

          • Thanks a lot that helped in most smooth way!

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

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

  14. 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!

  15. 1M
    1M
    45M 10S
    2H 28M 21S
    -
    -
    3H 20M 14S

    I want an average sheet like the above data in excel (for full column)

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

  17. Total activation duration (minutes)

    18504:06

    which formula to do use to convert this unto hours, minutes & seconds ?

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

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

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

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

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)