How to Fix Excel Time Formula Returning 9.05 Instead of 9.5
Question details
The user needs to correctly convert a standard time value (such as 9:30) into decimal hours (9.5) instead of receiving an incorrect mathematical result like 9.05.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Converting elapsed time or working hours into standard decimal format for payroll, billing, or reporting purposes.
- Observed behavior
- When attempting to convert time to decimals using hour and minute extraction or incorrect formatting, 9 hours and 30 minutes outputs as 9.05 instead of the correct decimal 9.5.
Verify that your original source cells contain valid time data (e.g., 9:30) by ensuring that the formula bar displays a recognizable time format rather than standard text.
Multiply the Time Value by 24
The simplest and most reliable method to convert a time value into decimal hours by leveraging how Excel natively stores time.
Excel stores time as a fraction of a full 24-hour day. For example, 12 hours is stored internally as 0.5. By multiplying the time cell by 24, you accurately convert this fraction into standard decimal hours.
Click on an empty cell where you want the final decimal result to appear.
Type =A1*24 (replace A1 with the cell reference containing your 9:30 time data) and press Enter.
Right-click the result cell, choose 'Format Cells', and select 'General' or 'Number' under the Number tab to display the correct 9.5 decimal value.

Use the HOUR and MINUTE Functions Properly
An alternative mathematical method when you specifically need to extract the hours and minutes separately to form a decimal.
Easily Calculate Time and Payroll with WPS Spreadsheet
WPS Office Spreadsheet provides full compatibility with all standard Excel formulas, making time conversion, payroll calculations, and data formatting incredibly easy and fast.
- 1. Open your file in WPS: Launch WPS Spreadsheet and open your time-tracking or payroll document.
- 2. Apply the time formula: Select an empty cell and enter the formula =A1*24 to convert the standard time.
- 3. Change cell format: Click the 'Number Format' dropdown menu in the Home tab and select 'General' to view the decimal.

Frequently Asked Questions
Why does multiplying an Excel time by 24 return a time format like 12:00 AM?
When referencing a cell formatted as 'Time', the spreadsheet automatically applies the 'Time' format to your new formula result. You can fix this by highlighting the cell, right-clicking, selecting 'Format Cells', and changing the format directly to 'General' or 'Number'.
How do I convert decimal hours back to a standard time format?
To reverse the conversion process, divide your decimal number by 24 (for instance, enter =A1/24). Then, change the formatting of that result cell back to 'Time' so it displays the correct hours and minutes.
Why did my previous MINUTE formula result in 9.05?
This common mistake happens if the minutes were treated as a standard decimal fraction out of 100 rather than out of 60. For example, 30 minutes is mathematically half an hour (0.5), not 0.30. Always divide your extracted minutes by 60 to find the accurate fraction of an hour.




