You can use the following formulas to convert a time into a decimal value in Excel:
1. Convert Time to Number of Hours: Multiply original time by 24 (the number of hours in a day)
2. Convert Time to Number of Minutes: Multiply original time by 1440 (the number of minutes in a day)
3. Convert Time to Number of Seconds: Multiply original time by 86400 (the number of seconds in a day)
The following example shows how to use each of these formulas in practice.
Example: Convert Time into Decimals in Excel
Suppose we have the following list of times in Excel:
We can use the following formulas to convert each time into a decimal that represents the total hours, minutes, and seconds:
Note: Make sure that the Hours, Minutes, and Seconds columns are all formatted as either General or Number in order for the decimals to show up correctly.
Here’s how to interpret the output:
- The time 2:30 is equal to 2.5 total hours.
- The time 2:30 is equal to 150 total minutes.
- The time 2:30 is equal to 9,000 total seconds.
And so on.
If you’d only like the integer values then you can wrap each formula in the INT() function:
Here’s how to interpret the output:
- The time 10:15:40 contains 10 whole hours.
- The time 10:15:40 contains 615 whole minutes.
- The time 10:15:40 contains 36,940 whole seconds.
And so on.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Calculate the Difference Between Two Dates in Excel
How to Calculate a Cumulative Sum by Date in Excel
How to Group Data by Month in Excel