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

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

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

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

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

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

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

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

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

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

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

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

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

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

  14. Hi

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

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

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

  18. Thank you. You saved my day.

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

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

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

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

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

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

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

    Thanks in Advance.

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

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

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

  29. How can I calculate the time into hours?

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

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

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

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

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

  34. Hai All,

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

    Thanks

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

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

  37. thanks

  38. HOW TO COUNT TOTAL MONTH HR (MINES ATTEND... SHRRT DATE 26 OCT TO 25 NOV TOTAL 31 DAY ,SO HOW TO COUNT 31 DAY TIME

  39. Hi All,
    I try to calculate total hours and minutes for below list of values.
    05:07:00 05:33:00 06:29:00 06:29:00 06:28:00 07:01:00
    (Those values are placed from B3 column the G3 column)
    I tried this formula
    =MINUTE (SUM (B3: G3) /60) &":"&SECOND (SUM (B3: G3) /60)
    Now I have got exact time, but there are some discrepancies in minutes.
    As per the formula I need to get "37:07:00", but I used to get "37:10:00".
    Kindly help some one to solve this issue.

    Regards

    Kannan Mohan

  40. Hi all professionals,
    could you please helping me to calculate from number to be a time, ex: 7.15 to be 7:15 ?

    Thank you in advance..

    Daovon

    1. Hi Daovon,

      The first thought that comes to mind is using the Replace All feature to change all dots (.) to colons (:). As soon as you do this, Excel will interpret your entries as times.

  41. hello, i'm adding the numbers of hours worked but when i autosum the values, it is not getting the right result. how to i add time values in decimal point? thank you

  42. Is there a way to set an excel equation so that for time, one box says 0700 the next says something like 0930 and the net time box says 2.5 ?

  43. I have a big spreadsheet with times spent on a task stored in this format
    dd:hh:mm:ss (days:hours:minutes:seconds)
    The second column shows how many tasks completed in that time (usually in the thousands).
    Is there a simple way to show how long is spent on each task?

    Thanks

  44. i have attendence machine report in excel where its mantioned 10.6 in general foramt when we convert that in to time its showing 2:06 how can we solve this problem kindly help

    thanks

  45. Thank you so much for this valuable effort. Please I need your help in calculating the daily over time as i have 2 column for; "IN" (Hr:Min AM) & "OUT" (Hr:Min PM) and the regular working hours shouldn't be less than 7 Hours started from any time between 7:00 AM to 8:00 AM and finished at anytime between 2:00 PM to 3:00 PM if it is not within the regular range should be deducted from the overtime. Then to color the exceeded time in Cyan
    Thank you so much

  46. I am trying to convert Regular hours which is in format hh:mm to straight time hours, for example of 2.95 equals 2.57 becuase we divide 0.95 by 60 to arrive at 0.57. I tried using =Hours(cell) + Minutes(Cell/60), I get 0:00

    Please guide

  47. Hello,
    I need to calculate the duration of certain activities: start - stop = duration. The start stop cells are formatted: 10/18/2016 20:05. I need the difference in numeric form ( not text ). To make it harder there only 12 production hours per day. So, if production ends at 17:00 and restarts at 5:00 the task, for example, started at 13:30 and ended the next day at 8:00. The actual task time is 6.5 hrs. I don't want to include the non work time. I need the results in decimal format. Is this possible?

    Please let me know. Thanks

  48. I am trying to convert 35.66 (this is 35 hours and .66 of an hour) into days:hours:minutes
    The manual conversion is 1:11:40 (1 day:11hours:39.6 minutes)
    is there a formula for this so that I do not have to convert manually?
    Can anyone help with this?

  49. Hi all, I am trying to convert hours into day:hour:minute.
    e.g. 208.03 and 852.47 to be converted into 28:2:14 and 115:7:13. Is anyone able to assist?

  50. Hi everyone there
    I need your help, to solve my problem
    working hours in my workshop in 3 shifts
    shift 1 starts 06:40 Ends 15:20 with break time from 11:45 to 12:20 total of 0.40 Min.
    shift 2 starts 15:00 Ends 23:20 with break time from 19:00 to 19:20 total of 0.20 Min.
    shift 3 starts 23:00 Ends 07:20 with break time from 03:00 to 03:20 total of 0.20 Min.
    My quistion is how can i calculate the exact working hours for each shift without the break time hours by usiing excel formula.

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