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.

Because Microsoft Excel uses a numeric system to store times, you can easily turn hours, minutes and seconds into numbers that you can use in other calculations.

In general, there are two ways to convert time to decimal in Excel - by changing the cell format and by using arithmetic calculations or Excel time functions, such as HOUR, MINUTE and SECOND. Further on in this tutorial, you will find the detailed explanation of the first way and formula examples demonstrating the other technique.

How to convert time to decimal number in Excel

Overall, there are three ways to change a time value to a decimal number: arithmetic operation, CONVERT function or a combination of three different Time functions.

The easiest way to convert time to decimal in Excel is to multiply the original time value by the number of hours, seconds or minutes in a day:

  • To convert time to a number of hours, multiply the time by 24, which is the number of hours in a day.
  • To convert time to minutes, multiply the time by 1440, which is the number of minutes in a day (24*60).
  • To convert time to seconds, multiply the time time by 86400, which is the number of seconds in a day (24*60*60 ).

Convert time to a decimal number in Excel

In the following sections, you will learn the other methods of converting times to a decimal number in Excel.

How to convert time to hours in Excel

This section demonstrates 3 different formulas to convert hours from the standard time format (hh:mm:ss) to a decimal number.

Formula 1: Arithmetic calculation

You already know the fastest way to convert a time value to a number of hours in Excel - multiplying by 24, i.e. by the number of hours in one day:

=A2*24

Where A2 is the time value.

To get the number of compete hours, embed the above formula in the INT function that will get rid of the fractional part:

=INT(A2*24)
The formula and arithmetic calculation to convert time to hours in Excel

Formula 2: CONVERT function

Another way to perform the "time > hours" conversion is to use the following Convert formula:

=CONVERT(A2, "day", "hr")

Formula 3: HOUR, MINUTE and SECOND functions

Finally, you can use a bit more complex formula, whose logic, however, is quite obvious. Extract the individual time units by using the HOUR, MINUTE and SECOND functions, then divide minutes by 60 (the number of minutes in an hour) and seconds by 3600 (the number of seconds in an hour), and add up the results:

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

How to convert time to minutes in Excel

The same three methods can be used to convert minutes from the standard time format to a decimal number.

Formula 1: Arithmetic calculation

To convert time to total minutes, you multiply time by 1440, which is the number of minutes in one day (24 hours * 60 minutes = 1440):

=A2*1440

If you want to return the number of compete minutes, utilize the INT function like in the previous example:

=INT(A2*1440)

You can view the results in the screenshot below:
Converting time to minutes in Excel

Formula 2: CONVERT function

To do the "time > minutes" conversion with the CONVERT(number, from_unit, to_unit) function, supply "day" as the unit to convert from and "mn" as the unit to convert to:

=CONVERT(A2, "day", "mn")

Formula 3: HOUR, MINUTE and SECOND functions

One more way to get the number of minutes is to multiply hours by 60 and divide seconds by the same number:

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

How to convert time to seconds in Excel

Converting time to total seconds in Excel can be done in a similar fashion.

Formula 1: Arithmetic calculation

Multiply the time value by 86400, which is the number of seconds in a day (24 hours * 60 minutes * 60 seconds = 86400):

=A2*86400
Converting time to seconds in Excel

Formula 2: CONVERT function

The formula is basically the same as in the above examples with the only difference that you convert the "day" unit to "sec":

=CONVERT(A2, "day", "sec")

Formula 3: HOUR, MINUTE and SECOND functions

Use the HOUR, MINUTE and SECOND functions like in the two previous examples (I believe at this point you don't need any further explanation of the formula's logic :)

=HOUR(A2)*3600 + MINUTE(A2)*60 + SECOND(A2)

Tips:

  1. If any of the above formulas returns a value formatted as time, simply change the cell's format to Generalto display it as a number.
  2. To convert time to a decimal number that represents the time in the internal Excel system, apply the General format to the cell. With this approach, 23:59:59 will be converted to 0.99999, 06:00 AM to 0.25, and 12:00 PM to 0.5. If an integer part of the converted number is greater than zero, it means that your cell contains both date and time values.

How to split date and time in Excel

As is often the case, your Excel worksheet may contain dates and times in one cell, while you want to split them into two separate cells.

Remembering that in the internal Excel system the date value is stored as a whole part and the time value as a fractional part of a decimal number, you can extract the date using the INT function, which rounds the cell value down to the nearest integer.

Supposing your original dates and times are in column A, the following formula will accomplish the separation:

=INT(A2)
Use the INT function to extract the date values

To extract the time portion, subtract the date returned by the above formula from the original date and time value:

=A2-B2

Where column A contains the original date & time values and column B contains the dates returned by the INT function.

If you'd rather not have time values linked to the separated dates (for example, you may want to remove the date column in the future), you can use the following MOD formula that refers to the original data only:

=MOD(A2,1)
Splitting date and time in Excel

Tip. If the separated date and time values are not displayed properly, change the format of the new columns to Date and Time, respectively.

This is how you split date and time in Excel. If you want to further separate hours, minutes and seconds into individual columns, then use the HOUR, MINUTE and SECOND functions, as demonstrated in How to get hours, minutes and seconds from a timestamp.

How to spell time in Excel

Sometimes, you may need to convert time into the format that reads something like "# days, # hours, # minutes and # seconds". A good thing is that you already know all the ingredients of the formula:

  • Extract days using the INT function;
  • Extract time units with HOUR, MINUTE and SECOND functions, and
  • Concatenate all parts in a single formula.

Having difficulties with figuring out a proper formula for your worksheet? The following example will make things easy!

Supposing you have the dates of upcoming events in column B beginning in cell B4, and the current date and time returned by the NOW() function in cell B1.

The formula to calculate the time difference is as simple as =B4-$B$1. Of course, nothing prevents you from subtracting the current date and time directly with =B4-NOW().

And now, let's make a countdown timer that would show how many days, hours, minutes and seconds are left until each event.
Setting up a countdown timer in Excel

The formula to enter in cell D4 is as follows:

=INT(C4) & " days, " & HOUR(C4) & " hours, " & MINUTE(C4) & " minutes and " & SECOND(C4) & " seconds"
The formula to spell time in Excel

If you wish to get rid of 0 values, like in cells D6 and D7 in the screenshot above, then include the following IF statements:

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

All zeros are gone!
The formula to spell time in Excel excluding zero values

Note. When either of the above formulas refers to a negative number, the #NUM! error will appear. This may happen when you subtract a bigger time from a smaller one.

An alternative way to write time in words in Excel is to apply the following custom time format to the cell: d "day," h "hours," m "minutes and" s "seconds". No formulas and no calculations are required! For more information, please see Creating a custom time format in Excel.

Convert text to time in Excel

If your time formulas and calculations do not work right, time values formatted as text is often the cause. The fastest way to convert text to time in Excel is using the TIMEVALUE function.

The Excel TIMEVALUE function has just one argument:

TIMEVALUE(time_text)

Time_text is a text string in any of the time formats that Excel can recognize. For example:

=TIMEVALUE("6:20 PM")

=TIMEVALUE("6-Jan-2015 6:20 PM")

=TIMEVALUE(A2) where A2 contains a text string
Convert text to time using the Excel TIMEVALUE function

As you see, the formulas with cell references and corresponding text strings deliver identical results. Also, please notice the left alignment of time strings (text values) in cells A2 and A6 and right-aligned converted time values in column D.

Convert time to text in Excel

Supposing you have an Excel file full of times formatted to look like "8:30:00 AM" and you want to convert them to the text format. Simply changing the cell's format to TEXT won't work because this would change your time values to underlying numeric representation of the time. For example, 8:30:00 AM will be converted to decimal 0.354166666666667.

So, how do you convert cells to the text format so that your cells still have the time in them? The answer is to use the TEXT function that converts a numeric value to text with the display formatting that you specify, for example:

=TEXT($A2,"h:mm:ss")

The screenshot below demonstrates other possible formats:
Converting time to text in Excel

How to convert numbers to time format in Excel

If you have a list of numbers such as 1, 2, 3.5 and you want to convert them to a time format, for example 1:00:00, 2:00:00 or 3:30 AM, perform the following steps.

  1. Divide the numbers by 24 (there are 24 hours in a day). The formula can be as simple as =A2/24.
  2. Select the cell(s) with the formula result, right-click and select Format Cells from the context menu or press Ctrl+1. Either way, the Format Cells dialog will appear, you select Time on the left pane under Category, and choose the format you want on the right pane under Type. Please see How to format time in Excel for more details.

Converting numbers to the time format in Excel

If your time exceeds 24 hours, 60 minutes, 60 seconds, use these guidelines to correctly show it.

That's all for today. If someone wants to get the first-hand experience with the formulas discussed in this article, you are most welcome to download the sample workbook below.

If you want to learn a few more useful formulas to calculate times in Excel, please check out the related tutorials at the end of this page. I thank you for reading and hope to see you again next week!

Practice workbook for download

Converting time in Excel - examples (.xlsx file)

459 comments

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

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

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

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

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

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

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

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

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

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

    • 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

  10. Hello guys,

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

    Thank you.

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

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

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

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

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

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

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

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

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

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

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

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