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!

Table of contents

186 comments

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

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

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

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

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

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

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

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

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

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

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

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

  12. Total activation duration (minutes)

    18504:06

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

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

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

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

  16. I have a report that gives seconds and I need an excel formula that will display that as HH:MM:SS - Anything over 86400 displays improper number of hours. Thanks

    • Go it - Divide by 86400 and format as {h}:MM:SS -

    • Hi!
      Did you use the [h]:mm:ss time format as recommended in the article above? How many hours do you get and what do you want to get?

  17. Hi, I've date-time format like 15-03-2020 01:50, Now I want to display the time as 13:50 and so on.. the problem is these times are autogenerated and don't have AM or PM. How I need to accomplish.

  18. Thank you very much for explaining this topic in a very simple way!
    This helped me alot. :)

  19. Hi,
    Iam working in aviation field, because of that i need to calculate the larger values in hours.
    For eg: 12346:00 - 12345:00, I know that answer should come as 1 hour.
    Instead, its showing as "#VALUE!".

    Please suggest some formulas for the same.

    • Hello!
      Unfortunately, Excel does not allow you to enter more than 4 characters in the number of hours. Your values are written as text.

      • Thank you for that,
        Is there any other way to track these hours calculation.

        For example, if the current value is 12345:6 hours, but after each flights these values need to be updated.

        Thank you in advance.

          • Hi, I have same issue, working in aviation and want to add/subtract aircraft flying hours/mins using excel e.g
            52166:40 - 52163:49

            I see your previous response above but dont understand how extracting th number of hours and minutes is the solution. Please advise if there is a way to do this calculation and show correct results in hh:mm format .

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

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

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

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

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

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

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

  23. 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;@)

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

  24. How do I calculate time remaining till 40 hrs.

    For example: I have clock in, clock out, lunch, and total time for the day. Clock in 8:00, clock out 4:30, lunch 0:30, total time 8:00. Then I have SUM total time Mon-Fri in hr:mm.

    How do I calculate 40 hr - current total time to see how many hr:mm I have left till I’m working 40 hr?

    Thanks for your help!

    • Hi!
      Unfortunately, without seeing your data it is difficult to give you any advice. Please provide me with an example of the source data.
      Without seeing your data, I can suggest the following formula:

      =TIME(40,0,0)-SUM(D2:D6)

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

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

  27. If I have an end time of 9:00am and I need to subtract 160 minutes to get a start time, how do I do that?

    • Hi!
      Pay attention to the following paragraph of the article above: "How to add / subtract more than 24 hours, 60 minutes, 60 seconds"

      • Alexander,

        I can't get it to work, I receive the Value error.

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

  29. Hi, can you help me, i want to change 12 hrs to 24hours for example [hh:mm am - hh:mm pm] all these are in one cell [hh:mm:ss - hh:mm:ss]

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

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

      • 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

  31. Hi,

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

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

  32. Sir
    For time comparison in a column the time format is in [H]:MM:SS and for to get results using IF function for greater than 24 hours as beyond time and less than 24 hours what formulae is to be used. Please reply sir.

    • Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? What result do you want to get? Give an example of the source data and the expected result.

      • Ex if we have times in columns like 34:34:43, 46:57:54, 12:32:56, 44:56:32, 08:45:46 and they are in time format. I want to get result using IF function as for greater than 24 hours to come as "Beyond time" and less than 24 hours as "Within time". Please text the formula and reply sir

          • Hello, in continuation to above query, how can we use if condition for time format that is [h] beyond 24 hours format. Ex. If [h]:mm:ss - 44:19:23 is greater than 2 hours or not.

            • Hello!
              Time is a common decimal number in Excel. If 2 o'clock is written down as the number 2, then convert it to a decimal number, as described in this article.
              Then just compare the two numbers.
              I hope I answered your question.

            • If I’ve to use if formula for : cell A2 value is 44:20:12 (format of cell value is [h]:mm:ss. Now wish to check if condition : =if(A2>=2 hours,“met”,“not met”)
              Please advise how to check the time in [h] format using if condition.. please help

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

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

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

    • 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""")))))))

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

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

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

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

      • 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

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

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

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

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

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

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

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

  40. How easy was that...?! Beautifully explained and so easy to follow. Thank you for the first rate help.

  41. Hi guys,

    I have a start time of 18:00 and end time is 01:00 the next day, when I calculate = End time - Start time I get a negative value of -17 instead of 7 hours . Which formula to use to to get the correct positive value of 6?

  42. Thank you after struggling to find it in the Excel help. 30 seconds of searching on Google and reading your article fixed it.

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

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

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

  45. Kindly help this condition
    my job time 9:00 am and grace time start 9:15 am
    I want to calculate 9:16 am to start late time in minutes

    reply

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