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 8. Total comments: 463

  1. How to format hours by using IF...
    Let's say I want from 06:00am to 22:00pm be "morning" and 22:00pm to 06:00am be "night".
    Thank you.

    1. Creating an Excel spreadsheet to use as a time card to keep track of the hours worked, I have used the formula =IF(end>start, end-start, 1-start+end) to reflect there are times I'll elapse (i.e. - go beyond midnight) to calculate the total number of hours. Now I need to convert that time into an hour and decimal minutes format to be able to calculate regular pay and overtime pay. How do I do that?

  2. Hi all,

    how to split 1 day 3 hours 17 minutes into hour and minutes separately?

  3. I have to convert value excel calculation is 100 qty is one hour this value i have convert in time format

  4. THANKS AND REGARDS FOR SUPPORT

  5. Would it be converting 2:53 to Hours then multiplying by 30 degrees?
    2:53*24=2.8833 then 2.8833*30=86.50 Degrees

  6. Need help converting hh:m to Degrees on a circle. For example 2:53 to degrees.

  7. 25days * 10:30 hour = ? In hh:mm format ?
    Whts the formula like this calculation

  8. 27days * 10:30hours = ans in hours
    How to formula for this type of sums ?

  9. Thanks for the tutorial and for answering questions. I'm trying to convert a cell that is 'x days, y hours, z minutes, and t seconds' into minutes. I've tried a few of the recommended solutions however none have worked. What might I be missing or have wrong (i.e critical spacing I'm adding/missing or a specific format I need to have in the answer cell)? Also note, sometimes days, hours, , minutes, and/or seconds might have a 0 value. Often when I type them in the first referenced cell is highlighted like normal but every referenced cell following is not highlighted. Thanks in advance for your help. Here are the suggested formulas I've tried:
    =INT(LEFT(C8 ;FIND("days"; C8) - 1)) * 24 * 60 + INT(MID(C8 ; FIND(" "; C8) + 1; FIND("hours"; RIGHT(C8 ; LEN(C8) - FIND(" "; C8))) - 1)) * 60 + INT(MID(C8 ;FIND(" "; C8;FIND("hours"; C8))+1;FIND("minutes"; C8)-FIND(" "; C8 ;FIND("hours"; C8)) - 1))

    And I've tried this one:
    =IF(ISERROR(FIND("day", A1)), 0, INT(TRIM(LEFT(A1, FIND("day", A1) -1))) * 24 * 60) + IF(ISERROR(FIND("hour", A1)), 0, INT(MID(A1,IF(FIND("hour",A1)-6<0,1,FIND(" ",A1,FIND("hour",A1)-6)),FIND("hour",A1)-IF(FIND("hour",A1)-6<0,1,FIND(" ",A1,FIND("hour",A1)-6)))) * 60) + IF(ISERROR(FIND("min", A1)), 0, INT(MID(A1, FIND(" ", A1, FIND("min", A1)-6), FIND("min", A1)-FIND(" ", A1, FIND("min", A1)-5))))

  10. Thank you, Svetlana! For weeks, I have been looking all over for help that covers all my need for time/date conversion. Your answers work perfectly with my data!

  11. How to convert number 102 to HH:MM

  12. Hello,
    Is there a format type or other way to quickly type hours to cell?
    For example I want to add time 21:00 by typing only "21".
    I have tried multiple different formats and if I type "21" it turns out 0:00.
    Any idea about the format I should use?

    1. Ville,
      Format cell as Custom, adding in "Type:" 00":"00
      So for eg. 7:15 you type 715

      Hope this helps,
      Davor

  13. HELLO DEAR,
    CAN I CONVERT NUMBER FORMET FOR EXAMPLE 1.75 IN TO TIME FORMET FOR EXAMPLE 1.45 HOURS IN MS EXCEL.
    9015223435

    1. I UNDERSTAND THAT YOU WOULD LIKE TO CONVERT NUMBER FORMAT (FOR EXAMPLE 1.75 IN TO TIME FORMAT WHICH IS 1.45 HOURS) IN MS EXCEL.

      > FIRSTLY, CONSIDER 1.75(WHICH IS IN GENERAL FORMAT) IN CELL A1. USE THE FORMULA IN CELL A2 AS =A1/24(THIS IS LIKE YOU ARE CONVERTING IT INTO HOURS). YOU WILL GET THE VALUE 0.072916667(WHICH IS GENERAL FORMAT).
      FINALLY, SELECT THE CELL A2 AND CONVERT INTO TIME FORMAT('HOME' TAB > GENERAL TO 'TIME'), YOU WILL GET THE VALUE 01:45:00.

      1. Navish, is there a way to do it the other way? If I have a time (let's say 8:30), how do I convert it to 8.5 in number format in excel?

        Thank you!

        1. Figured it out. Have four columns: A start time column, an end time column, a difference in time (hh:mm) column, and a decimal time column (hours). Format the first three as time and the last as general. For the start time and end time columns, make sure it is in military time and you have the date entered in the cell as well (it should only show the time when not clicked on). In the time difference column, do a function (use =) and subtract your end time from your start time. This should appear in "hh:mm" format. In the final column, use this equation: =CONVERT( cell ,"day", "hr"). Replace the 'cell' part with your desired time difference cell. It should look like "=CONVERT([@[Hours (time)]],"day", "hr")" in the formula bar when you are done, with a box highlighting your desired time difference cell. This should convert your time in hh:mm to hours as a decimal. I used this to add my difference in hours into a total numeric value.

          For example, lets say I volunteer from 1 pm to 2:30 pm every saturday for a month (4 weeks). that is 13:00 and 14:30 in my first and second columns (respectively) for the first four rows. In all of my third columns (time difference) for the first four rows, it should read "1:30". In my final column, it would read "1.5". I can then add my final column together to get a total of 6.

          1. You may be able to get it into three columns if you do a compound function to combine the third (time difference) and fourth (decimal time in hours).

        2. I would like to know the same thing

          1. Hi,
            This question has been asked many times in the comments.
            To convert time to decimal, multiply by 24

            =A1*24

  14. Dear
    Our worker overtime in number(6.83) we want change in hour pls solve with example

    1. 6.83 multiplied by 24 then custom format and select hh:mm

      1. My fault should be divided by 24 then custom format and select hh:mm

  15. This is a small sample of my data that I need to convert to [h]:mm.
    I can remove the words days, hrs and mins, although it's sort of a manual process by replacing them with nothing.
    I will be adding it all up and providing some statistical info on the changes, as there are two columns with similar data. One for before and another for after. Sometimes the second column has no value, contains the word "Removed" or is Zero "0 mins".
    1 days 7 hrs 49 mins
    6 hrs 10 mins
    14 hrs 25 mins
    8 hrs 50 mins
    18 hrs 4 mins
    17 hrs 58 mins
    1 hrs 21 mins
    4 days 2 hrs 34 mins
    2 days 20 mins
    I need help. I found something similar, but not quite the same. Also it's been written in a function. I know I have to call a function, but am not totally sure how this would be done.
    Thanks for any assistance or if someone can write a macro or VBA code for me?
    Much appreciated.

  16. I have time format in 1 days 3 hours 24 minutes.
    I need to change this to actual hours and minutes.

    Any help much appreciated.
    Thanks

  17. Hi,
    Is there a way to convert from 1 to 6 digits in a column to time HH:MM:SS
    110759 11:07:59
    110739 11:07:39
    110723 11:07:23
    41727 04:17:27
    41714 04:17:14
    124 00:01:24
    106 00:01:06
    48 00:00:48
    24 00:00:24
    I use the Text to column, fixed width function but , when they are not the same number of digits in a row they do not split properly.
    Tried the Custom format by adding 0 in front of the digits and it shows 6 digits in each cell but when I try to use the Text to column it ignores the format.
    Any ideas?

    1. Try with Delimited & space

    2. this is exactly what i'm searching for help on too. would love to see if anyone knows of a way to do this.

  18. I am trying to calculate lap splits for track athletes. I would like to calculate this in minutes, seconds and hundredths of a second. If I have elapsed time entered into a spreadsheet. Is there a formula that would calculate lap splits?

  19. Hello,
    I am creating a sheet to calculate how much time it will take to perform a series of daily tasks. I have a set number of items that require 7 minutes of work per item. For instance, on Monday, there are 24 items that take 7 minutes each, for a total of 168 minutes. I need a formula that shows how many hours and minutes it will take. I can do the math myself and know that it takes 2 hours and 48 minutes (2 hours=120 minutes, plus 48 minutes). How do I get this to work in Excel?

  20. Hi,
    Please help. I want to convert the following duration to a decimal Date/Time value:
    Input: 0 13:15:20
    Input format: d hh:nn:ss
    D=days
    h=hours
    n=minutes
    s=seconds
    Output required: eg: 0.5523
    A decimal value of the duration.
    Thank you for any help.

  21. 25mins,1 hr 8 mins convert to time

  22. how can i convert an excel data of this format 25 mins to time data

  23. Hi Svetlana,

    I have a question,

    How can I get the difference between "1/29/2019 9:48:13 AM" and "1/27/2019 12:06:36 PM" in "HH:MM:SS" format, what formula I should use for such cases ?

    Thanks is Advance

    Vivek Wadhawan

    1. number in seconds: 1.201
      function: TIME(0;0;1.201)
      result: 0:20:01

      number in seconds: 430
      function: TIME(0;0;430)
      result: 0:07:10

    2. Hi Guys,

      Sorry, I have overlooked this question somehow. To display the difference between the two dates in hours, minutes and seconds, use this format: [h]:mm:ss

      The hour unit code enclosed in square brackets is used to display more than 24 hours. For more information, please see How to show over 24 hours, 60 minutes and 60 seconds in Excel.

      1. I have DD:HH:MM:SS and need to convert to decimal value. You're saying I have to first change to [h]:mm:ss or hh:mm:ss (somehow) and then convert? I don't see any direct way to go from dd:hh:mm:ss to decimal value in any of the formulas. hh:mm:ss to decimal is very straightforward, but adding days to it doesn't seem to work.

    3. Same question here. How do you convert DD:HH:MM:SS into HH:MM:SS ?

  24. now i add two man working hours but it not produce the correct answer
    for example siva = 10.50 hrs
    raja = 10.50 hrs
    add 2 manhours = 21 hrs (excel result)
    but i want 21.40 hrs
    how do i do plz

  25. Hi
    I'm copying the data from the internet, the start time which in that format like "Fri Jan 11 20:30 19" and run time " 00:07:45:07" which is in "day:hour:minute:second" format. While pasting in to the excel sheet it's showing the show digit number like for start time 1547210301409 and run time value 27907397. Can u guys suggest me , How can i convert it in same format in excle ?

  26. Hi
    I want to show time without am/pm
    how can I do it?

  27. I'm trying to work out how to calculate time worked between 2 times, then minus 45 minutes break but then change the format from example 8hrs 45mins to 8.75hr
    Our payroll department, works in decimals (1hr 15mins is 1.25hr, 30mins is 0.5hr.

    I'm not sure whether that makes any sense or not.
    It will make doing my guys timesheets so much quicker each week.

    Many thanks
    Phil W

  28. How to convert any six digit no for example 71400 into 71° 40' 0" format in excel

    1. Hello, Mohit,

      Please do the following to change the formatting of your cells:
      - Select a cell(s) for which you want to create custom formatting, and press Ctrl+1 to open the Format Cells dialog.
      - Under Category, select Custom.
      - Type the following format code in the Type box:
      ##°##'#''
      - Click OK to apply and save the this format.

      I can also recommend you to have a look at the following article on our blog to learn more about custom formats:
      Custom Excel number format

  29. Hey Honey,
    I have 4, I want this to be converted into hours. Please guide.
    Below are the more examples.
    0
    60
    6.25
    10
    Thanks

  30. Need help!!

    I have a column with below details and i wanted to convert to time but i dunno what's going to be the divisor to properly convert to such.

    0
    30
    100
    130
    200

  31. Thank you so much.

  32. Could you please help me with the following?

    If I were to start production at 7:00am and had a work order to produce 500 cases which should take 2.5 hours to complete how do I calculate the end time in excel?

    For example... What formula would I use to calculate the following?

    Start Time: 7:00am (Cell A1)
    Hours Needed: 2.5 (Cell B1)
    End Time: 9:30am (Cell C1)

    Thank you very much for your time!

  33. I have the number 6.53 which actually is 6 hours and 53 minutes and i need to deduct this number (6.53) from 8 hours to get to know how much hours and minutes are not attended by the employee so in excel what formula will give me the desired result.

  34. How do I convert 429:30 to 429.5?

  35. Hi
    Could you please help me with the following. My software returns an actual duration in the following way: 1 hour,30 minutes. This is returned in a single cell. However, I need to convert the output to 1.5 for example.
    Could you please let me know how I could do this.
    Many thanks

    1. Vishal:
      This will give you the result you want from your sample:
      =LEFT(I29,SEARCH("Hr",I29)-2)&"."&((MID(I29,SEARCH("Min",I29)-3,2))/6)
      Where "1 Hr, 30 Min" is in I29.
      You'll have to work with this to get the results you want in another situation.

  36. Hi,

    Just wanted to let know others, I am able to find the formula (conditional formatting) as below:
    For more than 9 hours: =CONVERT($D2,"day","hr")>216 Fill with color 1
    For more than 10 hours: =CONVERT($D2,"day","hr")>240 Fill with color 2
    For more than 11 hours: =CONVERT($D2,"day","hr")>264 Fill with color 3

    Rgds,

  37. Hi,

    Thanks for the tutorial blog.

    I have a sheet wherein I will be calculating the time spent by my team member.

    Time-in will be enter in column B and time-out in column C, I will be calculating the total time spent in column D by formula =TEXT(c2-b2, "hh.mm").

    I want to highlight the column D with one color, if the time spent is more than 9 hours and with another color, if the time spent is more than 10 hours.

    What formula should I use in Conditional Formatting? Or is there any other way of highlighting the column D cells based on their values.

    Thanks in advance.
    Derok

    1. Hi,

      Just wanted to let know others, I am able to find the formula (conditional formatting) as below:
      For more than 9 hours: =CONVERT($D2,"day","hr")>216 Fill with color 1
      For more than 10 hours: =CONVERT($D2,"day","hr")>240 Fill with color 2
      For more than 11 hours: =CONVERT($D2,"day","hr")>264 Fill with color 3

      Rgds,

  38. Hello, Nice Tutorial.
    I have this , 1/3/1900 10:20:29 AM , in a a cell. I want to convert to sec 10:20:29. I don't want the date

    1. Stamatis:
      I would use the Text-to-Columns tool and in the third window select the date and the AM portions and click Don't Import option. Then format the data as Time in the Format Cells window.

  39. Sir,
    I want to convert a ten digit number to hh:mm:ss format for eg. 1505475418 to hh:mm:ss

    1. Amit:
      I found this explanation and conversions on Excel Tips from John Walkenbach.
      If you import data you might encounter time values stored as Unix timestamps. Unix time is defined as the number of seconds since midnight (GMT time) on January 1, 1970 -- also known as the Unix epoch.

      For example, here's the Unix timestamp for August 4, 2008 at 10:19:08 pm (GMT):
      1217888348

      To create an Excel formula to convert a Unix timestamp to a readable data and time, start by converting the seconds to days. This formula assumes that the Unix timestamp is in cell A1:
      =(((A1/60)/60)/24)

      Then, you need to add the result to the date value for January 1, 1970. The modified formula is:
      =(((A1/60)/60)/24)+DATE(1970,1,1)

      Finally, you need to adjust the formula for the GMT offset. For example, if you're in New York the GMT offset is -5. Therefore, the final formula is:
      =(((A1/60)/60)/24)+DATE(1970,1,1)+(-5/24)

      A simpler (but much less clear) formula that returns the same result is:
      =(A1/86400)+25569+(-5/24)

      Both of these formulas return a date/time serial number, so you need to apply a number format to make it readable as a date and time.

      I've found that the custom format m/d/yyyy hh:mm:ss works pretty well. Be sure to make your cell wide enough to accommodate the size of the value otherwise you'll get ##### displayed in the cell.
      I used the formula =DATE(1970,1,1)+A5/86400
      After I do all of this your number turns out to be 9/15/2017 11:36:58
      If you want just the 11:36:58 part you'll need to copy the 9/15/2017 11:36:58 and Paste Special/Values into another cell and copy the 11:36:58 into another cell. At this point it will be text, so if you want to do a DATEDIF or other calculation you'll need to use the first conversion for that.

  40. we have cumulative overtime data in 45:00:00 format (actually in cell it shows like 01/01/1900 9:00:00 PM)
    I want to convert it in number value like 45 (it must not in time format)

    1. Nitin:
      Have you tried to format the cell as a number?
      It sounds like it is formatted as something else.
      If formatting it as a number doesn't work try the custom format [H]:MM:SS

  41. Sir,

    I want to convert time (06:30) to number (0630) in excel.

    1. Vikash:
      I think the simplest way to convert a value that is already formatted as time in Excel is to copy the values from Excel and paste them into Word as plain text.
      When they're in Word, replace the ":" with nothing, then copy these numbers and paste them into Excel.
      When they're in Excel, format the cells as Custom format "0000" and you'll see the values displayed as you need them.

  42. Al:
    If you change the formatting to another Time that is included in the Time list, does this solve the issue?

  43. I'm trying to calculate the difference between regular worked and overtime hours (hours over 40)

    I have this formula to calculate the cell of up to 40 hours
    =IF(E36=40,J36-40,0)

    all I get is "0.00" or "####################..."

    HELP!

    I then need to multiply the product of these figures by the hourly wage.

    Thank you

    1. Al:
      How is the cell formatted that holds the If statement?
      This looks like a cell formatting issue.

      1. Its a custom format [h]:mm

  44. How do I convert 1:45:87 (minutes: second: 100ofsecond)
    The result should be in second ex (105.87)second
    But unable to enter because its automatically takes 1:45:87=1:46:27 please somebody help

  45. Hi hope you will be fine,

    i want to convert number to time format and i used this formula =left(A3,2)&":"&right(A3,2) and its giving me correct time format on below number 1234,1232,3145,

    i want to know how to convert these number exe, 0123,0234,0012,0020,0003.

    kindly help me out in this regard

    1. =IF(LEN(A3)>2,LEFT(A3,LEN(A3)-2),"0")&":"&IF(LEN(A3)>1,RIGHT(A3,2),"0"&RIGHT(A3,1))

  46. I’m wanting to display 12:34 as 12.34.

    1. Winston:
      Let's say you want the original number in cell A2.
      Before you enter anything into A2 format the cell as text.
      If you want the modified number in B2 then format B2 as text before you enter:
      =SUBSTITUTE(A2,":",".")

  47. I would like to convert "39 hour(s), 6 min(s), 31 sec(s)" to a decimal

  48. Hello Guys
    i want formula that if i put 1430 ,it should convert to 14:30. plz help

    1. Aleem:
      There are several ways to do that, here is one:
      Where original number is in A2 enter this into A3:
      =TIME(LEFT(A2,LEN(A2)-2),RIGHT(A2,2),)
      Then format A3 in the time format you want to see.

  49. I want to IN-OUT Time Calculation formula for my company Register in soft format.

    Worker come in 8 Hrs shift = P

    Night + First (next day) = P
    8 Hrs + Duty = P + OT Hrs

    required formula as above condition.

    IN Date IN Time Out Date Out Time Preset OT Hr
    Absent
    01-10-17 6:30 01-10-17 18:30
    01-10-17 22:30 02-10-17 8:30

    1. Hello,

      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com.
      Please also don't forget to include the link to this comment into your email.

      I'll look into your task and try to help.

  50. HI Team,

    Please let me know how to convert minutes to hours for example I need to convert 120:33:00 to hours & minutes.

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

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