Background
When handling work hours or durations in Excel, there are often times when you want to convert “Time” into a decimal number. For example, expressing “8 hours and 30 minutes” as “8.5” makes calculations much easier.
Currently, my Excel sheet is in the following state:
- Cell A1: 8:30 (Format: h:mm)
- Cell B1: 17:00 (Format: h:mm)
- Cell C1:
=B1-A1→ Result: 8:30 (Format: [h]:mm)
I wanted to convert this “8:30” displayed in C1 into the numeric format “8.5”, so I tested the method below.
Desired Specification
- Convert time to a decimal number, like 8 hours 30 minutes → 8.5.
- Keep using
B1 - A1as the basis for calculation. - Allow arbitrary settings for decimal places (e.g., up to 1 decimal place).
Steps
1. Modify the Formula
First, enter the following formula in cell C1:
=(B1 - A1) * 24
Why multiply by 24? In Excel, time is internally processed as “1 day = 1”. Therefore, 1 hour equals 1/24, and 30 minutes equals 0.5/24. By multiplying the time serial number by 24, you can convert the unit from “days” to “hours” in decimal format.
2. Set Format to “Number”
Next, with cell C1 selected, perform the following operations:
- Go to the “Home” tab in Excel.
- Open the formatting dropdown in the “Number” group.
- Select “Number” instead of “General” or “Time”.
- Adjust the decimal places (e.g., set to 1 decimal place to see “8.5”).
Now, the display in cell C1 will change from “8:30” to “8.5”.
Application Examples
- “7 hours 45 minutes” → 7.75
- “9 hours 15 minutes” → 9.25
- “6 hours” → 6.00
Being able to convert like this is extremely useful for calculating average total hours or converting time into hourly wages.
Summary
- In Excel, time can be numericalized using
=(End Time - Start Time) * 24. - By changing the cell format to “Number”, you can display it with decimal points.
- This is useful for work time management, payroll calculations, and graphing.
This is a highly practical technique for anyone handling time data in Excel. Thank you for reading. I hope this helps.
