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

  1. I would like to convert time to a numerical number to add up total hours.
    Example:
    6:00AM-2:00PM=8 hours

    1. Hello Erica!
      Specify what you want to calculate. Your formula counts the time from 2 PM to 6 AM. And this is not 8, but 16 hours. If you want to calculate the time from 6 AM to 2 PM, then the formula is necessary
      = 2: 00 PM-6: 00AM
      To convert a result from time to number, use the formula
      = (2:00 PM-6:00AM) * 24
      And set the number format in this cell

  2. Hello dear..
    I am from Indonesia..
    How to stop auto convert number from general to custom
    ex.
    from 1. auto convert to 01.00
    from 10. auto convert to 10.00
    from 100. auto convert to 100.00.00

  3. How do you convert 06:55 into 0655 in Excel. I want to to eliminate the colon.
    Thank you.

  4. I've downloaded a csv file that has a time on it but when I convert to Excel and use a formula on it it shows as the decimal equivalent of the number and not the time ,I cannot get to change to a time format that equals the original downloaded number/time

    1. Hello Geoff!
      Please try the formual below to convert time from a decimal number to "hh:mm:ss":
      =TIME(D1, MOD(D1, 1)*60, 0)

      That should do the trick.

      1. thank you but the issue I have would be say i wanted to concatenate 2 cells ..... one with contents CPark and the other 10:46 AM the concatenate function results CPark0.4486111111111111 rather than CPark10:46
        your suggestions effect upon the original 10:46 AM cell returns 12:26AM
        thank you for your help so far though

        1. Hello Geoff,

          Thank you for the clarification. In this case, formual below will convert your data from time to text:
          =CONCATENATE(A1, TEXT(B1, "hh:mm AM/PM"))

          That should do the trick.

          1. working now thank you Alexander !!!!

            1. so when I use a look up to cross reference 2 sheets in the same workbook even though I have the concatenate correct it doesn't pick up the cell I need ...can anyone have a look at the file if I send it to them to see where Im going wrong please ? I'm sure its due to the time format mentioned above that even though they look the same in the cell ref they are not .....

          2. sorry that is not the answer thanks Alexander although its probably on the right track
            I cannot find the solution anywhere on the internet

  5. Hello,
    In my excel spreadsheet I have column totally the number of hours in total (ex: from (9 am to to 10:30 am = 1.3) I would like to convert the 1.3 to 1.5. How add this function in my existing formula: =IF(k3≥J3,K3-J3,1-K3+J3)*L3..... L3 being the hours in total.
    Thank you!

    1. Hello Danielle!
      If I understand your task correctly, you need to convert time from HH:MM:SS format to decimals. If so, you just need to multiply it by 24 (for example, =А3*24) and set the General format for this cell.

  6. Has anyone actually found a resolution conversion for "D HRS:MIN:SEC" to "HRS"?
    eg. 1 22:12:00

  7. I know I'm asking a lot here but could you explain how to convert "Duration in seconds (Unix)" to a readable format of years,months,weeks,days,seconds in Excel? I see all sorts of ways to convert date to date, etc but not "duration" of time in seconds! I need a way to convert it into a readable format of years, months, weeks, days, hours and seconds. I can do it in Perl, C and others but not in Excell. If you can help I have a few examples of what the end result needs to be in specific representations such as (11W) for 11 weeks or (13W2D) for 13 weeks and two days or (1Y26W6D) 1 year, 26weeks, 6days and even further. Here are a few examples...

    Duration in Seconds:
    1) 6652800 and the output would be 11W or 11(W)eeks
    2) 8035200 and the output would be 13W2D or 13(W)eeks 2(D)ays
    3) 47779200 and output would be 1Y26W6D or 1(Y)ear 26(W)eeks and 6(D)ays
    4) 47779200 seconds and output would be 1Y6M5D3H16M51S or 1(Y)ear,6(M)onths,5(D)ays,3(H)ours,16(M)inutes,51(S)econds
    and/or convert years into months...
    5) 47779200 output to 18M5D3H16M51S or 18(M)onths,5(D)ays,3(Hours),16(M)inutes,51(S)econds

    Does this make sense? I'm not in a big hurry so if this is to much at this time don't worry about it. I'll keep looking around and I want to say: I really appreciate your taking the time to help with your explanations and this web site, these examples and explanations some of my make daily stuff much easier and I really appreciate how you offer so much expertise to everyone.

    Many Thanks!!
    Dave C.

  8. 11.2hrs convert to days,hrs,min
    formula pls?

  9. If Hour minute and second value 1370:06:09 in this format then how to separate hours minute and second.
    Pls suggest.

  10. how to convert in date & Time format below value
    20001124204143.0Z

    1. I will try with Mod formula but 2020 value showing wrong this will shows in filter as 2020 Jan to Dec but still running feb month and when we check in Ctrl+Shift+3 format in 2020 date then showing another date.

  11. Hello everybody!
    I can't find within the answers … I'm having difficulties with changing minutes and second in format 35:12 into seconds. Result I am looking for should be 2112.
    Thanks"

  12. hour calculation - after sum total hour is 18:90: I want to I change from the 18:90 (18 hrs 90 min) in order to become 19 hrs 30 min. Can you I change it in Excel?

    thank you

  13. Start 6:45 7:28 7:00 7:27 6:28 6:59 13:00
    Finish 22:48 23:00 23:35 22:15 22:54 22:31 22:03
    H:min 16:03 15:32 16:35 14:48 16:26 15:32 9:03
    How do we add these hours and mins to hours only in Excel?

  14. Trying to convert to below to minutes with out text

    Notify to Last Assign Transit Time Total In Progress Time Turnaround Time
    1h 14m 1m 40m 58m

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

  16. I WANT TO ADD TIME VALUE AS U/M:-
    A1 = 93:01:00
    B1 =13349:50:00
    C1 = REQUIRED FORMULA TO ADD ABOVE MENTIONED TIME VALUE.
    THANKS

  17. Sir,

    Date Shift ArrTim LateHrs DepTim EarlHrs WrkHrs OvTim Present

    01/12/2019 G 0.00
    02/12/2019 G 9.56 18.00 8.04 1.00
    03/12/2019 G 9.56 18.01 8.05 1.00
    04/12/2019 G 9.46 18.01 8.15 1.00
    05/12/2019 G 9.57 18.01 8.04 1.00
    06/12/2019 G 9.47 18.18 8.31 1.00
    07/12/2019 G 9.57 18.00 8.03 1.00
    08/12/2019 G 0.00
    09/12/2019 G 9.47 18.00 8.13 1.00
    10/12/2019 G 9.56 18.01 8.05 1.00
    11/12/2019 G 9.57 18.00 8.03 1.00
    12/12/2019 G 9.47 18.00 8.13 1.00
    13/12/2019 G 9.58 18.01 8.03 1.00
    14/12/2019 G 10.26 0.26 18.00 7.34 1.00
    15/12/2019 G 0.00
    16/12/2019 G 10.05 0.05 18.01 7.56 1.00
    17/12/2019 G 9.56 18.00 8.04 1.00
    18/12/2019 G 9.56 18.00 8.04 1.00
    19/12/2019 G 9.57 18.00 8.03 1.00
    20/12/2019 G 9.57 18.01 8.04 1.00
    21/12/2019 G 9.58 18.00 8.02 1.00
    22/12/2019 G 0.00
    23/12/2019 G 9.56 18.00 8.04 1.00
    24/12/2019 G 9.57 18.00 8.03 1.00
    25/12/2019 G 9.57 18.00 8.03 1.00
    26/12/2019 G 9.55 18.00 8.05 1.00
    27/12/2019 G 10.06 0.06 18.00 7.54 1.00
    28/12/2019 G 9.53 18.00 8.07 1.00
    29/12/2019 G 0.00
    30/12/2019 G 9.58 18.00 8.02 1.00
    31/12/2019 G 9.58 17.09 0.51 7.11 1.00

    kindly solve this how we calculated the total working hour's in that sheet
    here is mentioned that in time and out time and difference how many hour's working an employee in our office and after how to calculate total working hour's in a month kindly reply

    Thanks.

  18. Please support how to convert time into hours duration i.e. OUT Time 10:23:28 AM and IN Time 7:27:31 PM need to calculate the duration in Excel for bulk data.

    Thanks.

    1. Hello Shahid,
      If you need the time difference in hh:mm:ss format, here is the formula for you:
      =IF(A2<A1, A2+1, A2)-A1

      Supposing the IN time is in A1 and OUT - in A2. If however, you need it to be a just number, the following formula will do the job:
      =HOUR(IF(A2<A1, A2+1, A2)-A1)+MINUTE(IF(A2<A1, A2+1, A2)-A1)/60

  19. How can convert 1 productivity = 8:30 hours in excel sheet example .5 = 4:15hours and .25 = 2 hours
    Time difference 15,30,45,60 minutes

  20. I WANT TO CONVERT HOURS TO minutes
    A/S CAST OFF TOTAL HRS TOTAL minutes
    15:50 19:25 3:35

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

  22. Hi all,

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

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

  24. THANKS AND REGARDS FOR SUPPORT

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

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

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

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

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

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

  31. How to convert number 102 to HH:MM

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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