How to Convert Time to Decimals in Excel

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

Knowing how to convert time to decimals in Excel could be such a time-saver.

You don’t have to manually compute your time value into hours, minutes, seconds. Excel can do that for you!

Here’s a little secret:

Converting time to decimals by changing the format from ‘Time’ to ‘Number’ doesn’t work. 😊

In this tutorial, we’ll show you how to create a specific time into decimals in Excel. As a bonus, we’ll also teach you how to do the opposite — convert number to hours and minutes.

Kasper Langmann, Co-founder of Spreadsheeto

Let’s get started!

Get your FREE exercise file

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!

Download the FREE Exercise File

Download exercise file

Converting Time to Number

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.

But before everything else, let’s peek inside Excel’s time system so you can understand better how the conversion works.

Kasper Langmann, Co-founder of Spreadsheeto

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.

in excel time system, 24 hours is counted as 1

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

The Arithmetic 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’.

6 hours is mistakenly converted to 0 hours

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

general option on number format dropdown

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

6 hours is correctly converted to 6

How about using a time value with minutes on it?

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

5 hours and 10 mins is converted to 5.17

Similarly, you can convert time to minutes and seconds using the same principle.

Kasper Langmann, Co-founder of Spreadsheeto

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

15 minutes is correctly converted to 15

How about converting a time value more complicated?

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

1 hour, 30 minutes, and 15 seconds is converted to 90.25

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

25 seconds is converted to 25

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

30 hours, 30 minutes, and 30 seconds is converted to 109,830

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

converting 40 hours, 54 minutes, and 10 seconds to hours, minutes, and seconds

The ‘INT’ Function

What if you would like to get rid of the decimal?

To do so, use the ‘INT’ function, which returns the integer part of the decimal number by rounding the value down:

using the int function to round the value down

The Convert Function Method

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.

The ‘CONVERT’ function, obviously, converts a number from one measurement system to another.

Kasper Langmann, Co-founder of Spreadsheeto

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:

converting 6 hours to 6 by the convert function

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

converting complex time to hours, minutes, and seconds using the convert function

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

using the int function to round down the results of converting time to number using the convert function

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

Convert Decimal to Time

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

dividing numerical value 20.56 by 24

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

time option from the number format dropdown

Here’s the result:

result of converting decimal to time

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

more number formats option on number format dropdown

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

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

selecting hhmmss time format

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

result of converting decimal to time in hhmmss time format

Easy, right?

Wrapping things up…

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

We included a sample worksheet you can use to try out what you learned from this tutorial. If you’re stuck, refer again to this article, apply the formula, and compare the result! 😊

Kasper Langmann, Co-founder of Spreadsheeto