How to Convert Excel Time from HH:MM to Decimal Hours
Question details
The user needs to convert a time formatted as HH:MM into a standard decimal hour number (for example, converting 1:54 to 1.9) for calculations and payroll.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating working hours, determining payroll, or summing time durations where time data must be expressed as whole and decimal numbers rather than clock time.
- Observed behavior
- Excel inherently stores time as a fraction of a 24-hour day, meaning direct mathematical calculations with time values may return incorrect results unless converted to decimal formats.
Ensure the cells containing your original time data are correctly formatted as 'Time' (HH:MM) and identify an empty adjacent column to display the converted decimal results.
Multiply the Time Value by 24
The most straightforward and widely used method to convert Excel time into decimal hours by multiplying the default time fraction by 24.
Because Excel stores time as a fraction of a 24-hour day (where 1 full day equals 1.0), multiplying a specific time value by 24 successfully converts it into total decimal hours.
Click on an empty cell where you want the decimal hour result to appear.
Type the formula =A1*24 (assuming your HH:MM time is located in cell A1).
Press Enter to calculate the result.
Right-click the result cell, select 'Format Cells', and change the category to 'General' or 'Number' to display the decimal correctly.
Use the CONVERT Function
Utilize Excel's built-in conversion function to calculate decimal hours without relying on manual math operators.
Easily Convert Time and Manage Data in WPS Spreadsheet
You can effortlessly convert time formats, perform complex payroll calculations, and manage vast spreadsheets using WPS Spreadsheet. It supports all standard Excel formulas, ensuring seamless workflow and complete data accuracy.
- 1. Open your workbook: Launch WPS Spreadsheet and open the document containing your time data.
- 2. Select an empty cell: Click an adjacent blank cell where the decimal conversion will take place.
- 3. Enter the formula: Type =A1*24 in the formula bar and press Enter to execute the calculation.
- 4. Adjust number formatting: Go to the Home tab, locate the Number Format drop-down menu, and select 'Number' or 'General'.
Frequently Asked Questions
Why does my converted decimal time show up as 12:00 AM?
This happens because Excel automatically applies the 'Time' format to the cell containing your formula. To fix this, select the cell, right-click to choose 'Format Cells', and change the format category from 'Time' to 'General' or 'Number'.
Can I convert decimal hours back to HH:MM format?
Yes, you can reverse the process by dividing the decimal hours by 24 (for example, =A1/24) and then formatting the resulting cell as 'Time' (HH:MM).
How do I convert standard minutes to decimal hours?
If you only have minutes stored as a regular integer (for example, 45 minutes), divide the value by 60 (e.g., =45/60) to get 0.75 hours. Make sure the cell is formatted as a Number.
