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 5. Total comments: 469

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

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

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

  4. Thank you so much.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  27. Hi

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

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

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

  31. Thank you. You saved my day.

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

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

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

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

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

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

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

    Thanks in Advance.

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

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

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

  42. How can I calculate the time into hours?

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

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

  44. Hi everyone,
    How can I calculate agricultural land units, I have

    20 Marla=1 Kanal
    8Kanal=1 Acre (i.e. 1 Acre is equal to 160 Marla)

    How to Add, Subtract, Multiply or Divide these units ?
    kindly help

  45. How do I show only values and not "0"
    examples
    Time result 0:03:51 would show as 3:51
    Time result 0:30:10 would show as 30:10
    Time result 0:00:22 would show as :22 or 0:22

    The current cell formula is
    =TIME(N160,Q160*60,P160)
    Thank you

  46. I have data that is formatted in the following manner.

    "22 minutes" where the minutes are written as a number followed by the word "minutes" as well as "1 hr 30 min" where the time is larger than an hour and they are written with a number for the hour followed by "hr" and the a number for the minutes followed by "min". Is there a way to first separate the two different notations and then convert the numbers to a format hh:mm:ss? Any help would be appreciated.

    Thanks!

  47. Hai All,

    How to convent from number to time e.g.085600 to 8:56:00 AM

    Thanks

  48. Hi Elnur,

    You can use below formula to count 24 hrs time:-

    suppose your 1 time in column A and 2 time in column B then type-
    =((b2-a2)+(b2<a2))

    change number format to hh:mm by pressing ctrl+1.

    enjoy...!!

  49. Hi,
    we have a daily reports and they are counted from 20:00 hours to 08:00 hours, I found a problem counting the hours,

    for example if work started at 23:00 and completed at 06:00 hours, what formula shall I use to get right result?

    xls. simply takes 23:00-06:00 = 17, which is not right,

    I found a way but I believe the is must be a easier way??

    Thanks

  50. thanks

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