Calculate Overnight Work Hours and Unpaid Breaks
On this page
An overnight shift needs a start date and an end date, not just two clock readings. Calculate gross time from the dated interval, deduct only explicitly unpaid breaks, then assign worked time to the selected workweek.
Use dated start and end points
Suppose a shift starts October 2 at 22:00 and ends October 3 at 06:00. The end is on the next day, so gross wall-clock duration is eight hours. A 30-minute unpaid break inside the shift leaves 7 hours 30 minutes, or 7.5 decimal hours. If start and end are the same date and time, duration is zero. If the entered end precedes start on the same date, correct the date or explicitly select "ends next day"; do not silently assume a 24-hour shift.
Adjacent shifts may meet at the same instant. Overlapping or duplicate shifts cannot be added without double-counting, so correct the rows before totaling them. Total your dated shifts once their endpoints are clear.
Deduct unpaid breaks and split workweeks
A paid break remains part of counted time. Deduct only a break that you designate unpaid, lies within the shift and does not overlap another unpaid break. If a shift crosses the workweek boundary, record the break's start and end as well so its deducted minutes belong to the correct week. A duration-only break entry cannot establish that split.
Five shifts of 7 hours 30 minutes each total 37 hours 30 minutes, not 37.30 hours. For a simple 45-hour week at a 20-per-hour base rate, a user-chosen 40-hour threshold and 1.5 multiplier produce 40 regular hours, five premium hours and 950 gross. Two weeks of 45 and 35 hours must be calculated separately: the same simple assumptions give 1,650 total gross, not the pay for one combined 80-hour week. Actual overtime entitlement and the legal regular rate depend on jurisdiction, classification and compensation details. The pay and work-time hub links to the separate pay tools.
Wall-clock versus elapsed time at clock changes
Ordinary time-card arithmetic can use local wall-clock readings. For actual elapsed instants, select an IANA time zone and real dates. On March 8, 2026 in America/New_York, 01:30 to 03:30 spans two displayed clock hours but one elapsed hour because the spring clock skips an hour. On November 1, 2026, 00:30 to 02:30 spans two displayed clock hours but three elapsed hours because the fall hour repeats. These examples use the zone's 2026 rules; the workweek and pay policy must still be checked separately.
A local time that never occurred at spring transition should be rejected. A repeated fall time such as 01:30 requires choosing the earlier or later occurrence. "CST" alone is not enough to select a zone. A calendar-week grouping by local dates is not automatically the same as a fixed 168-hour payroll workweek across a daylight-saving transition.
Use complete date-times in a spreadsheet
If A2 holds the full start date-time, B2 the full end date-time, and C2 the unpaid break in minutes, a simple decimal-hours formula is =(B2-A2)*24-C2/60. For the 22:00-06:00 next-day example with a 30-minute unpaid break, the result is 7.5. Fill it down for non-overlapping rows and use =SUM(D2:D8) for one workweek. Use a new weekly range for the next week, then compare each week's total with the chosen threshold.
Do not use MOD(B2-A2,1) as a universal overnight formula: it discards whole days. Standard spreadsheet date-time serials alone do not resolve IANA zone ambiguity or nonexistent local times. See the Excel and Sheets guide for duration formats and the separate rounding guide for policy choices.
Check the result before payroll use
Review the dated rows, break labels, workweek start, time mode, per-week totals and any applied rounding. The time-card result is a transparent estimate, not an official record or a determination that a break is legally unpaid. For pay from an already known total, use the Overtime Pay Calculator; for a duration without dated shifts, use the Decimal Hours Calculator.
Frequently asked questions
Is every lunch break unpaid?
No. Record whether a particular break is paid or unpaid under the applicable arrangement and rule; the calculator should not guess.
Can I combine two weeks before calculating overtime?
No. Show each workweek separately under the selected threshold and boundary convention.
Should I round each shift first?
Keep exact underlying minutes or seconds and apply only the explicitly chosen posting rule at the documented stage.
Sources and scope
Microsoft Support documents full date-time subtraction and accumulated hours in Excel. U.S.-specific legal context: Department of Labor overtime guidance and its regular-rate overview. Microsoft content was checked October 2, 2026; the DOL overtime page could not be fully fetched in this review, so legal details remain subject to current official verification. The numerical and DST examples here are independent CalculatorGeek acceptance cases, not payroll-law statements.