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

  1. Hi,
    Is there a way to convert a length of time in text string formatted like the following to a numerical number such as total days, so I may determine the average length of that time for hundreds of entries.

    10 Years 8 Months 26 Days
    1 Years 4 Months 23 Days
    5 Years 11 Months 5 Days
    0 Years 6 Months 12 Days

    Thank you for any assistance!

      1. I think I have the same issue, but with hours. Our report gives us the cell with: 7 hours 10 minutes 4 seconds, but we need it in the format hh:mm:ss. Cand we transform that?
        Thank you

  2. Hello!

    I could use your expertise, please!

    I'm trying to go from:-

    1 hour 18 mins

    to

    78 minutes

    Is that possible?

    Thanks
    Stephanie

    1. Hello!
      I hope you have studied the recommendations in the tutorial above. It contains answers to your question

      =HOUR(A1)*70+MINUTE(A1)

  3. Hey, I'm trying to convert 61:20:00 to decimal, but the formulas I'm finding online are not working because it's over 24 hours. Any tips on how to get it to work? I'm using Google Sheets.

      1. Awesome! Found the solution in the first link. Thanks so much.

  4. i have looked in the posts and can not get the following to work out. in column a i have "start time", b is "end time", c is "duration"and in column d i need it to be in minutes. ie - (start) 11:08 AM, (end) 3:20 PM, (duration) 4:12 - but can not get the results in minutes. Which formula do i need to convert duration to minutes?
    my formula for duration is b3-a3 (formula is hh:mm

    1. Hi!
      I hope you have studied the recommendations in the tutorial above. Pay attention to the following paragraph of the article above - How to convert time to minutes in Excel

  5. Hi,

    I've received an excel sheet with the format of '# days # hours # mins' per cell in one column. Some cells have '# hours # mins' and others have just '# mins', all in the same column. I'm really stuck on how to convert all into mins without calculating each cell individually. Is there a formula I can apply to update this? Would really appeciate any direction!

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

      =IFERROR(--LEFT(A1,SEARCH("days",A1)-1),0)*1440+ IFERROR(--MID(A1,SEARCH("hours",A1)-3,3),0)*70+ IFERROR(--MID(A1,SEARCH("mins",A1)-3,3),0)

      I hope my advice will help you solve your task.

      1. Hello Alexander, just wanted to say the above helped me so much!!! I searched many forums and articles and you were the only one that had the answer. Thank you

      2. Hi Alexander,

        Thank you so much - it's working, I only need to add a '0' before the data that reads as '2 hours 30 mins' etc, so it's '02 hours 30 mins', otherwise it won't work on those cells. But this is wonderful, thank you! so much faster than what I was working on before!

        1. Hi Amy, is there a formula that could fix this? Running into the same issue

  6. Hi,

    I need to show whether the time elapsed between 2 dates is within the required time frame.
    I have generated the different between the 2 dates using:
    =INT(W4-C4)&" days "&TEXT(W4-C4,"h"" hrs ""m"" mins """)

    And then want to use a simple IF formula to determine if it's within the time frames:
    =IF(Y4>K4, "No", "Yes")
    where
    Y4 is the cell with the first formula
    and K4 is either 0 02:00, 1 00:00 or 5 00:00 (in custom format dd hh:mm)
    This seems to work fine for the 2 hours and 1 day option but "Yes" is always return for the 5 day option.

    I've also tried this where the first formula is a simple =W4-C4 and i have the same issue.

    Can you help please?

    1. Hello!
      The formula in cell Y4 returns text. Therefore, you cannot compare text value with > or < .
      In cell Y4, enter the time in the time format. For example, 2 days is 48:00:00.
      Remember that date and time in Excel are numbers.
      Try this formula

      =IF((W4-C4) > K4, “No”, “Yes”)

      I hope my advice will help you solve your task.

  7. Hi, I'm having a classic problem with a graph being messed up because I have midnight time in my data.
    I solved this once year's ago, but have since forgotten and can't find the answer. Was it something to do with writing that midnight time as decimal number?

  8. Hi,

    I was wondering if anybody can help me with this:

    In an Excel sheet, I have accumulated deducted minutes in cell D1 displayed like this:

    146:50 (146 hrs & 50 minutes)

    cell formatted as: [h]:mm

    I want to display deducted time in 3 separate cells like this:

    H10 G10 F10
    18 2 50
    (days) (hrs.) (min.)

    ** each 8 hours = 1 day deduction

    Thanks in advance,
    Jaafar

    1. Hello!
      To get working days, hours and minutes, use these 3 formulas:

      =INT(D1*24/8)
      =INT(MOD(D1*24,8))
      =(D1*24-INT(D1*24))*60

      Hope this is what you need.

      1. Hi,

        This is what I needed. Problem solved.

        Many many thanks, Alexander.

  9. Hi Hi!

    Would you please assist me on a formula / function that can convert the following:
    03Hrs15Mins06Secs to just the numbers eg. 03:15:06
    34Mins04Secs to eg. 00:34:04

    thank you so much!

    1. Hello!
      Use substring functions in Excel to extract text from cell:

      =TIMEVALUE(IFERROR(MID(A1,SEARCH("Hr",A1,1)-2,2),"00") &":"&IFERROR(MID(A1,SEARCH("Min",A1,1)-2,2),"00") &":"&IFERROR(MID(A1,SEARCH("Sec",A1,1)-2,2),"00"))

      This should solve your task.

  10. Hi & thanks for your most excellent site, it has come in handy for me so many times!

    I have a problem where I'm trying to calculate the difference between two datetimes like this

    Start: 29/Nov/21 11:52 AM
    End: 1/Dec/21 5:38 PM

    Excel nicely converts the above when pasting in to the cells A2 & B2 as follows:

    A2: 29/11/2021 11:52
    B2: 01/12/2021 17:38

    C2: =B2-A2 returns the difference, expressed by Excel as: 02/01/1900 05:46

    D2: =int(C2) returns the days, expressed by Execl as: 2 (format cell to number)

    E2: =MOD(B2-A2,1) gives me just the time, expressed by Execl as: 05:46:00

    So, I can use the above to deterrmine days & hours. If I use 8 hours as a working day, 2 days equate to 16 hours, plus the 5:46, would equate to 21:46 hours:minutes.

    1. I'm wondering if there's a better way than the above?

    2. My second problem comes from having to account for weekends (non-working times).

    I have no idea how to do this. I suspect I'd have to use a calendar?

    Would really appreciate your thoughts.

    Many thanks,

    Derrick

    1. Hello!
      Try this formula in cell C2:

      =B2-A2-NETWORKDAYS.INTL(A2,B2,"1111100")

      The NETWORKDAYS.INTL function counts the number of weekends. In this formula, these are Saturday and Sunday.
      I hope I answered your question. If you have any other questions, please don’t hesitate to ask.

  11. Hi I need to convert the following:

    The original data is 03 Hrs 11 Mins 00 Secs and i need it is look like this 3.11

    I hope you can help me!

    Thanks so much!

      1. Hi! thank you, but i am still not getting the desired outcome.

        Please assist.

        1. Hi!
          I’m not sure I got you right since the description you provided is not entirely clear. However, I’ll try to guess and offer you the following formula:

          =TRIM(LEFT(A1,SEARCH("Hrs",A1)-1))&"."&TRIM(MID(A1,SEARCH("Mins",A1)-3,2))

          If this is not what you wanted, please describe the problem in more detail.

  12. Good day Alexander Trifuntov

    I am trying to convert 30d21h45m into hours on excel using a formula, could you please help.

  13. Looking to average out two columns of numbers based on hours and minutes to find the mean time. Looking for
    whole hour and minute total. Help would be appreciated.

    Count Average Time Total Time
    3 0.479166667 34:30:00 (34 hours, 30 min, 0 sec) [H]:MM:SS turns into 1/1/1900 10:30:00AM
    3 8:16:20 24:49:00

  15. How to convert 300 minutes to 3:20:00? HH:MM:SS

    1. Hi!
      Excel cannot perform incorrect calculations. 300 minutes is 5 hours.
      I recommend reading the first paragraph of the article above.

  16. How do I convert 44,694:30 hours to 44,694.5? I can't find formula here.

    44,694 hours and 30 minutes to convert in decimal.

      1. FYI, make sure the cell yur formula is in is formatted as a number no general. i've seen this cause issues.

  17. Is there a potential solution for correcting the formatting. When Pasting time values from different systems they will show the minuets as hours and others will be correct. This is causing issues with my data tables.

  18. I have a track result in a form of 2:04.66 (=2 minutes, 4 seconds, 660 milliseconds). What is the formula to convert the original value to seconds, i.e. 124,66?

    1. Hi!
      The answer to your question is in the first paragraph of the article above. Please read it carefully.
      Multiply the time by 86400.

  19. i have 400.5 min and to convert it into Hr Min Sec

  20. how do i convert this (07-09-2021 21:00 ) into this (21:00 - 22:00)

  21. Hi
    Similar to Robert says:
    February 6, 2021 at 11:37 am
    Can I please have 1 days 4 hours 21 mins converted to 28.21 in Excel

    I am importing travel time from google maps such as above so my cell contains let's say
    16 hours 20 mins
    Id like this to be 16.33 so I can charge it out multiplied by an hourly rate.
    Love your help with this. (I'm actually using MS Access) if that makes it easier or harder?
    Poida

      1. I still can't get it, I can't see it in the first paragraph when it is all in just one cell
        17 hours 59 mins
        Thank you

        1. Hi!
          I am assuming that there is a text written in your cell and not the time. To convert this text to time, use the formula

          =--SUBSTITUTE(TRIM(CONCAT(IF(ISNUMBER(--MID(D1,ROW($1:$94),1)),MID(D1,ROW($1:$94),1)," ")))," ",":")

          1. Thank you so much for your replies, I think you should go in my favourites.
            That worked a treat, I just had to add the x24 to the end to get 17.98333 hours.

            I'd still be at Uni trying to put that equation together, and I'm over 50.

            Cheers Poida

  22. Hi there,

    How can I convert 12:00:00 AM to 00:00:00 and 12:00:00 PM to 12:00:00 in the same formula including both AM/PM, if I need to convert time to AM or PM accordingly?

    I want to convert,
    12:39:46 AM in A2 to 00:39:46.
    12:04:14 PM in B2 to 12:04:14.

    2. you can have formatted it into 13:00:00 instead

  23. I would like to convert 400 minutes to hours and minutes.... but I'd like to time displayed as 6 40 (where the 40 minutes are display using superscript.)

  24. Hi I've been trying a few different formulas to convert from time to an hour decimal but they're all giving me the same odd results.

    eg. for A1*24

    7:35 is producing 559.58
    10:00 is producing 562

    I've made sure the format is decimal, and tried it in different ones but same issue. Any help would be awesome. thanks =)

  25. Hi, how can I convert time 06:15 to 6.25? Thanks

    1. Hi!
      Have you tried the ways described in this blog post?
      The answer to your question is in the first paragraph of this article.

      1. Yes, but it doesn't work.

        Well, the time that I need to convert is a result of calculated time. i.e. Start time is 9:00 (A1), end time is 18:30 (B1). I use the subtraction formula (=B1-A1) to get 9:30 (C1), and then I use the formula =C1*24 as stated in the first paragraph but it gives me 12:00 instead of 9.5. I am not sure what I did wrong in converting the 9:30 to 9.5. Please advise.

          1. Got it, it works now. Thank you so much :)

  26. When I type 05082021, I want it to show as 05/08/2021, and when I type 0957, I want it to show as 09:57
    Please help. I've tried everything I can think of.

  27. "1 day, 2 hours, 20 minutes and"

    can you convert this to decimal?

  28. I've used this formula:

    =IF(INT(AF3-AD3)>0, INT(AF3-AD3) & " days, ","") & IF(HOUR(AF3-AD3)>0, HOUR(AF3-AD3) & " hours, ","") & IF(MINUTE(AF3-AD3)>0, MINUTE(AF3-AD3) & " minutes and ","") & IF(SECOND(AF3-AD3)>0, SECOND(AF3-AD3) & " seconds","")

    and got this:

    2 hours, 20 minutes and

    How can I change it to decimal?

    you help will be greatly appreciated.

  29. All I need is a column to record date received and a column to record time received.
    I just want to type 250621 for the date, and see it as 25/06/2021.
    For the time I would like to type for example: 1315 and it show as 13:15.
    I've tried the formula tab and formatting cells, but getting nowhere.

  30. i have value 0.90 in a cell in excel i want to convert it as hours

  31. Hi am new to all this and I have a question how do I convert 20hrs to numbers

  32. I have a column for duration. It varies from 22S to 17M 13S. Is it possible to create a formula to read this in 00:00:00 format? I can do something like =timevalue("0".&Left(C1,1)&":""&MID(C1,4,2)) for a duration of 2m 46s but that formula then wouldn't work for the next cell if it is 17m 59s. Is there a way to have one formula convert varying durations to the 00:00:00 format?

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

      =SUBSTITUTE(IF(ISNUMBER((SEARCH("m",C1))), "0:"&SUBSTITUTE(SUBSTITUTE(C1,"m ",":"),"s",""), "0:0:"&SUBSTITUTE(SUBSTITUTE(C1,"m ",":"),"s","")),"m",":0")

      I hope my advice will help you solve your task.

      1. It worked. Thank you.

  33. 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. I.e. I have a wo that states standard time to complete 1 = 7 minutes so in my mind if I utilize military time as a decimal 0.12 * 150 = 18 which is incorrect If I state a whole number i.e. 7 * 150 = 1050. How do I get this to calculate expected time to complete to show as time and be correct?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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