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

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

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

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

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

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

    Thanks in Advance.

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

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

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

  9. How can I calculate the time into hours?

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

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

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

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

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

  14. Hai All,

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

    Thanks

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

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

  17. thanks

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

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

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

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

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

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

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

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

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

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

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

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

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

  31. Hi There,
    I have calculated elapsed time, but rather than excel using ":" as time separator, I want to use "h" for hours and "m" for minutes. For example: 01:50 should be displayed as 01h50m. I want to keep it formatted as a time to be able to total up elapsed times later.
    Thanks

    1. I discovered by experiment that you can use a backslash to escape the character you want to use as text in the format. So for your case the format field should contain hh\hmm\m. This works both in the custom format field for a cell and in a TEXT() function. For example, =TEXT(0.25,"hh\hmm\m") displays text 06h00m.

  32. I see an error in your formula for Converting time to hours in Excel

    =HOUR(A2) + MINUTE(A2)/60 + SECOND(A2)/360

    That last value should be 3600.
    I found your tutorial but kept getting wrong values.
    This is when I discovered that error.

    1. As Jim (post 32) has already noted in September, the formula to convert time to hours should read
      =HOUR(A2) + MINUTE(A2)/60 + SECOND(A2)/3600, not 360! It does make a difference. Could you plse change that in the text.
      Thanks, Paul

  33. Hello guys,

    Anyone could help me convert total time to decimal? Ex. 58:16hrs to be converted to 2d 10.26h.

    Thank you.

  34. Hi all,

    I am trying to multiply x number by minutes and seconds and then get the result in days, hours and minutes.

    For example, if I have 1100 units and each unit takes 4 minutes 50 seconds to produce, what formula would I use to determine that it takes x days, y hours and z minutes to produce the 1100 units. Not too concerned about seconds.

  35. Dear all,

    could you give me one hint :)
    I'd like to sum/add decimal number with general number.
    Can I do it ? I was looking for but I couldn't find. Thank you!

  36. I FETCHED DATA IN EXCEL FORMATE FROM ONE PLACE AND I GOT 357564 AS BUSINESS ELAPSED TIME,WHICH IS DAYS,HOUR AND MINUTE..HOW WILL CONVERT 357564 INTO DAYS HOURS AND MINUTES

  37. Hello, need help.
    how to auto calculate the hours below the situation.
    If have 12hrs kpi(key performance indicator)Before vessel arrival from the port of origin and then If the vessel arrived @ 02:30AM,8-29-15. what time supposedly ill sent my reports.

  38. I am trying to convert clock in time from a 60 minute clock to 100 minute military time. How do I convert the minute portion of the time? For example: 7:52:03am should be 7:86:03am (I need to multiply the MM by 1.66)

    1. Hello Lisa,

      To get it right and consider the AM/PM time, please use this formula:
      =LEFT(TEXT(A1,"h AM/PM"),LEN(TEXT(A1,"h AM/PM"))-3)&":"&IF(INT(MINUTE(A1)*1.66)<10," ","")&INT(MINUTE(A1)*1.66)&":"&TEXT(A1,"ss AM/PM")

      Where A1 is the cell with the original time.

      • LEFT(TEXT(A1,"h AM/PM"),LEN(TEXT(A1,"h AM/PM"))-3) gets the numeric symbols of time including AM/PM.
      • IF(INT(MINUTE(A1)*1.66)<10," ","")&INT(MINUTE(A1)*1.66) gets minutes with leading 0.
      • TEXT(A1,"ss AM/PM") gets seconds with leading 0 and AM/PM.

  39. I am trying to add columns of hours, minutes and seconds. I was able to convert the columns into total seconds using =(C2-INT(C2))*86400. I then add the columns of total seconds together. I then take that value and divide by 86400. However when I try to convert my final value back into a time format, I am having trouble with cells that over 24 hours. I have tried using [HH]:MM:SS

  40. Hi,

    I’m trying to convert/separate the following date/time format “8/24/2016 17:05:36:242” in a workable format, ideally to numbers/values. I had various attempts: define various (custom) cell formats, using MOD function etc., but no success so far. Do you have any suggestions?
    Thanks a lot.

    George

  41. Hi,
    I am trying to make a time-sheet for my working hours.
    i have a column with time started and time finished. The 3rd column is the difference between the 2 (hours worked for that day). I do this for every day of the week (rows). In the next column I want to sum my hours that I worked per day, but I want to use a formula. In this column I don't want to use time, but a number. What formula do I need to use.
    Example
    Start Finish Hrs worked (time) Sum hours (numbers)
    9:00 12:00 03:00 (=finish-start) 3 (=convert hrs worked)
    9:00 14:00 05:00 (=finish-start) 8 (=sum hrs above+hrs today)
    10:00 17:00 07:00 (=finish-start) 15 (=sum hrs above+hrs today)
    08:00 18:00 10:00 (=finish-start) 25 (=sum hrs above+hrs today)

    I have trouble finding a formula that can calculate the last column as time doesn't go further than 24 hours. i want to do this for the whole year.

    Thank you!
    Monique

  42. Hi,
    I am trying to make a time-sheet for my working hours.
    i have a column with time started and time finished. The 3rd column is the difference between the 2 (hours worked for that day). I do this for every day of the week (rows). In the next column I want to sum my hours that I worked per day, but I want to use a formula. In this column I don't want to use time, but a number. What formula do I need to use.
    Example
    Start Finish Hrs worked (time) Sum hours (numbers)
    9:00 12:00 03:00 (=finish-start) 3 (=convert hrs worked)
    9:00 14:00 05:00 (=finish-start) 8 (=sum hrs above+hrs today)
    10:00 17:00 07:00 (=finish-start) 15 (=sum hrs above+hrs today)
    08:00 18:00 10:00 (=finish-start) 25 (=sum hrs above+hrs today)

    Thank you!
    Monique

    1. I am also looking for an answer to this - have you had any luck Monique?

  43. Hello, this has helped a lot, I have been able to calculate work units into total hours, but now I want to convert total hours calculated, 466:56 - into FTE.

    Where 466:56 is the work units required to be worked in a month, and 168 hours is the amount of hours a staff member works a month so the FTE should be around 2.8

    Dividing the cell with (=F11/189) 466:56 by 189 in does not work as the number in the "=F11" is shown as 19.46.

    Hope this is clear, thanks.

  44. Our time is currently entered as 41:30:00 for 41 hours 30 minutes and 0 seconds. I tried using your convert time to hours formulas but I come up with 17.5 which is clearly not correct. Can you tell me what I am doing wrong?

    1. Suppose your value is at cell 'A1' then use-
      =INT(A1)*24+HOUR(A1)+MINUTE(A1)/70+SECOND(A1)/3600

  45. Hi there
    I'm trying to convert 0d 1h 6m to a numerical number any chance of helping out I've tried to use a couple of variations to formulas on here but cant seem to get them to work any help would be appreciated.

  46. great tutorial. My question is this. At my job I have to complete so many "points per hour" so if I have A1=7:00 and A2=17:00 and I have completed 72 points, which is located in A3. How would I calculate my points per hour? Thanks in advance

  47. I am looking to do the following:
    - calculate time from two given values, and convert into a decimal *that rounds to set criteria*.
    We use a time calculation where
    0-2min = 0.0
    3-8min = 0.1
    9-14min= 0.2 etc. so the criteria would have to be established.

    Example:
    START: 14:00 STOP: 15:03
    =TIME: 1.1

  48. Hello,
    I have a column for hours, another for minutes and another for seconds:
    Hr Min Sec
    7 15 22
    7 17 25
    7 17 45
    How do I combine these into a single time formatted column?
    Time
    7:15:22am
    7:17:25am
    7:17:45am

    Thanks in advance,
    Brian

    1. HI,
      HOW DO I GET 1212 TO 12:12:00??

      1. =TEXT(F7,"00\:00")

  49. How do i change the time format from 10:00 into 10+00. (10 is the hr and 00 is the minutes.

    1. Hello Karl,

      To change the cell format, do the following. Select the cell(s), press Ctrl+1 to open the Format Cells dialog, on the left-side pane under 'Category' choose Custom, and type the following format in the Type box: hh+mm

  50. Working on a report where I have a measurement of time such as 112:26:56. This time is for the entire month, now how can I covert that time into a decimal?

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