How to Calculate 24-Hour Time to the Nearest Quarter Hour in Excel
Question details
The user needs to convert the difference between a start and stop time into decimal hours and round the elapsed time to the nearest quarter hour.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking work shifts, calculating timesheets, or measuring elapsed time using standard 24-hour time entries.
- Observed behavior
- The goal is to accurately subtract valid time values, convert the resulting fraction of a day into decimal hours (e.g., 9.75), and reliably round that decimal to 0.25 increments.
Ensure your start and stop times are entered with a colon (e.g., 07:30 and 17:15) so the spreadsheet recognizes them as valid time formats rather than standard numeric values like 0730.
Use MROUND to Calculate and Round Decimal Hours
Subtract the start time from the stop time, multiply the difference by 24 to convert it into decimal hours, and wrap the formula in the MROUND function to snap it to the nearest quarter hour.
Spreadsheet software stores one full day as the number 1. Therefore, hours are stored as fractions of a day. To convert a time duration into standard decimal hours, you must multiply the calculated difference by 24.
By applying the MROUND function with a multiple of 0.25, the decimal hours will automatically round to the nearest quarter hour (e.g., 9.86 rounds to 9.75).
Enter your start time in cell A1 (for example, 07:30) and your stop time in cell B1 (for example, 17:15).
Select cell C1 and type the formula =(B1-A1)*24. This calculates the time difference and multiplies it by 24 to output the decimal hours.
Modify the formula in cell C1 to =MROUND((B1-A1)*24, 0.25) and press Enter. This tells the software to round the exact decimal hours to the nearest 0.25 increment.
If the result displays as a time (like 9:45) instead of a decimal number, right-click cell C1, select Format Cells, and change the format category to Number or General.
Easily Calculate and Manage Timesheets in WPS Spreadsheet
WPS Spreadsheet provides seamless support for all standard time calculation formulas, making it incredibly simple to track work hours, convert times to decimals, and organize your payroll data without compatibility issues.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and enter your start and end times in proper HH:MM format in two separate cells.
- 2. Select the destination cell: Click the cell where you want the calculated decimal hours to appear.
- 3. Enter the rounding formula: Type the formula =MROUND((End_Cell - Start_Cell)*24, 0.25) into the formula bar and press Enter.
- 4. Adjust the cell formatting: Right-click the result cell, select 'Format Cells', and choose 'Number' to ensure the result is displayed as a proper decimal (e.g., 9.75).
Frequently Asked Questions
Why does my time calculation show up as a weird time format instead of a decimal?
By default, spreadsheets often carry over the Time format from your input cells to the formula cell. To see the proper decimal number (e.g., 9.75 instead of 9:45), right-click the cell, select 'Format Cells', and change the format category to 'General' or 'Number'.
Can I always round up to the next quarter hour instead of the nearest?
Yes. If you need to strictly round up (e.g., for billing purposes), replace the MROUND function with the CEILING function. Use the formula =CEILING((B1-A1)*24, 0.25).
Why do I need to multiply the time difference by 24?
Spreadsheets use a serial number system where 1 represents a full 24-hour day. Hours, minutes, and seconds are stored as decimal fractions of 1. Multiplying an elapsed time fraction by 24 converts it into standard decimal hours.
What if I enter 0730 without the colon?
If you enter 0730 without a colon, the software treats it as the whole number Seven Hundred Thirty, not a time value. You must enter time values with a colon (07:30) for time-based subtraction and conversion formulas to work properly.
