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

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

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

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

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

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

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

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`

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

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:

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")`

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`

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

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

`=A2*86400`

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")`

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

- If any of the above formulas returns a value formatted as time, simply change the cell's format to
**General**to display it as a number. - 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.

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

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

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.

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.

The formula to enter in cell D4 is as follows:

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

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!

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.

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

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.

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:

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.

- Divide the numbers by 24 (there are 24 hours in a day). The formula can be as simple as =A2/24.
- 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 apply or change the time format in Excel for more details.

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 for converting time in Excel.

If you want to learn a few more helpful formulas to add and subtract times, calculate elapsed time and time difference, please check out other parts of our Excel Times tutorial (the links are available at the end of this page). I thank you for reading and hope to see you again next week.

Excel formulas
Excel functions
Vlookup in Excel
Merge data in Excel
SumIf
Excel CountIf
Excel Compare
Excel If statement
Excel Charts
Pivot Table
Updates
Excel conditional formatting
Excel formatting
Excel time
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 142 Responses to "How to convert time to decimal number, hours, minutes or seconds in Excel"

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

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

25mins,1 hr 8 mins convert to time

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.

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?

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?

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?

this is exactly what i'm searching for help on too. would love to see if anyone knows of a way to do this.

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

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.