Comments on: Show time over 24 hours, 60 minutes, 60 seconds in Excel

When subtracting and adding time in Excel, you may sometimes want to display the resulting time intervals over 24 hours in the hh:mm format, over 60 minutes in the mm:ss format, and over 60 seconds in the ss format. The task is a lot easier than it may sound, and you will know the solution in a moment. Continue reading →

Comments page 5. Total comments: 202

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

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

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

  4. if value of a1 is 2 and value of p1 is 15how to fine vale of b1 c1 d1 e1

  5. This isn't working for me. When the total hits 24 hours it starts over; adding time that totals 28:30 shows a total of 4:30.

    I am certain both the cells I am adding and the cell I am putting the total in are formatted as custom h:mm.

    I am using Excel on an Office 365 subscription; Excel for Mac, version 15.32.

    Do you have any further guidance?

    1. Hi Josh,

      To get an "over 24 hours" custom format, you should enclose the hour code in square brackets. So, the total cell should be formatted [h]:mm

      1. Svetlana,
        i got it, thanks

      2. Hi Svetlana, can you give an exemple please? thanks

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

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

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

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

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

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

    Thanks

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

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

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

    Warm regards,
    Thyagaraj V

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

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

  18. Thank you so very much. Much appreciated

  19. Hi there!

    How should i add running clock in excel?

  20. Thanks Svetlana

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

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

  22. Thanks, Svetlana.

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

    1. It worked!

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