Calculation Issues

How to Calculate Overtime for Shifts Crossing Midnight in Excel

Maira Mehtab Sep 20, 2026 868 views

Question details

The user wants to calculate the total hours and overtime for employee shifts, specifically needing a method to handle shifts that start on one day and end after midnight on the next, while treating weekends as non-working days.

Product: Microsoft Excel
Device & OS: not provided
Scenario: Tracking employee work hours and calculating overtime for variable shift schedules.
Observed behavior: Finding a way to accurately output the time difference for shifts ending on the next calendar day without resulting in negative time calculation errors.

Before you start

Ensure your start time and end time columns are formatted as "Time" (e.g., hh:mm AM/PM) so that the calculation formulas can recognize the values correctly.

Solution 1Recommended

Use a Logical Time Calculation Formula

Apply a mathematical formula to correctly calculate the duration between a start time and an end time, even when the shift falls on the next calendar day.

Standard subtraction fails when an end time is chronologically earlier than the start time (e.g., starting at 10 PM and ending at 6 AM). By adding a boolean condition to the formula, you force Excel to add 1 whole day (24 hours) to the end time when it crosses midnight.

Select the target cell

Click on the cell where you want the total shift hours to be displayed (e.g., cell E2).

Enter the formula

Assuming cell C2 contains the Start Time and cell D2 contains the End Time, type the formula =D2-C2+(D2<C2). This ensures that if the End Time is less than the Start Time, 24 hours are added to the calculation.

Format as time

Right-click the cell, select "Format Cells", navigate to the "Number" tab, select "Time", and choose your preferred duration format. Click OK.

Apply to all rows

Click and drag the fill handle at the bottom-right corner of the cell to copy the formula down to the rest of the shift records.

Adjusting Cell References: Depending on your column layout, ensure that the formula always follows the structure: =EndTime-StartTime+(EndTime<StartTime).

Solution 2

Utilize Built-in Timesheet Templates

Save time and avoid manual formula entry by using a pre-made Excel template designed specifically for shift and overtime calculations.

Efficient Time Tracking

Easily Calculate Shift Overtime in WPS Spreadsheet

WPS Office provides robust formula support and a rich, free library of pre-built timesheet templates, making it incredibly easy to track hours and calculate overtime for complex variable shifts.

1. Open your document: Launch WPS Spreadsheet and open your existing timesheet or start a blank workbook.
2. Apply the calculation formula: Select the duration cell and enter the formula =End_Time-Start_Time+(End_Time<Start_Time) to calculate hours worked.
3. Use free templates: Alternatively, click "New" on the homepage and search the Template library for ready-to-use timesheet models that automatically calculate overtime.

Fully compatible with Microsoft Excel formulas, functions, and files (.xlsx).Extensive free template library for timesheets, payroll, and scheduling.Intuitive formatting options to quickly handle time and date calculations.Lightweight software that runs smoothly on Windows, Mac, and Linux.

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a string of hash symbols (######) when subtracting time in Excel?

This happens when subtracting a later time from an earlier time results in a negative value, which standard time formatting cannot display. Using the midnight-crossing formula structure (adding +1 or +(EndTime<StartTime)) resolves this by adjusting the calculation to the next day.

How do I calculate total shift hours as a decimal instead of a time format?

To view the hours as a decimal number (e.g., 8.5 hours instead of 8:30), multiply your time calculation formula by 24. For example, use =(D2-C2+(D2<C2))*24, and be sure to format the resulting cell as "Number" or "General" instead of "Time".

How can I exclude weekends from my weekly overtime calculations?

If you need to calculate total available working days excluding Saturday and Sunday, you can use the NETWORKDAYS or NETWORKDAYS.INTL function. You can then reference these working days to determine regular expected hours before calculating any overtime surplus.

Excel & Data Problems