An overnight shift needs a rule for the date boundary. Full start and end timestamps are safer than assuming every end time earlier than the start belongs to the next day.
Prefer complete timestamps
With full start and end timestamps in A2 and B2, =B2-A2 gives elapsed time. Apply Duration formatting. A start at 22:00 and next-day end at 06:00 should produce eight hours.
Inspect the actual dates and time-zone context. A negative result may indicate a reversed entry rather than an overnight shift that needs automatic wrapping.
Use clock-only arithmetic with limits
For a documented clock-only workflow where each shift lasts less than twenty-four hours, =MOD(B2-A2,1) can represent crossing midnight. That assumption must be explicit.
MOD removes whole-day differences. It can therefore conceal a reversed timestamp or turn a multi-day interval into only its remainder. Do not use it as a blanket correction for every negative or unexpected duration.
Validate breaks and exceptional shifts
Test a daytime shift, an overnight shift, equal clock times, and a genuine multi-day record. Equal times can mean zero duration or a full day; clock values alone cannot decide.
Subtract breaks only when their units and eligibility are established. Flag missing timestamps instead of silently treating them as midnight. For periods crossing time-zone or daylight-saving changes, preserve source offsets and use a workflow that correctly interprets those instants. A spreadsheet’s displayed clock difference should not be assumed to establish a payroll entitlement or a universally correct elapsed-time calculation without the governing rules.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.