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 2. Total comments: 122

  1. Hi,

    Please suggest formula to get working time beyond 24 hrs. Like in time 5:50 am in 21st jan, Out time 8:24 am next day. I need a formula which display total working hrs like 27 hrs 34 min.

      1. Hi is there any error in my message? which article you are suggesting? please share the link

        1. Its not working. calculation includes midnight span.

          IN TIME OUT TIME TOTAL WORKING TIME
          5:50 AM 8:24 AM 2:34:00

          I NEED TO PLAY 27[H]:34 [MM] in total working time cell.

          I used =IF(BN7>BO7,BO7+1,BO7)-BN7 this formula but its not working beyond 24 hrs.

          1. Hi!
            As far as I can see from your second comment, your task is now different from the original one. To get a difference of more than 24 hours, you need to use the date and time, not just the time.

  2. Is there a formula to take total hours worked in a year but only change the format for minutes to a decimal?
    Ex: 574:40 ([h]:mm cell format
    convert to 574.67 hours
    * I want to keep the hours the same but convert the number after the : to a decimal
    Thanks

  3. i have clock in and clock out as am to pm in one row like wise for a week ,i need to add total no of hours for that week FOR EXAMPLE
    ram
    9:30AM-2:30PM
    3:20PM-9:40PM
    1:30PM-9:50PM IN THIS WAY PER WEEK

    HOW TO USE FORMULA PLZ HELP ME.

    1. Hello!
      To convert text to time and find the difference, use the formula

      =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(MID(A1,SEARCH("-",A1,1)+1,50),"P"," P"),"A"," A")) -TIMEVALUE(SUBSTITUTE(SUBSTITUTE(LEFT(A1,SEARCH("-",A1,1)-1),"A"," A"),"P"," P"))

      Then you can find the sum of these values.

  4. I have a spreadsheet with accrued vacation leave and accrued floating holiday totals available. I need to let the employee know how much time they have to use by the end of the year so they don't loose any time. They can carry over 160 hours into the new year. Currently one field is formatted as text and the other total is formatted custom ( [h]:mm;@)

    1. Hi!
      Your description assumes that I know your details. But I don't know what "floating holiday totals" means, which field is formatted as text, and what is written in it. Describe in detail your data and what you want to calculate.

  5. Hii sir i need total hrs calculattion like this example. 102hrs:45mns + 01hrs:10mns = 103hrs:55 mns how to use the formula in excel

  6. i need the duration formula for 23:40 to 02:00

  7. Hi,

    How to filter data between date and time range as below:

    From Date:
    To Date:

    From Time:
    To Time:

    I have four criteria to extract data from that.

    Please advise.

    Kiruban

  8. Hi there, My system produces a data dump and shows a booking timeslot in the following format. 1330-2030 .I am currently using =RIGHT(D2,4) and =LEFT(D2,4) to get the values of 1330 in a seperate start column and 2030 in the end column.

    However there is colon : in this data, how can I
    A) Convert this into time
    B) Do a calculation on excel so that I can subtract both values (end time and start time) to get the booking time of 7 hours.

    1. Hello!
      Here is the formula that should work perfectly for you:

      =TIME(LEFT(D1,2),MID(D1,3,1),0)
      =TIME(MID(D1,6,2),RIGHT(D1,2),0)

      Hope this is what you need.

      1. Dear Alexander,

        Can you please help me to get the below requirement:

        Start Time Minutes Time should be
        2:00 PM 45 2:45 PM
        32 3:17 PM
        30 3:47 PM

        1. Hello!
          To add minutes to the time, divide the number of minutes by 1440.

          =C1+D1/1440

          C1 -- 2:00 PM
          D1 -- 45

  9. Hi,

    How do I subtract 30 minutes from 00:00 (12 midnight) using excel formula?

    1. Hello!
      The formula below will do the trick for you:

      =IF(A1<A2,A1+1-A2,A1-A2)

      I hope I answered your question. If something is still unclear, please feel free to ask.

  10. I have a spread sheet one cell has employee’s dispatch time (military time) example 23:59 (11:59 pm) the next cell has employee’s arrival time example 00:20 (12:20 am).
    Dispatch on one day, arrives a few minutes later (21 minutes) the following day.
    I have figured out the rest of the spread sheet with your help, however these two cells, as soon as I calculated them I get the death formula symbol of ####### any help would be appreciated.
    Thanks for your valuable time. Pat

  11. Good evening sir,
    I have downloaded a file from online and the time is in the format of 000:56:30, 0101:34:66, 132:43:00, 000:34:32, 0345:56:34, 0056:12:35 and so on in this format. While selecting the columns the task bar is showing count of numbers only but not sum of the time and the average. I have used TEXT(Value,"[H]:MM:SS") and after that used TIMEVALUE of that function and it is showing as #Value. I have tried using TIME(HOUR,MINUTES,SECOND) it is showing as #value. Please solve the above problem. I will post u file to Email also if you provide email. Pls solve it.Sir,
    I want to get total sum and sorting of these column in ascending or descending order sir.

    Hi,
    To convert the text 0345: 56: 34 to time, convert the text to a number. For example, if this text is written in cell A1, use the formula

    =--A1 you have given this reply sir.

    Sir I have used the formula it is giving the result #Value. Please provide your email I'd so that I can send file to you sir. Or please send me an email so as to send the file to you sir.

  12. I was able to come up with this formula however now I cannot find my duplicate. I think I've been looking at this for too long. Any suggestions??

    I'm coming up with: 2 months 18 daysG 18daysJ 22 hrs 18 min

    IF(L76936<60,TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysA ""h ""hrs"" m ""min""")),IF(L76936<3600, TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysB ""h ""hrs"" m ""min""")), IF(L76936<86400, TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysC ""h ""hrs"" m ""min""")), IF(L769361," daysD ", " dayE ") & TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysF ""h ""hrs"" m ""min""")), ROUNDDOWN(L76936/2592000,0) & IF(ROUNDDOWN(L76936/2592000,0)>1, " months ", " month ") & ROUNDDOWN(MOD(L76936,2592000)/86400,0) & IF(ROUNDDOWN(L76936/86400,0)>1," daysG ", " dayH ") & TEXT(MOD(L76936,2592000)/86400,CHOOSE(MATCH(L76936,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""daysJ ""h ""hrs"" m ""min"""))))))

    Thank you

    1. Did come up with this formula but now get a preceding 0 in days when I have 685984 seconds. I would like to eliminate the 0.

      Result: 07 days 22 hrs 33 min

      IF(ISERROR(IF(L76953<60,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")))),0,IF(L76953<60,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")),IF(L76953<3600,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")),IF(L76953<86400,TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")),IF(L769531," months "," month ")&TEXT(MOD(L76953,2592000)/86400,CHOOSE(MATCH(L76953,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""","d ""days ""h ""hrs"" m ""min""")))))))

      1. And 2424119 seconds displays as 028 days 1 hrs 21 min

  13. Could you please assist? I am trying to convert seconds to months, days, hours and mins however if it cannot be converted to months then days hours and mins will do. Currently, I have 6819518 seconds which comes to 2months 18days 22hours 18min or 78days 22hours 18min. This is the formula I have currently am using however it becomes an issue when there is more than 30 days. I tried changing 86400 to 2592000 but not yielding the results I need. Any help is greatly, greatly appreciated!!

    IF(ISERROR(TEXT(A1/86400,CHOOSE(MATCH(A1,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""days ""h ""hrs"" m ""min"""))), 0, TEXT(A1/86400,CHOOSE(MATCH(A1,{60,3600,86400},1),"[m] ""min""","[h] ""hrs"" m ""min""", "d ""days ""h ""hrs"" m ""min""")))

    1. Hello!
      To convert seconds to days, hours, minutes and seconds, use these formulas:
      days (in G3)
      =INT(E2/(60*60*24))

      hours (in H3)
      =INT(E2/(60*60))-G3*24

      minutes (in I3)
      =INT(E2/60)-G3*24*60-H3*60

      seconds (in J3)
      =--RIGHT(E2,2)

      I hope it’ll be helpful.

  14. Good evening sir,
    I have downloaded a file from online and the time is in the format of 000:56:30, 0101:34:66, 132:43:00, 000:34:32, 0345:56:34, 0056:12:35 and so on in this format. While selecting the columns the task bar is showing count of numbers only but not sum of the time and the average. I have used TEXT(Value,"[H]:MM:SS") and after that used TIMEVALUE of that function and it is showing as #Value. I have tried using TIME(HOUR,MINUTES,SECOND) it is showing as #value. Please solve the above problem. I will post u file to Email also if you provide email. Pls solve it.
    Thank you.

    1. Hello!
      Explain what result you would like to get from these text values - 0101:34:66, 132:43:00, 0345:56:34 ?

      1. Sir
        I want to have the sum of those column in time format and want to have sort in ascending or descending order

      2. Sir,
        I want to get total sum and sorting of these column in ascending or descending order sir.

        1. Hi,
          To convert the text 0345: 56: 34 to time, convert the text to a number. For example, if this text is written in cell A1, use the formula

          =--A1

          Please check out this article to learn how to convert text to number with formula and other ways.

          1. Sir I have used the formula it is giving the result #Value. Please provide your email I'd so that I can send file to you sir. Or please send me an email so as to send the file to you sir.

            1. Hello!
              Use the following formula:

              =IF(LEFT(A1,2)="00",--MID(A1,3,20),--A1)

              Please note that 0101:34:66 is not the time. There are no 66 seconds.

  15. I am looking to identify the total minutes for the following that has been split into days, hours, minutes, seconds: in the format of 01:04:56:05
    01 - in theory 24 hours
    04 - 4 hours
    56 - minutes
    05 - seconds
    I should be able to see: 1736 minutes

    1. Hello!
      To convert hours into minutes, you must first multiply the specified time by 60 (the number of minutes in one hour), then by 24 (the number of hours in one day). In other words, we need to multiply time by 1440.
      I believe the following formula will help you solve your task:

      =TIMEVALUE(MID(A1,4,20))*1440+60*24*(--LEFT(A1,2))

      Hope this is what you need.

  16. How to add 6 hour 24 min 40 secs + 2 hours 39 min 30 secs

  17. Hi excel specialist
    How can I change from excel format from 24-00 to 24.00. what is the formula
    Pls. advise thanks in advance hope to hear from anyone.

  18. Setting up a time card, I have 9 columns, 3 sets of IN time and OUT time. I can get columns to add up using a 24 hour format when it's a low vs a high hour such as IN:0600 / OUT:1600 for a total of 10 hours. However, what I can't figure out is how to formulate In:2000 / OUT:0600 for a total of 10 hours as the amount of time worked was 10 hours.

    I know out how to get it to auto sum hours worked when I format the cell (mm/dd/yyyy h:mm) but that over populates the time card and makes it a lot harder to read. Is there a way to auto calculate the total hours worked using (h:mm) and auto sum the 3 column sets into a single total hours worked regardless of what day and time they clocked in?

    My crews work 24 hour rotating shifts logging between 8 to 14 hours at a time. They also check out between the start and end of the shift and have to clock back in.

    For Example; IN-19:00 (Begin Shift) OUT-24:00 (30 Min Meal) / IN-00-30 OUT-0330 (30 Min Rest) / IN-0400 OUT-0600 (Time to go Home) Total time worked: 10 Hours

    I want to set up a time card they can just put in the time they clock in and the time they clock out to simplify their lives. It'll also help keep from displaying unnecessary data for our finance clerk and speed the processing of payroll up.

    1. Hello Rick!
      If the end time is longer than the start time, then you need to add 1 day to their difference

      =IF(A1 > A2,A2-A1+1,A2-A1)

      If you need the number of hours to be more than 24, use the custom format [h]:mm:ss.
      I hope this will help, otherwise please do not hesitate to contact me anytime.

  19. Hi,
    I have a data dump from a scheduling system that displays the time in excel as follows:
    1.00:48:34 - I am reading this as 1 day, 48 minutes, 34 seconds.
    1.10:19:09 - 1 day, 10 hours, 19 minutes and 9 seconds.
    When I try to add these cells in Excel, I get nothing. What time format can I convert these to and/or what formula could I use?

    1. Hello Alan!
      If I got you right, the formula below will help you with your task:

      =LEFT(A1,FIND(".",A1,1)-1)+TIMEVALUE(RIGHT(A1,8))

      Please go to Format Cells, choose Number -> Custom Format and set

      d"d."h"h."mm"m."ss"s."

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  20. Hi, I'm trying to subtract 2 hours from 01:30:00. It should give me 23:30:00 but I keep getting an "#########" answer.

    1. Hello Stan!
      If the end time is greater than the start time, the following formula should work for you:

      =IF(B3 > B1,B1-B3+1,B1-B3)

      To the start time you need to add 1 day.
      I hope it’ll be helpful.

  21. How do I get 1 hour to show as 60 minutes in excel? Example: it shows 1:00:00.0 currently but would like it to show 60:00.0 instead.

    1. Hello Adam!
      Please go to Format Cells, choose Number -> Custom Format and set
      [mm]:ss.0;@

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  22. HOW CAN I CONVERT 110.5HRS TO DAYS / HOURS / MINUTES IN EXCEL FORMULA

    PLEASE HELP

    1. Hello Jerry!
      Please use the following formula
      =A1/24
      Then go to Format Cells, choose Number -> Custom Format and set
      dd:hh:mm;@
      or
      dd"d." hh"h."mm"m.";@

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

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

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

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

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

  26. Hi there!

    How should i add running clock in excel?

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

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



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)