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

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

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

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

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

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

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

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

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

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

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

  11. Thank you so much.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  26. 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,":",".")

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

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

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

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

  31. Please help me I need to change 1.50 to show 1.30, or 1.25 to 1.15 how can I do that

  32. Kindly assist me on how to display 130 minutes as integer in excel program

  33. How do you separate elapsed minutes or seconds with thousands separator? I have calculated times between two dates and custom formatting will not let me separate the minutes or seconds accordingly.

    Thanks

  34. Hi

  35. How to convert "22.91" in Minute and Second?

  36. Question: How to solve Date and Time in Excel formula if start one day and end next day?

    I have issue if I have

    Start time: 7/7/2017 23:28:00 (Time start at evening PM) and
    End time: 7/8/2017 00:00:00 (Time end next day in the morning AM)
    I want to subtract End Time to Start time but not getting correct result.

  37. Hi,

    Some question,
    -I have in line A 2 shifts Morning & Evening( with end & start times)
    - Line B gives my the worked hours for AM & PM( end time - start time)
    - Line C should give me the total hours ( 27 : 00 ) but instead it always give me the time 03:00.

    Any workaround

    A: STU 1 3:00 - 23:00 18:00 - 1:00
    B: TOTAL 20:00 7:00
    C: DAY TOTAL ???03:00?????

  38. Thank you. You saved my day.

  39. Hi Team,

    Am trying to caluculate the time difference in same but am unable to do that, could anyone help me on the same.

    09:00 AM-06:00PM in one cell another i need the number of working hours

    Please suggest me...

  40. Dear Excel user's

    I have entered time for 6.10 mins in Excel (i,e 6 hours and 10 mins) how to i get 310 minutes using the formula Pls help us.

    1. Dear Suresh,

      please note that 6 hours and 10 mins is 370 minutes. But if you need 310 minutes exactly and 6:10 is stored in A1, please use the following formula:
      =(A1*1440)-60

  41. when am (70+12)/60 I got answer as 1.20 hours but the correct answer is 1.12 hours how can I 1.12 hours.do u have any formula for than...please reply as soon as possible

  42. hi,
    i wants to calculate over time .our organization start overtime after 30 minutes to the regular time. i am unable to set overtime formula .
    Please guide me an Example ..i am using Excel 2013.
    Best regards,
    Abdul Rehman

  43. Hi,
    i am making attendance sheet and i am unable to get the result when an employee works less then the regular time ,so i need his hours in Negative numbers.please guide me how can i get negative numbers.i am using h:m format in excel 2013.
    Best regards,
    Abdul Rehman

  44. How to Write 15:30 into 15 hours 30 minutes?
    i don't have any format like this in format cell- custom

    1. You can write (hh "hours" mm "minutes") in custom window, without the brackets(). I have written it just to make it look different from other text.

  45. How to write 15:30 into 15 hours 30 minutes?

    Thanks in Advance.

  46. I need to convert minutes to individual units
    column A has the date, column B has the start time column C has the end time

    8-22 mins = 1 Unit
    23-37= 2 Units
    38-52 = 3 Units
    53-67 = 4 Units

    The Units are made so you can choose them from a drop box.
    I need it so that the times = the correct number of units, if they enter the wrong units or no units it would highlight. Or else if the time equals 7 mins or less that the time is highlighted. PLEASE HELP

  47. Hi,

    I have a Question for my offical report work.

    i used this formula to =INT(I2-A2) & " days, " & HOUR(I2-A2) & " hours, " & MINUTE(I2-A2) & " minutes and " & SECOND(I2-A2) & " seconds" to get the exact days,hours,min,&sec . in this scenario i need to covert this values into percentage .

    My task time is 5 hours if i exceed the limit the cell wants to show the exact percentage

    eg1 : i received one mail on 22 jan 2017 12:14:20 pm and i replied to that mail on 22 jan 2017 20:41:20 pm . it exceed my task line so could you please help me what formula should i used to solve the issue.

  48. Anyone out there, can you help me, please?

    I am trying to convert 21 hours 36 minutes to a decimal format in Excel, for a part-time weekly timesheet i.e. 21.6 hours, what formula do I need to use to achieve this?

  49. How can I calculate the time into hours?

    Day In Out Regular Hours
    1 7:13am 1:15pm

  50. How would you calculate an end time if the start time is 8am and the hours worked is 6.75?

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