Formula Errors

How to Fix Excel Time Formula Returning 9.05 Instead of 9.5

Aamir Naveed AkramAamir Naveed Akram Oct 10, 2026 870 views

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.

How to Fix an Excel Time Formula That Returns 9.05 Instead of 9.5
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.
Before you start

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.

Solution 1Recommended

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.

1
Select a target cell

Click on an empty cell where you want the final decimal result to appear.

2
Enter the multiplication formula

Type =A1*24 (replace A1 with the cell reference containing your 9:30 time data) and press Enter.

3
Format the result cell

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.

Multiply the Time Value by 24
Fixing Time Formatting: If your formula result displays as a random time (like 12:00 AM), changing the cell format to 'General' or 'Number' instantly resolves the issue.
Work Smarter with WPS Spreadsheet

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. 1. Open your file in WPS: Launch WPS Spreadsheet and open your time-tracking or payroll document.
  2. 2. Apply the time formula: Select an empty cell and enter the formula =A1*24 to convert the standard time.
  3. 3. Change cell format: Click the 'Number Format' dropdown menu in the Home tab and select 'General' to view the decimal.
100% compatible with Microsoft Excel (.xlsx) formats and formulasSupports all standard date and time functions effortlesslyLightweight software with an intuitive, familiar user interface
microsoft office alternative - wps office

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.