How to Calculate an Overnight Shift Across Midnight
Shifts that start on one day and finish the next morning frequently cause negative calculation errors in spreadsheets like Excel or Google Sheets. Here is the correct mathematical method.
Overnight Shift Duration = (Clock-Out Time + 24 Hours) − Clock-In Time − Unpaid Breaks.
Example: For a shift from 10:00 PM to 6:30 AM with a 30-minute unpaid break: from 10:00 PM to midnight is 2 hours, and from midnight to 6:30 AM is 6.5 hours (8.5 total elapsed hours). Deducting 30 minutes leaves exactly 8.00 paid hours.
Automate Overnight Math
Our specialized Overnight Shift Calculator automatically handles date rollover without error.
Why Standard Subtraction Fails for Night Shifts
When calculating regular daytime hours, you subtract start time from end time:
However, on an overnight shift from 10:00 PM to 6:30 AM, 6:30 AM is numerically smaller than 10:00 PM (06:30 < 22:00). Subtracting 22:00 from 06:30 produces -15.5 hours!
The Two Correct Calculation Methods
Method 1: Split at Midnight (12:00 AM)
Break the shift into two blocks: before midnight and after midnight.
- From 10:00 PM to 12:00 AM Midnight = 2 hours.
- From 12:00 AM Midnight to 6:30 AM = 6.5 hours.
- Total Elapsed Shift = 2 + 6.5 = 8.5 hours (8 hours, 30 minutes).
Method 2: Add 24 Hours (The Formula)
If the end time is less than start time, add 24 hours (1440 minutes) to the calculation:
In minutes: (390 + 1440) - 1320 - 30 = 1830 - 1320 - 30 = 480 minutes = 8.00 paid hours.
=MOD(End_Time - Start_Time, 1) * 24 to automatically handle shifts crossing midnight without negative values.