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

  1. Kindly help. How do I convert 7h 27m 51s to HH.MM

    1. Hi!
      Have you tried the ways described in this blog post? Use example "Formula 3: HOUR, MINUTE and SECOND functions".
      If this is not what you wanted, please describe the problem in more detail.

  2. Hi,

    Can you please help me:

    2m 46s need to change in this form 00:02:46

    thanks in advance

    1. Hi,
      For your data, you can use the formula:

      =TIMEVALUE("0:"&LEFT(C1,1)&":"&MID(C1,4,2))

      Pay attention to the following paragraph of the article above: Convert text to time using TIMEVALUE function

  3. don't know if you can help but kevin asked the same question of 5th feb
    I have task that need to be completed each different time but I am sure if I get the one calculation right it can be change for that length of time for example.
    1 task takes 20 mins over a week they do 392 which in my head is 20 x 392 = 7840 minutes
    my simple head is divide that by 60 to gat 130.67 but then if I use =CONVERT(M204,"mn","hr")/24 I get 5.44 that convert to 10 hours 40 minutes it seems which way I use a formula I get a different answer
    the other times are
    1 task x 20 mins
    1 task x 30 mins
    1 task x 75 mins
    1 task x 45 mins
    but I I can get the right formula changing the length of time should be easy …….. or will it
    what is the right formula for this to give me the right length of time in hours and minutes for all these jobs
    thanks in advance

    1. Hello!
      If I understood your problem correctly, then to convert minutes to hours use the formula

      =A1/60/24

      Then set the cell custom format to "37:30:55"
      I hope my advice will help you solve your task.

  4. Can I please have 1 days 4 hours 21 mins converted to 28.21 in Excel

    1. Hi,
      Please describe your problem in more detail. How is your original data written as 28:21:00 or as the text "1 days 4 hours 21 mins"?

  5. Hi There,
    I need to convert 08:30:24 into hours and minutes only. tried with above but failed, Kindly help
    THanks
    Stay safe.

    1. Hello!
      If I understand your task correctly, you can use custom time format —

      "hh:mm"

      I hope it’ll be helpful.

  6. How to convert 56 to 056:00 in Excel
    Thnx

    1. Hello!
      If I understand your task correctly, the following formula should work for you:

      =TIME(0,56,0)

      Use a custom date format

      [hh]:"0"mm:ss

      I hope this will help

  7. Hi there,

    Is it possible to convert time 08:30 to 08.30 instead of 08.5

    Thanks,

    1. Hello!
      Time cannot be shown using a point. But you can convert it to text

      =TEXT(A1,"hh.mm")

      I hope my advice will help you solve your task.

  8. Hi,

    How could i convert shift times in to hours as a formula.

    For example, if someone is working from 6am - 2 pm 5 days a week, how can I get the total hours worked calculated?

  9. I am trying to keep track of my employees hours worked in Excel. I do not need to calculate their "In" and "Out" times, but instead calculate their bi-weekly total hours to ensure they do not go over their allotted 69 hours per month.

    Example:
    Debra worked 31.45 hours for pay period 1 of 2.
    Right now I have =69-31.45 to show me how many hours she has leftover for the month. However, it is giving me 37.55 - which is incorrect.

    How can I change the whole number in Excel from 100 to 60 so it will calculate her remaining hours?

    I hope this makes sense! I have been on a hunt to figure this out - YouTube, Google, my Excel book and I cannot figure this out.
    Any help with this is SOOOO appreciated :)

  10. I'm trying to take a Start Time and add 1 minute per question being answered to calculate an end time.
    What I currently have:
    (F13) Time Format (HH:MM) Start Time: Manually enter Time "example. 21:00"
    (G13) Number Format Total Questions: =(Results!K3) "example. 153"
    (H13) End Time: =SUM(TIMEVALUE(F13:G13)) "Result: Error Value"

    Could someone help me resolve the error?

    1. Hello!
      If the number of minutes is written in cell G13, then you can add minutes to all of them with the formula

      =F13 + G13/1440

      I hope I answered your question.

  11. How do you do the first example you shared if the time is greater than 12? for example, I have time 23:56
    and I would like to convert it to minutes. When I use this formula =(R11*1440) I am getting 14396 - this should be 1436. Why is this happening? Thanks!

  12. I want to convert minutes in HH:MM:SS format but condition is minute showing in General

    For Example

    Total min 200 is nothing but 03:20:00

    Is there any formula to convert like mentioned above?

  13. I need to get the year, month, and day as separate integer values from a date such as 7/13/2014.

    How do I do that?

  14. am try to convert actual minutes to decimals such as 4.11 hours worked to equal 4.18 hours and 3.32 hours to 3.53 and so forth so it works like a time clock where 15 minutes =.25 -30 min =.5 45 minutes =.75 thank you in advance

      1. I am also having a problem like this. Due to a disability i need to do my my timesheet in excel. we use 24 hour time (military) but they want the difference expressed as a decimal. For example, Start Time 11:30 End Time 16:45 = 05:15 but they want the time sheet to say 5.25 (don't trust my math) I'm sure you addressed it earlier but if you did I didn't get it. I would appreciate your guida

  15. bonsoir,
    j'ai un petit problème sur excel, c'est que j'ai besoin de créer des colonnes avec ces donnée

    - column 1: Julianne day (1 to 365)
    - column 2: Solar time in decimal format (00:00:00) with interval of 5 min (from 1 to 12 for each hour))

    mon problème c'est que j'arrive pas a trouver une formule pour gérer l'intervalle de 5min sur un ans.

    merci

  16. August 1, 2020 3:52:15 PM AST
    how to change this one as date format , tried a lot, can you please guide me.

  17. 2 days 19 hours 40 minutes

    Its convert to 67.40 Dec... Can u plz send the excel formula

    1. Hello!
      What does 67.4 mean? What are these units of measurement?
      2 * 24 + 19 + 40/100? This number doesn't make sense.

  18. i have 261 minute. needs to be convert as HH:MM

    1. Hello!
      The formula below will do the trick for you:

      =CONVERT(D1,"mn","hr")/24

      Set the cell time format hh:mm

  19. Hello;
    Can you please help convert decimal 37.5 to hours and minutes.
    When converting a number under 24 hours the formula number/24 works but if totalling hours over a week, the formula does not work if the total goes over 1 day or 24 hours.
    Thank you

    1. Hello!
      To be able to show more than 24 hours in a cell, please use this time format: "37:30:55". You can find it in the list of Excel time formats.

    2. I am looking for the excel formula please :0)

  20. Hi,
    How to convert 9,30 in decimal 9,50 or 8,45 in 8,75.
    The times I receive in Excel are the effective times marked with a comma (none :)
    tnx

    1. Hello!
      The rounding of numbers in your examples occurs according to different rules. Therefore, I can not offer you a general formula for your two tasks. I recommend paying attention to the MROUND function. For your first example, the formula is suitable

      =MROUND(A1,0.5)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  42. Hi all,

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

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

  44. THANKS AND REGARDS FOR SUPPORT

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

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

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

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

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

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

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