Convert Time Into Decimals in Excel (Hours/Minutes/Seconds)
Do you know how to convert time into decimals in Excel? 🤔
By knowing how to convert time into decimals in Excel, you don’t have to manually compute your time value into hours, minutes, and seconds. Let Excel do that for you!
In this tutorial, we’ll show you how to convert time into decimals in Excel so you can make use of it for your timesheets and a whole lot more ⌚
To start, download this free practice workbook we’ve prepared for you to work on for this tutorial.
Table of Contents
Convert time to decimal
Before you can convert time into hours or minutes or seconds, you need to first convert time into decimal numbers.
Let’s go with the basics of the Excel time system so you can better understand how time conversion works.
When you write “6:00” in Excel, it automatically detects the data as “h:mm” or hours minutes format. When you change its format to “Number”, you’ll get “0.25” instead.
Now, why did it change to 0.25? 🤔
This is because, in the Excel time system, 24 hours is equal to 1. Any time value you enter into the cell is divided by 24 when changed into Number format. The formula below shows why it displayed 0.25.
6 / 24 = 0.25
In Excel, there are two (2) ways to convert time to decimal values:
- The Arithmetic Method
- The CONVERT Function Method
Let’s discuss them one by one 😀
The easiest way to convert time to decimal in Excel is using the Arithmetic Method. All you need to do is to multiply the original time value by the number of hours, minutes, or seconds 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 by 86400, which is the number of seconds in a day (24*60*60 ).
Convert Function Method
If you don’t know how many hours, minutes, or seconds there are in a day, the CONVERT function is a good (if not the better) alternative for you.
From its name, the CONVERT function converts a number from one measurement system to another. Whether it’s weight or mass, distances, time, and more.
Don’t worry, we’ll only cover conversion in the time measurement system for this tutorial 😊
The syntax of the CONVERT function is:
=CONVERT (number, from_unit, to_unit)
With the following arguments:
- 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:
To convert time to decimals using the CONVERT function, simply supply the appropriate units to the formula.
It’s time to put these methods into action. Open your practice workbook and you’ll see different time values in the time column. Let’s convert time to decimal hours, minutes, and seconds 💪
Convert time to hours
Let’s convert time to decimal hours using the 2 methods mentioned above.
First, let’s use the arithmetic method.
All we need to do is simply multiply the time value by 24.
- Double-click the cell and then type the equal sign to begin the formula.
- Click the cell reference of the time value and multiply it by 24. Use the following formula:
- Press Enter.
Don’t worry if you get 0:00 as the result 😰
Every time you multiply a value in Time format, Excel may automatically display the result in the same format as well.
To display the number of hours, go to the Number group in the Home Tab. Select General or Number from the drop-down.
You’ll immediately see that the time is now converted into decimal hours 😊
Let’s try to convert time into decimal hours using the CONVERT function.
- Double-click the cell.
- Type the CONVERT function.
- The first argument in the formula is the number. Click the cell reference where your time value is. In our case, it’s cell B3.
- The next argument is the from_unit which is the beginning unit. The beginning unit is “day”.
- The last argument is the to_unit which is the ending unit. The ending unit is hour, so type “hr”. Then close the formula with a right parenthesis.
- Press Enter.
Whether you use the Arithmetic method or the CONVERT function, you will get the same result 👍
Fill in the rest of the rows by double-clicking or dragging down the fill handle.
You have successfully converted time to decimal hours!
But you’ll observe that the results have a lot of decimal values 😟
To get rid of the other decimal places and get the nearest whole number of hours, use the INT function. The INT function returns the integer part of the decimal number by rounding the value down.
All you have to do is place it before the formula like this 😊
For arithmetic method:
For the CONVERT function method:
Fill in the rest of the rows.
Now you have the number of hours in whole numbers 👍
Once you get this, converting time to minutes and seconds in Excel will go smoothly. It follows the same steps. Let’s get to it right away!
Convert time to minutes
The same two (2) methods can be used to convert time into a number of minutes.
For the arithmetic method, multiply the time you want to convert by 1440. Like this:
If you see the result 0:00. Just change its format to Number or General just like what we did when we converted time to decimal hours earlier.
If you want to return the number of complete minutes, utilize the INT function like in the previous example:
Using the CONVERT function to convert time to a number of minutes will yield the same result.
Use this formula:
Since we want to convert time to a number of minutes, use “mn” for the to_unit in the formula.
To return to a complete number of minutes, use the INT function.
Fill in the rest of the rows. And there you have it! Calculate time in minutes takes only a few minutes with Excel ⚡
Convert time to seconds
Converting time to a number of seconds in Excel can be done similarly.
If you want to use the Arithmetic method, multiply the time value by 86400.
Or you can use the CONVERT function to convert time to a number of seconds.
Here, you don’t need to use the INT function anymore since you need to calculate time into a complete number of seconds.
Fill in the rest of the rows.
Now, this is how the spreadsheet should look like 👇
Awesome, right? 😀
That’s it – Now what?
Hooray! Now you have successfully learned how to convert time into decimals in Excel. You can actually save time when calculating time in Excel. Whether in hours, minutes, or seconds, you can convert time in no time 😉
With its awesome features and functions, Excel helps you skip manual work and get the job done faster and easier. This only means that you shouldn’t stop learning about Excel here.
Learn top Excel functions you wish you knew sooner to get work done faster and easier. Logical functions like IF and SUMIF, and the most useful (and popular) Excel function: VLOOKUP 🚀
Sign up for my free online Excel course 📧 to turbocharge your skills in Excel! You’re one click close to becoming an Excel Expert 😎
Wondering where else you can use what you’ve learned about converting time in Excel? We know. it’s Timesheets! Learn how to create timesheets in Excel (plus FREE templates!) here.
Dive deeper into time functions and date functions in Excel too! We have a complete guide for you 😊