How to Convert Time to Decimal in Excel

Microsoft Excel

Convert time to decimal in Excel – by using arithmetic calculations or CONVERT function or Excel Time functions (HOUR, MINUTE, and SECOND).

When working with Excel, sometimes we might need to convert time to decimal numbers (such as hours or minutes, or seconds). This is just to make it easy to use the time in other calculations.

There are three ways to convert time to decimal in Excel – by using arithmetic calculations or CONVERT function or Excel time functions, such as HOUR, MINUTE, and SECOND. This tutorial demonstrates all three methods to convert time to decimal in Excel.

Converting Time to Decimal Numbers in Excel

For example, instead of having the time as 5:30 PM, you may want the get:

  • Number of hours as 5
  • Number of minutes as 30

Thankfully Excel has three different ways to change a time value to a decimal number: arithmetic operation, CONVERT function, or a combination of three different Time functions.

Convert Time to Decimal Number Using Arithmetic Operation

This section demonstrates how to convert time to hours, minutes, and seconds using arithmetic calculations in Excel.

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

To do that, first, you have to remember how many hours, minutes, and seconds there are in a day period:

  • 24 hours in 1 day
  • 60 minutes in 1 hour
  • 60 * 24 (hours) = 1,440 minutes in 1 day
  • 60 seconds in 1 minute
  • 60 * 1,440 (minutes) = 86,400 seconds in 1 day

When you enter ‘12:00’ in Excel, Excel automatically detects this entry as ‘h:mm’. And if you change the format of that value to ‘Number’, you’ll get ‘0.50’.

This is because in Excel ‘24 hrs is equal to 1’. That is why ‘12:00’, when converted to ‘Number’ turns to 0.50 (12/24).

Convert Time to Hours in Excel

To convert time to a number of hours, simply multiply the time value by 24, i.e. with the number of hours in a day.

Suppose you have the time in cell A2 as 12:00 PM and you want to convert it into hours, use this formula:

=A2*24

Where A2 is where the time value is.

Don’t be alarmed if you got ‘12:00 AM’ at first. After you multiply a value in ‘Time’ format, Excel will automatically display the result in the same format, ‘Time’.

To fix this, apply the ‘General’ or ‘Number’ format to the result. Go to the ‘Home’ tab, click on the Number Format drop-down list and select ‘General’ or ‘Number’. The ‘General’ format displays the number as it is while the ‘Number’ format adds two decimal places to the value.

Let us explain how this works, Excel stores dates and times as numbers. As mentioned before 1 day (24 hours) is stored as 1 in Excel. So each hour is store as 1/24.

So when you enter the value 12:00 PM, in the backend, it is stored as the value ‘0.50’ (12/24). So, when you multiply the time by 24, it will give you the number of hours that have passed out of the 24 hours). 

Also if you have a value such as 2:30 PM, you will get the result as 14.30 (where the minutes/seconds are shown in decimals and full/complete hours as integers). In this case, the numeric value of 30 minutes in Excel would be 0.50 hours.

In case you convert 12.30 PM to decimals, you’ll get 12.5, but if you only want the full hour value and ignore the minutes part, use the below formula using INT function:

=INT(A2*24)

Convert Time to Minutes in Excel

To convert time to minutes, you can simply multiply the time value by 1440, which is the number of minutes in a day (24 * 60).

Suppose you have the time in A2 as 4:45 AM and you want to convert it into minutes, use this formula:

=A2*1440

Or if you can’t remember number of minutes in a day, multiply the time by 24*60:

=A2*24*60

In the above example, ‘285’ is the total number of minutes that have elapsed on that day in the given time.

Convert Time to Seconds in Excel

To convert time to seconds, you can simply multiply the time value by 86,400, which is the number of seconds in a day (24 * 60 * 60).

If you have the time in A2 as 05:50:10 AM and you want to convert it into seconds, use this formula:

=A2*86400 

or

=A2*24*60*60

In the above example ‘21010’ is the total number of seconds that have elapsed on that day in the given time.

Convert Time to Decimal Number Using the CONVERT Function

Another method to perform the time to decimal conversion is to use the CONVERT function. The CONVERT function converts a number from one measurement system to another.

The syntax of CONVERT function is

=CONVERT(number,from_unit,to_unit)

Arguments:

  • number – numeric value to convert
  • from_unit – the beginning unit
  • to_unit – the ending unit

Here you’re converting time to decimal numbers, there are only 4 units you need to remember:

  • “day” – days
  • “hr” – hours
  • “mn” – minutes
  • “sec” – seconds

This function takes a numerical value ( time in this case) and converts it into hours or minutes or seconds.

Convert Time to Hours in Excel

Suppose you a time value in cell B2, below is the formula that will convert time to hours:

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

In the above formula, seconds arguments (“day”) the tells function that the value in cell B2 is in day format and the third argument (“hr”) tells it to convert it into hours.

If you’d like to get only the hour value and ignore the minute’s parts, use the below INT formula:

=INT(CONVERT(B2,"day","hr"))

Convert Time to Minutes in Excel

To convert time to minutes using the CONVERT function, supply “day” as the unit to convert from and “mn” as the unit to convert to:

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

Convert Time to Seconds in Excel

The formula is essentially the same as in the previous two examples with the only difference is that you convert the “day” unit to the “sec” unit:

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

Convert Time to Decimal Number Using Excel Time Functions

Another easy way to convert time to decimal numbers is by using the inbuilt time-related Excel functions (HOUR, MINUTE, and SECOND). This method uses a bit more complex formula than the other two methods, however, its logic is quite obvious.

Functions:

  • HOUR(serial_number)
  • MINUTE(serial_number)
  • SECOND(serial_number)

The HOUR, MINUTE, and SECOND functions simply return the number of hours, the number of minutes, and the number of seconds that have elapsed in the given time, respectively.

Convert Time to Hours Using Time Functions

Since the aim here to get all the parts in hours (hours, minutes, and seconds), you need to combine all three functions into one formula.

To convert time to hours, extract the individual time units by using HOUR, MINUTE, and SECOND functions, then divide the minute value by 60 (the number of minutes in an hour) and the second value by 3600 (the number of seconds in an hour (60*60)), and add up the results:

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

Convert Time to Minutes Using Time Functions

To convert time to minutes, simply multiply hours by 60 and divide seconds by the same number:

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

Convert Time to Seconds Using Time Functions

To get all the parts (hours, minutes, and seconds) in seconds, multiply hours by 3600 (60*60) and multiply minutes by 60 and add up the results:

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

That’s it.