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 💪