How to Calculate Regular and Overtime Hours Correctly in Excel
Question details
Calculate accurate regular and overtime hours from times entered as plain numbers (HHMM) without treating minutes as base-100 decimals.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating employee payroll or timesheets where start and end times need to be separated into regular shifts and overtime.
- Observed behavior
- Subtracting raw number values like 2330 and 1156 treats minutes as decimals, leading to incorrect hour calculations because time relies on a 60-minute scale.
Check your timesheet data to see if it is formatted as actual Time (with colons, like 11:56) or as plain numbers (like 1156). The calculation method depends on your data format.
Use Standard Excel Time Formats for Arithmetic
The most reliable method is to enter times using colons. This allows Excel to naturally compute base-60 time differences without complex conversions.
When you use standard time formats, Excel interprets the data as a fraction of a 24-hour day, allowing you to use simple subtraction to find the difference between shifts.
Select the cells for your start and end times, right-click, choose 'Format Cells', select the 'Time' category, and pick a standard format (e.g., 13:30).
Input your start and end times using a colon (for example, enter 11:56 instead of 1156).
In a new cell, use the formula =(End_Time - Start_Time) * 24 to get the total hours worked as a decimal.
Assuming an 8-hour workday, use the formula =MIN(Total_Hours, 8) to calculate the regular hours.
To calculate any time worked beyond 8 hours, use the formula =MAX(Total_Hours - 8, 0).
Convert Raw HHMM Numbers Using DOLLARDE and DOLLARFR
If your times are already entered as raw numbers without colons (e.g., 1156), use the DOLLARDE function to correctly calculate base-60 differences.
Easily Calculate Timesheets with WPS Spreadsheet
WPS Spreadsheet fully supports advanced time calculations, standard time formatting, and financial conversion functions like DOLLARDE, making payroll and timesheet management seamless.
- 1. Open your file: Launch WPS Spreadsheet and open your timesheet document.
- 2. Format time data: Highlight your time entries, right-click, select 'Format Cells', and apply the correct Time format.
- 3. Apply formulas: Use standard time subtraction or the DOLLARDE conversion formulas exactly as you would in Microsoft Excel.
Frequently Asked Questions
Why am I getting negative numbers when calculating shifts that cross midnight?
Standard subtraction yields a negative result if the end time is numerically smaller than the start time (e.g., 02:00 minus 22:00). You can fix this by wrapping the subtraction in the MOD function: =MOD(End_Time - Start_Time, 1).
Why does 8 hours and 30 minutes show up as 8.5 in my spreadsheet?
When you multiply an Excel time value by 24 to get total hours, Excel converts the time to a decimal value. 30 minutes is half an hour, so it correctly displays as 0.5. To display it as 8:30 instead, simply subtract the times without multiplying by 24, and format the result cell as Time.
What does the DOLLARDE function do in time calculations?
Typically used for financial fractions, the DOLLARDE function converts a number with a fractional part (representing base-60 minutes) into a standard decimal. This allows you to perform accurate arithmetic on time values that were incorrectly entered as raw numbers (like 1156).
