How to Convert Time to Decimals in Excel

Before you start:

Throughout this guide, you need a data set to practice.

I’ve included one for you (for free).

Download it right below!

One of the most common uses of converting time to number is when dealing with timesheets.

But whether you’re a business owner making timesheets for your employees or not, you should learn how to convert time to number.

If you write “6:00” in Excel, which Excel automatically detects as ‘h:mm’, and change the format to ‘Number’, you’ll get ‘0.25’ instead.

Here’s the reason why:

In Excel, 24 hours is equal to 1.

That’s why ‘6:00’, when converted to ‘Number’ becomes 0.25.

6 / 24 = 0.25

Now that you get it, we’ll show you the two (2) methods of converting time to numbers:

• The Arithmetic Method
• The ‘CONVERT’ Function Method

Because Excel treats a 24-hour day period as 1, all we have to do to convert time to an hour is multiply the time by 24, the number of hours in a day.

So to convert ‘6:00’ (‘F4’) to time, multiply it by 24.

Don’t be surprised if you got ‘0:00’ at first.

After you multiply a value in ‘Time’ format, Excel may automatically display the result in the same format, ‘Time’.

To remedy this, apply the ‘General’ or ‘Number’ format on the result. 

The ‘General’ format takes the number as it is while the ‘Number’ format adds two decimal places to make the value more precise.

On the ‘Home’ tab, click the number format dropdown and select ‘General’.

After doing that, you’ll then see the result, ‘6’.

How about using a time value with minutes on it?

Let’s convert ‘5:10’ to hours:

First, you have to remember how many minutes and seconds there are in a 24-hour day period:

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

In a 24-hour day period, there are 1,440 minutes and 86,400 seconds.

These are the values that you’ll be using in the formula. 

If you don’t like to memorize the values, you have to at least understand the equations to get them:

• Minutes = 60 * 24
• Seconds = 60 * 24 * 60

For now, let’s try and convert ‘0:15’ into minutes by using the formula:

time * 1440

How about converting a time value more complicated?

Let’s convert ‘1:30:15’ (1 hour, 30 minutes, and 15 seconds) into minutes:

Let’s now proceed into converting time to seconds.

This time, let’s assume you don’t know the exact number of seconds there are in a day, so we’ll be using ‘60 * 24 * 60’ in the formula.

Let’s convert ‘0:00:25’ into seconds with the formula:

time * 60 * 24 * 60

This time, let’s try ‘30:30:30’ with ‘86,400’:

Here’s a summary of how to convert time into hours, minutes, and seconds using arithmetic operations:

If you intuitively know how many hours, minutes, and seconds there are on a day, the arithmetic is the best one for you. If not, the ‘CONVERT’ function is a good alternative.

It’s syntax is…

=CONVERT (number, from_unit, to_unit)

Here’s what the parameters mean:

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

Because we’re dealing with converting time to numbers, there are only 4 units we need to remember:

• “day”

• “hr”

• “mn”

• “sec”

To convert time to decimals using the ‘CONVERT’ function, simply supply the appropriate units to the formula.

Let’s try this out by converting ‘6:00’ to hours:

Now, let’s apply them all to the same time value as we did earlier:

If you like to round them down, use the ‘INT’ function:

Simple, right? Now, let’s try an easy way to reverse the process.

Converting number to time is easier than the one above.

Simply speaking, you only have to follow two steps:

1. Divide the decimal by 24
2. Format the cell to ‘Time’

Let’s convert ‘20.56’ to time.

First, divide the value by ‘24’.

Apply the time format by clicking ‘Time’ from the number format dropdown.

Here’s the result:

Now, if you to convert the number to time: hh mm ss, click the ‘More Number Formats’ on the number format dropdown.

Then, select ‘Time’ from the ‘Category’.

Choose the ‘Type’ with the hh mm ss and hit ‘OK’.

Immediately, you’ll see the change on the format.

Easy, right?

What you learn above is especially important if your work or tasks deal with time.