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

  1. Hi,

    I want this 176 Hours 06 Mins

    In Numbers like - 176.06

    1. Hi! To extract numbers from text and insert a dot between them, try this formula:

      =SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(A1,ROW($1:$95),1)),MID(A1,ROW($1:$95),1)," ")))," ",".")

  2. Hi, would everyone know how to conver this time stamp from SAP business objects report?

    "Updated
    Time"
    102752
    103343
    114240
    120446
    125726
    143743
    144708
    145859
    150353
    155545
    160009
    160009

    1. I can't guess what result you want to get. I’ll try to offer you the following formula:

      =TIME(LEFT(A1,2),MID(A1,3,2),RIGHT(A1,2))

  3. Hello
    Am trying to convert hours in this format 150:00:00 to format 150 ,is it possible or it can only be done manually?

    1. Hi! Above in the article it is written that to convert hours into numbers, you must multiply by 24. Read it carefully.

  4. Hello, thank you so much for this, below formula is what i need but in case i want to deduct the number of weekends in it, is it possible?

    =INT(C4) & " days, " & HOUR(C4) & " hours, " & MINUTE(C4) & " minutes and " & SECOND(C4) & " seconds"

    im thinking if incorporating networkdays formula but i was not able to get the result i want

    thanks in advance Alexander

  5. May 1 2023 1:03AM
    May 1 2023 7:30AM
    Convert to M/DD/YYYY HH:MM:SS AM/PM format

    1. Hi! If your date is written as text, use the formula below to convert it to date and time. Then apply the date and time custom format you want.

      =DATE(MID(A1,FIND("~",SUBSTITUTE(A1," ","~",2),1)+1,4), VLOOKUP(LEFT(A1,3), {"JAN",1;"FEB",2;"MAR",3;"APR",4;"May",5;"JUN",6;"JUL",7;"AUG",8;"Sep",9;"OCT",10;"NOV",11;"DEC",12},2,0), MID(A1,FIND(" ",A1,1)+1,2)) + TIMEVALUE(MID(A1,LEN(A1)-6,5)&" "&RIGHT(A1,2))

  6. I have a cell with numbers in the format of YYYYMMDDHHMMSS (20230501080229), need to convert to YYYY MM DD HH MM SS (2023 May 01 08:02:29) to easily read on the spreadsheet. Thank you.

    1. Hi! Use the MID function to extract the required digits and create the date and time using the DATE and TIME functions.

      =DATE(MID(A1,1,4),MID(A1,5,2),MID(A1,7,2))+TIME(MID(A1,9,2),MID(A1,11,2),MID(A1,13,2))

      Set the date and time format you want in the cell, for example "yyyy mmmm dd hh:mm:ss"

  7. 2023-03-01T00:16:36+00:00
    2023-03-01T00:35:39+00:00
    2023-03-01T00:39:00+00:00
    2023-03-01T01:01:44+00:00
    2023-03-01T01:05:57+00:00
    2023-03-01T01:25:51+00:00
    2023-03-01T01:40:14+00:00
    2023-03-01T01:55:27+00:00
    2023-03-01T02:13:56+00:00
    2023-03-01T02:27:51+00:00
    2023-03-01T02:31:06+00:00
    2023-03-01T02:33:34+00:00
    2023-03-01T02:43:46+00:00
    2023-03-01T02:52:38+00:00
    2023-03-01T02:56:37+00:00
    2023-03-01T02:57:09+00:00
    2023-03-01T03:05:35+00:00
    2023-03-01T03:08:44+00:00
    2023-03-01T03:14:07+00:00
    2023-03-01T03:20:39+00:00
    2023-03-01T03:33:29+00:00
    2023-03-01T03:43:00+00:00
    2023-03-01T03:54:52+00:00
    2023-03-01T04:01:29+00:00
    2023-03-01T04:12:33+00:00
    2023-03-01T04:16:18+00:00
    2023-03-01T04:21:23+00:00
    2023-03-01T04:27:26+00:00
    2023-03-01T04:34:44+00:00

    I want to convert these timestamps into hourly basis

  8. Got time in decimal form and want 15,3722222222222 and want to form it into minutes and hours, tried =TEXT(A1/24;"h:mm:ss") with result: h:22:20 what am I doing wrong?

    1. Hi! I got the right result. Check your regional settings. Maybe your hours are not marked with "hh" but with something else.

  9. Hello,

    I feel like I've tried everything. I have a report that needs to be converted to total minutes. See data example below.

    1d 05h 44m
    6h 16m
    3h 00m
    0m
    1h 02m
    1d 04h 36m

    I need to convert it to show the following in total minutes. Most of the formulas I've tried error when there is a cell that has only "m" or "h" and "m' data.

    1784
    376
    180
    0
    62
    1716

    Is that possible?

    Thanks!

    1. I am waiting for the answer to this question

  10. I have a column of Time say as below and rows with name. The format says General. I now wanted to show the same in a pivot but in Pivot its taking as 0:00:00 and not showing as follows. I have 3 ppl working on different days and some days its zero so wanted to know which day they had come and which day they didnt. Basically pivot is not capturing the time.

    03:59:13
    09:14:04
    08:36:58
    08:01:34

  11. I'm trying to convert 2 minutes & 30 seconds in excel from this format 2:30 into a decimal which should show as 2.5 minutes.
    Another example is 15 seconds currently shows as 0:15 in excel and I need it to show it as 0.25 minutes.
    How do I do that?

    1. Hi!
      If these values are written as time and a custom time format (e.g. mm:ss) is used, use the instructions in the first paragraph of this article: How to convert time to decimal number in Excel.

  12. i have wind data each 12 second and i wanted to change it to each 1 second with interpolation
    TIME WIND SPEED
    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
    i need to change it to second time appreciate your help

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

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

  15. thank you! i was struggling for an hour to Sum a lot of work hours on a spreadsheet, converting the AM/PM hours with INT() then doing SUM() worked great!

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

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

          1. Its really work, bu unfortunately once i have the value as ( 01h 22sc ) its dropped

            1. Hi!
              The formula doesn’t work since it has been created based on the details you provided in your first query. I see from your subsequent comment that your task differs from the one you originally described.
              If the time is not written down completely, the formula will not help.

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

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

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

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

  22. I have a spreadsheet that we use and the "time" is listed as a custom "time" using the format 00":"00, which would show 1:30 Pm as 13:30

    I need to convert that "time" to an actual time string for 24 hour clock using an excel formula but I cannot figure it out. Can you assist??

  23. hi,

    i tried using 'To convert the time to decimal, multiply it by 24' but it didn't work, is there another method ?

    thanks

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

  25. Good evening,
    Maybe you can help me out, it would be so great. And I'm sorry if you've already directly answered this question. I couldn't find it. I work on a 24 hour schedule and need to figure out to track my hours. For example, I come in at 1305 and leave at 1535. I am looking for a formula that will enter the time I worked in decimal hours so equaling 2.50 hours, not in total time (2:30) . I can compute it by hand of course, but who wants to do that? Haha thank you very much for your help!

  26. Hello Sir,

    I would like you to help me to sort out this small issue which I’m unable to do here is my problem

    Example
    4534HRS - 2449SEC (I need total hours here)
    It do repeats daily like initial and final

    4530HRS - 2343SEC 4533HRS - 975SEC
    4533HRS - 975SEC 4534HRS - 2449SEC

    Kindly help me to fix this issue.Thanks in advance

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

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

  29. We bill by hours and tenths so I need a formula to convert the result to hours and tenths by set minute increments as follows:
    • 1-6 = 0.1
    • 7-12 = 0.2
    • 13-18 = 0.3
    • 19-24 = 0.4
    • 25-30 = 0.5
    • 31-36 = 0.6
    • 37-42 = 0.7
    • 43-48 = 0.8
    • 49-54 = 0.9
    • 55-60 = 1.0
    2:34 should output 2.6; 1Hr 5 minutes = 1.1; 2 Hrs 15 minutes = 2.3; 3 hrs 45 minutes = 3.8
    Is there a formula that will auto calculate the end result to hours and tenths? Up until now I have done that field manually...

  30. Hi i want to calculate time worked on excel to be converted into hours worked. but time is filled in one cell only eg 12:45-13:00 and the next cell answers .25 hours worked ?

    Can you assist?

  31. How do I convert Actual elapsed time "5311" to 1 Hour 28 Minutes using a formula

    1. Hi!
      Sorry, it's not quite clear what you are trying to achieve.
      Why is “5311” equal to 1 Hour 28 Minutes?

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

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

  34. I always search here when I stuck in any excel issue and I have never been disappointed. Keep up the good work.

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

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

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

        1. Hi!
          Unfortunately, I was unable to reproduce your issue. The formula is designed to work with time values, but it can also work with times written as text. With the data you provided, it also works correctly.

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

  39. If I need to have the clock in times and clock out times to be converted to decimal values that consist of 0-4 minutes = 0 , 4-9 =.1, minutes, can the formula be set up to do the conversions for me?

    1. Hi!
      Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in detail, I’ll try to suggest a solution.

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

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

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

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

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

  45. How to convert this data (1 Day 1 Hour 10 Minutes) into decimal

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

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

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

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

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

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