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.
Let’s get started!
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.
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
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’.
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:
Similarly, you can convert time to minutes and seconds using the same principle.
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:
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:
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.
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.
Convert Decimal to Time
Converting number to time is easier than the one above.
Simply speaking, you only have to follow two steps:
- Divide the decimal by 24
- 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?
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! 😊