Comments on: How to convert time to decimal number, hours, minutes or seconds in Excel

The tutorial demonstrates different ways to convert time to decimal in Excel. You will find a variety of formulas to change time to hours, minutes or seconds as well as convert text to time and vice versa. Continue reading →

Comments page 2. Total comments: 275

  1. Hello, I am setting up a payroll calculator on excel and wanted to see if there is an if function or any function that I can use to calculate payroll correctly. I current input hours formatted as text and then multiply by 24 but It gets thrown off if there is a whole number.

      1. i have data sheet for wind turbine in every 10 second i get wind speed, and i need to resample this data in every second (The model is
        based on a 1 s sample time, in order to resample the wind speeds the 10 s data was interpolated linearly between points)
        T W
        12:33:05 2
        12:33:10 2
        12:33:15 1
        12:33:21 1
        12:33:26 1
        12:33:31 2
        12:33:36 2
        12:33:41 2
        12:33:46 1
        12:33:51 1
        12:33:56 1
        12:34:01 1
        12:34:06 1
        12:34:11 1
        12:34:16 1
        12:34:21 1
        12:34:26 1
        12:34:31 1
        12:34:36 1
        12:34:41 1
        12:34:46 2
        12:34:52 2
        EXPECTED LIKE THIS
        12:33:05 2
        12:33:06 2.1
        12:33:07 2.15
        12:33:08 1.09
        12:33:09 1.51
        12:33:10 2
        12:33:11 2
        12:33:12 2
        12:33:13 1
        12:33:14 1
        12:33:15 1
        12:34:16 1
        12:34:17 1
        12:34:18 1
        12:34:19 1
        12:34:20 1
        12:34:21 1
        12:34:22 1
        12:34:23 1
        12:34:24 1
        12:34:25 2
        12:34:26 2

  2. I need to convert numbers to hours and mins
    2.03 is what I have in excel I need 2:03 is there away to format the data

  3. Hi!

    I would like to get your best guess on how to have a cycle time of a task calculated in the right way: data in one (!) cell is like "2 Days 5.49 Hours" as one example.

    I now would like to have following information: How many business hours does it take for a colleague to finish this job ?

  4. I would like to change the time from e.g. 1h 32m 4s to 01:32:04 without having to do it individually. How would I do this?

    1. Hello!
      Extract the numbers from the text and use the TIMEVALUE function to convert the text to time. Try the formula:

      =TIMEVALUE(SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(H1,ROW($1:$30),1)),MID(H1,ROW($1:$30),1)," ")))," ",":"))

      1. hello dear,

        its work but once i have a time as a "01h 31sec " its dropped with wrong time
        how can be flexed becouse the time fromat that im tried to fixed it like this
        15h 30m 20s
        01h 30s

  5. 1 days 2 hours 20 minutes How to convert it into 26 hours 20 minutes
    I tried but result is not yet got

    1. Set up the number format to custom like that: [h]:mm

  6. I am trying to show total hours as days and hours with this formula as it shows Days hours and minutes which is what I need.
    =INT(C4) & " days, " & HOUR(C4) & " hours, " & MINUTE(C4) & " minutes and " & SECOND(C4) & " seconds"
    In C6 I have 40.00 hours carried over in column F6 is 4.0 hours extra holiday
    I add the hours together in H6 to show a total of 44 hours
    In column I have H6/8 = 5.5 days (working an 8-hour day).
    The formula shows this as 5 days 12 hours 0 min 0 sec.
    I understand that it sees the .5 as half a day - 12 hours.
    Is there a way to convert this so it shows as 5 days 4 hours.
    I have tried changing H6 to divide by 24 and have tried H6 to divide by 3.
    Is what I am asking possible.

    Regards
    Peter

  7. Hi all,

    could I possibly get some pointers on the below:

    i usually download a report with a time in and time out for our engineers on site.
    is there an easy way to calculate if they have been on site from 7:30 am to 5:30 pm to get a full shift.
    the shifts are based on a 10 hour work day.
    for example if they work 9h45m - they would get 0.97 of a shift.

    this would save me days of work! i'd appreciate any help!

    Many thanks!!

  8. Hello

    I would like to calculate the lateness

    Time in should be 830 = 8:30am ( I use =TIME(IF(LEN(A1)=2,0,LEFT(A1,LEN(A1)-2)),RIGHT(A1,2),0) , this formula provided by you )
    Actual in 903 = 9:03am
    How to prepare formula for lateness in minutes ? and show in minutes ?

    1. Hi!
      Calculate the time using this formula in two cells and subtract time. Or subtract in one formula

      =TIME(IF(LEN(A2)=2,0,LEFT(A2,LEN(A2)-2)),RIGHT(A2,2),0) - TIME(IF(LEN(A1)=2,0,LEFT(A1,LEN(A1)-2)),RIGHT(A1,2),0)

  9. Hi,

    Is there any formula in Excel which it could directly convert day/hour/minute display into total minutes?

    Example: 1d 20h = 2640 mins

    Thanks!

  10. I am trying to convert time on duty as follows in one cell to a number of hours in another
    8:00-16:00 - is that possible?
    Thank you
    Grainne

  11. Hi,

    I am trying to convert 3.50 to show 3hours and 50 minutes in time format.
    Working out new hours for a store allocated for a service, the current hours are formatted to a decimal place for 3hrs 50 minutes. Whereas the way I have calculated it was 4hr 15 minutes in the H:MM format. How can I get this to translate the same for the 3.50, as I need to find the variation in time format.

    For example: old hours 3hr 50 mins, new hours 4hr 15mins = an uplift of 25minutes. I have tried to convert the 3.50 to the H:MM format is just gives me 12pm or 84:00. Which is wrong.

    New Hours Current hours
    4:15 3.50

      1. I just get the value error.

  12. I have the following in a spread sheet and am wondering how I go about setting up a formula to learn the difference between the cells. It is a HUGE data sheet with almost 20k entries. The day as just a number and not a full date is throwing me off.

    Day Hour:Min:Sec

    29 18:25:26 29 18:39:12

    1. The data points should have looked like this
      29 18:25:26
      29 18:39:12

  13. How do I go from 0800-1200 to 8:00am-12:00pm In One cell

  14. Hi, I have a dataset with time being in the General number format. I know I can manually select the cell to be formatted in time, but don't want to do it manually for all the rows. Can someone tell me how the conversion works? Thanks

    For example:
    44653.8395833333 is equivalent to 4/2/2022 8:09:00 PM

  15. Hello, I have an excel sheet with the time in format "11 Days 2 Hours 58 Min 0 Sec" I want to convert this into either a numeric format or just into minutes. What formula could I use to accomplish this?

  16. Good morning. I am trying to count the presence of specific digits *within* a column of time values. For example, how many minutes in a column of times end in the character 0? I can't find that anywhere online, and experimenting with COUNT, COUNTIF and COUNTIFS gives me obviously bad answers. Please help?

      1. Thank you. I think we're a lot closer to success, but I'm getting a #VALUE! error with that.

        I used TEXT function to convert the time column to a column of pure text that looks like it should, basically 1746 rows of values like this...

        18:01
        18:48
        20:15
        20:45
        21:35
        21:59
        22:10
        22:30
        22:59
        23:32
        05:30

        That worked. Then I pasted your formula, modified for all the row numbers in column E containing the data...

        =SUM(--(RIGHT(TEXT(e3:e1748,"hh:mm"),1)="0"))

        ...and got the #VALUE! error. If you can help, I'd hugely appreciate!

  17. Dear Sir,
    I have to compare two time sets and check which of them appeared earlier.
    so using the delta between TIME A and TIME B:
    DELTA = TIME A - TIME B

    if Delta is >0, than no problem, i will get the time difference and can be transformed to Text, etc. in order draw an average.
    BUt, if Delta is <0, i have a problem because (TIME*24) is not working if the Time is a negative number.

    I hope my explanation is clear.
    thank you in advance,
    regards,

  18. Hello,

    I was wondering how to translate a time written as a whole number to a time value. For example, the values I have on my spreadsheet are values listed as "543" for 5:43 AM and "1350" for 1:50 PM. I also have values that are "50" for 12:50 AM. Can you please help me translate these values into a time value?

    Thank you

    1. Hello!
      To create a time value, use the TIME function.

      =TIME(IF(LEN(A1)=2,0,LEFT(A1,LEN(A1)-2)),RIGHT(A1,2),0)

      I hope it’ll be helpful.

  19. Hello,

    I am struggling with something that might be simple so I apologize in advance if it is.

    I'll give an example:
    it takes 00h 00min 03sec to bake a cookie
    I want to know how much time it will take to bake 500k cookies.
    so I want excel to multiply 00h 00min 03sec with 500k
    The result should be same format 00h 00min 03sec. I also need to be able to convert the hours in days if needed.

    Thank you in advance for your help
    Jamie

  20. Hello, I am working as a logistics coordinator and i have 2 questions:

    1) If i have a driver who finishes driving at 18:00(F7) and starts next day at 06:00(E8) it means he has 12hrs of rest time. I used formula =24-F7+E8. But if on sunday he doesnt drive at all. For example finishes saturday at 14:00 but starts only monday at 06:00. What formula could i use to automatically add 24 if there is a blank cell?

    2) Is it possible to use format not ''12:45'' but ''12h 45min'' when it counts the rest time?

    Thank you

  21. 10 Day(s) 22 Hr(s) 34 Min(s)

    How to convert this into days.

      1. Hi Sir , Thanks for the response.

        i have tried that the same but not getting the exact days.

        10 Day(s) 22 Hr(s) 34 Min(s) - I want to convert this in days only , like 10days 22 hrs should come as 11 days. seeking for your help on this.
        Thank You

  22. Hi

    Wanting to do 'end time' - 'start time' in order to get travel time.

    End time and start time are in 24hr clock format.

    For example, end time is 1100 and start time is 1000. Thus 60 minutes. But I need a formula for this.

    No AM / PM or : used.

    Please help! I've read the above.

    1. Nevermind. I figured it out. Used LEN and RIGHT functions to add : to all values then I used the formula you presented above.

  23. Hello,
    Wondering how to do a column conversion of hours into days. I have 728 rows in the column

  24. Hello,

    Is there a way to have excel automatically convert the time in hours to minutes, AND display the result in the same cell in which the data is input?

    Thanks

  25. Hi,
    Is there a way to convert a length of time in text string formatted like the following to a numerical number such as total days, so I may determine the average length of that time for hundreds of entries.

    10 Years 8 Months 26 Days
    1 Years 4 Months 23 Days
    5 Years 11 Months 5 Days
    0 Years 6 Months 12 Days

    Thank you for any assistance!

      1. I think I have the same issue, but with hours. Our report gives us the cell with: 7 hours 10 minutes 4 seconds, but we need it in the format hh:mm:ss. Cand we transform that?
        Thank you

  26. Hello!

    I could use your expertise, please!

    I'm trying to go from:-

    1 hour 18 mins

    to

    78 minutes

    Is that possible?

    Thanks
    Stephanie

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question

      =HOUR(A1)*70+MINUTE(A1)

  27. Hey, I'm trying to convert 61:20:00 to decimal, but the formulas I'm finding online are not working because it's over 24 hours. Any tips on how to get it to work? I'm using Google Sheets.

      1. Awesome! Found the solution in the first link. Thanks so much.

  28. Hi,

    I've received an excel sheet with the format of '# days # hours # mins' per cell in one column. Some cells have '# hours # mins' and others have just '# mins', all in the same column. I'm really stuck on how to convert all into mins without calculating each cell individually. Is there a formula I can apply to update this? Would really appeciate any direction!

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =IFERROR(--LEFT(A1,SEARCH("days",A1)-1),0)*1440+ IFERROR(--MID(A1,SEARCH("hours",A1)-3,3),0)*70+ IFERROR(--MID(A1,SEARCH("mins",A1)-3,3),0)

      I hope my advice will help you solve your task.

      1. Hello Alexander, just wanted to say the above helped me so much!!! I searched many forums and articles and you were the only one that had the answer. Thank you

      2. Hi Alexander,

        Thank you so much - it's working, I only need to add a '0' before the data that reads as '2 hours 30 mins' etc, so it's '02 hours 30 mins', otherwise it won't work on those cells. But this is wonderful, thank you! so much faster than what I was working on before!

        1. Hi Amy, is there a formula that could fix this? Running into the same issue

  29. Hi,

    I need to show whether the time elapsed between 2 dates is within the required time frame.
    I have generated the different between the 2 dates using:
    =INT(W4-C4)&" days "&TEXT(W4-C4,"h"" hrs ""m"" mins """)

    And then want to use a simple IF formula to determine if it's within the time frames:
    =IF(Y4>K4, "No", "Yes")
    where
    Y4 is the cell with the first formula
    and K4 is either 0 02:00, 1 00:00 or 5 00:00 (in custom format dd hh:mm)
    This seems to work fine for the 2 hours and 1 day option but "Yes" is always return for the 5 day option.

    I've also tried this where the first formula is a simple =W4-C4 and i have the same issue.

    Can you help please?

    1. Hello!
      The formula in cell Y4 returns text. Therefore, you cannot compare text value with > or < .
      In cell Y4, enter the time in the time format. For example, 2 days is 48:00:00.
      Remember that date and time in Excel are numbers.
      Try this formula

      =IF((W4-C4) > K4, “No”, “Yes”)

      I hope my advice will help you solve your task.

  30. Hi,

    I was wondering if anybody can help me with this:

    In an Excel sheet, I have accumulated deducted minutes in cell D1 displayed like this:

    146:50 (146 hrs & 50 minutes)

    cell formatted as: [h]:mm

    I want to display deducted time in 3 separate cells like this:

    H10 G10 F10
    18 2 50
    (days) (hrs.) (min.)

    ** each 8 hours = 1 day deduction

    Thanks in advance,
    Jaafar

    1. Hello!
      To get working days, hours and minutes, use these 3 formulas:

      =INT(D1*24/8)
      =INT(MOD(D1*24,8))
      =(D1*24-INT(D1*24))*60

      Hope this is what you need.

      1. Hi,

        This is what I needed. Problem solved.

        Many many thanks, Alexander.

  31. Hi Hi!

    Would you please assist me on a formula / function that can convert the following:
    03Hrs15Mins06Secs to just the numbers eg. 03:15:06
    34Mins04Secs to eg. 00:34:04

    thank you so much!

    1. Hello!
      Use substring functions in Excel to extract text from cell:

      =TIMEVALUE(IFERROR(MID(A1,SEARCH("Hr",A1,1)-2,2),"00") &":"&IFERROR(MID(A1,SEARCH("Min",A1,1)-2,2),"00") &":"&IFERROR(MID(A1,SEARCH("Sec",A1,1)-2,2),"00"))

      This should solve your task.

  32. Hi & thanks for your most excellent site, it has come in handy for me so many times!

    I have a problem where I'm trying to calculate the difference between two datetimes like this

    Start: 29/Nov/21 11:52 AM
    End: 1/Dec/21 5:38 PM

    Excel nicely converts the above when pasting in to the cells A2 & B2 as follows:

    A2: 29/11/2021 11:52
    B2: 01/12/2021 17:38

    C2: =B2-A2 returns the difference, expressed by Excel as: 02/01/1900 05:46

    D2: =int(C2) returns the days, expressed by Execl as: 2 (format cell to number)

    E2: =MOD(B2-A2,1) gives me just the time, expressed by Execl as: 05:46:00

    So, I can use the above to deterrmine days & hours. If I use 8 hours as a working day, 2 days equate to 16 hours, plus the 5:46, would equate to 21:46 hours:minutes.

    1. I'm wondering if there's a better way than the above?

    2. My second problem comes from having to account for weekends (non-working times).

    I have no idea how to do this. I suspect I'd have to use a calendar?

    Would really appreciate your thoughts.

    Many thanks,

    Derrick

    1. Hello!
      Try this formula in cell C2:

      =B2-A2-NETWORKDAYS.INTL(A2,B2,"1111100")

      The NETWORKDAYS.INTL function counts the number of weekends. In this formula, these are Saturday and Sunday.
      I hope I answered your question. If you have any other questions, please don’t hesitate to ask.

  33. Hi I need to convert the following:

    The original data is 03 Hrs 11 Mins 00 Secs and i need it is look like this 3.11

    I hope you can help me!

    Thanks so much!

      1. Hi! thank you, but i am still not getting the desired outcome.

        Please assist.

  34. Good day Alexander Trifuntov

    I am trying to convert 30d21h45m into hours on excel using a formula, could you please help.

  35. Looking to average out two columns of numbers based on hours and minutes to find the mean time. Looking for
    whole hour and minute total. Help would be appreciated.

    Count Average Time Total Time
    3 0.479166667 34:30:00 (34 hours, 30 min, 0 sec) [H]:MM:SS turns into 1/1/1900 10:30:00AM
    3 8:16:20 24:49:00

  36. How do I convert 44,694:30 hours to 44,694.5? I can't find formula here.

    44,694 hours and 30 minutes to convert in decimal.

      1. FYI, make sure the cell yur formula is in is formatted as a number no general. i've seen this cause issues.

  37. Hi
    Similar to Robert says:
    February 6, 2021 at 11:37 am
    Can I please have 1 days 4 hours 21 mins converted to 28.21 in Excel

    I am importing travel time from google maps such as above so my cell contains let's say
    16 hours 20 mins
    Id like this to be 16.33 so I can charge it out multiplied by an hourly rate.
    Love your help with this. (I'm actually using MS Access) if that makes it easier or harder?
    Poida

      1. I still can't get it, I can't see it in the first paragraph when it is all in just one cell
        17 hours 59 mins
        Thank you

        1. Hi!
          I am assuming that there is a text written in your cell and not the time. To convert this text to time, use the formula

          =--SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(D1,ROW($1:$94),1)),MID(D1,ROW($1:$94),1)," ")))," ",":")

          1. Thank you so much for your replies, I think you should go in my favourites.
            That worked a treat, I just had to add the x24 to the end to get 17.98333 hours.

            I'd still be at Uni trying to put that equation together, and I'm over 50.

            Cheers Poida

  38. Hi there,

    How can I convert 12:00:00 AM to 00:00:00 and 12:00:00 PM to 12:00:00 in the same formula including both AM/PM, if I need to convert time to AM or PM accordingly?

    I want to convert,
    12:39:46 AM in A2 to 00:39:46.
    12:04:14 PM in B2 to 12:04:14.

    2. you can have formatted it into 13:00:00 instead

  39. When I type 05082021, I want it to show as 05/08/2021, and when I type 0957, I want it to show as 09:57
    Please help. I've tried everything I can think of.

  40. i have value 0.90 in a cell in excel i want to convert it as hours

  41. Hi am new to all this and I have a question how do I convert 20hrs to numbers

  42. I have a column for duration. It varies from 22S to 17M 13S. Is it possible to create a formula to read this in 00:00:00 format? I can do something like =timevalue("0".&Left(C1,1)&":""&MID(C1,4,2)) for a duration of 2m 46s but that formula then wouldn't work for the next cell if it is 17m 59s. Is there a way to have one formula convert varying durations to the 00:00:00 format?

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =SUBSTITUTE(IF(ISNUMBER((SEARCH("m",C1))), "0:"&SUBSTITUTE(SUBSTITUTE(C1,"m ",":"),"s",""), "0:0:"&SUBSTITUTE(SUBSTITUTE(C1,"m ",":"),"s","")),"m",":0")

      I hope my advice will help you solve your task.

      1. It worked. Thank you.

  43. don't know if you can help but kevin asked the same question of 5th feb
    I have task that need to be completed each different time but I am sure if I get the one calculation right it can be change for that length of time for example.
    1 task takes 20 mins over a week they do 392 which in my head is 20 x 392 = 7840 minutes
    my simple head is divide that by 60 to gat 130.67 but then if I use =CONVERT(M204,"mn","hr")/24 I get 5.44 that convert to 10 hours 40 minutes it seems which way I use a formula I get a different answer
    the other times are
    1 task x 20 mins
    1 task x 30 mins
    1 task x 75 mins
    1 task x 45 mins
    but I I can get the right formula changing the length of time should be easy …….. or will it
    what is the right formula for this to give me the right length of time in hours and minutes for all these jobs
    thanks in advance

    1. Hello!
      If I understood your problem correctly, then to convert minutes to hours use the formula

      =A1/60/24

      Then set the cell custom format to "37:30:55"
      I hope my advice will help you solve your task.

  44. Can I please have 1 days 4 hours 21 mins converted to 28.21 in Excel

    1. Hi,
      Please describe your problem in more detail. How is your original data written as 28:21:00 or as the text "1 days 4 hours 21 mins"?

  45. Hi There,
    I need to convert 08:30:24 into hours and minutes only. tried with above but failed, Kindly help
    THanks
    Stay safe.

    1. Hello!
      If I understand your task correctly, you can use custom time format —

      "hh:mm"

      I hope it’ll be helpful.

  46. How to convert 56 to 056:00 in Excel
    Thnx

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =TIME(0,56,0)

      Use a custom date format

      [hh]:"0"mm:ss

      I hope this will help

  47. Hi there,

    Is it possible to convert time 08:30 to 08.30 instead of 08.5

    Thanks,

    1. Hello!
      Time cannot be shown using a point. But you can convert it to text

      =TEXT(A1,"hh.mm")

      I hope my advice will help you solve your task.

  48. Hi,

    How could i convert shift times in to hours as a formula.

    For example, if someone is working from 6am - 2 pm 5 days a week, how can I get the total hours worked calculated?

  49. I'm trying to take a Start Time and add 1 minute per question being answered to calculate an end time.
    What I currently have:
    (F13) Time Format (HH:MM) Start Time: Manually enter Time "example. 21:00"
    (G13) Number Format Total Questions: =(Results!K3) "example. 153"
    (H13) End Time: =SUM(TIMEVALUE(F13:G13)) "Result: Error Value"

    Could someone help me resolve the error?

    1. Hello!
      If the number of minutes is written in cell G13, then you can add minutes to all of them with the formula

      =F13 + G13/1440

      I hope I answered your question.

  50. How do you do the first example you shared if the time is greater than 12? for example, I have time 23:56
    and I would like to convert it to minutes. When I use this formula =(R11*1440) I am getting 14396 - this should be 1436. Why is this happening? Thanks!

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