How to Calculate Overnight Time (Shifts That Cross Midnight)

A shift that starts at 10:00 PM and ends at 6:00 AM should come out to eight hours. Type =B2-A2 into a timesheet with those two clock times, though, and the result often comes back negative, or as a strange fraction, or as an error triangle. The math looks like it should be simple subtraction, but the moment a shift crosses midnight, the clock resets to 00:00 and the end time becomes technically "earlier" than the start time.

This trips up night-shift payroll, security rosters, call-center scheduling, and any timesheet where "clock out" happens the day after "clock in." The fix is a small adjustment once you know where the wraparound happens — here's how to calculate it correctly, by formula or by hand.

What is an overnight time calculation, and why is it different?

An overnight time calculation finds the duration between a start time and an end time when the end time falls on the calendar day after the start time — for example, a shift running from 11:00 PM to 7:00 AM. Ordinary time subtraction assumes both times sit on the same 24-hour clock cycle, so when the end time is numerically smaller than the start time, plain subtraction reads it as happening earlier, not later, and returns a negative or nonsensical result.

• Same-day shift (e.g., 9:00 AM–5:00 PM): end time is always later in the day, so direct subtraction works without adjustment.
• Overnight shift (e.g., 10:00 PM–6:00 AM): end time is numerically earlier, so the calculation needs to account for crossing into the next day.
• Mixed timesheet: a full week of shifts where some rows cross midnight and others don't needs one formula that handles both cases correctly without manual checking row by row.

Why the midnight rollover matters

Getting this wrong doesn't usually throw a visible error — it just quietly produces the wrong number of hours, which shows up in a few specific places:

• Night-shift payroll. An unadjusted formula can return a negative number of hours or, worse, a small positive number that dramatically understates what was actually worked.
• Security and facilities rosters. Coverage gaps are easy to miss when a shift's calculated length silently comes out wrong, making a fully staffed night look understaffed on paper.
• Call-center and healthcare scheduling. Overlapping overnight shifts need accurate hour totals to check against labor rules and mandatory rest periods between shifts.
• Overtime calculations. If overnight hours are undercounted, a week that actually crossed into overtime can look like it didn't.

Step-by-step: calculating overnight hours

Method 1: Manual calculation (no spreadsheet)

1. Count from the start time to midnight. For a 10:00 PM start, that's 2 hours until 12:00 AM.
2. Count from midnight to the end time. For a 6:00 AM end, that's 6 more hours.
3. Add the two segments together. 2 hours + 6 hours = 8 hours total, correctly spanning the midnight boundary.

Method 2: Excel formula with MOD (handles any shift automatically)

1. Enter the start and end times in separate cells. Format both as Time, for example A2 as the clock-in and B2 as the clock-out.
2. Use the MOD-wrapped subtraction formula. This works whether or not the shift actually crosses midnight.
   =MOD(B2-A2,1)*24
3. Format the result cell as Number. The formula returns decimal hours, such as 8 or 7.5, ready for payroll totals.

Method 3: Manual IF formula (if MOD feels less intuitive)

1. Build an IF statement that checks for a negative result. If the end time is earlier than the start time, add 24 hours; otherwise, subtract normally.
   =IF(B2
2. Press Enter. The result is the same decimal-hour total as Method 2, just written out explicitly instead of relying on MOD.

Common mistakes with overnight time calculations

1. Subtracting clock times directly without a wraparound check

=B2-A2 alone assumes the end time falls later on the same day, so any shift that crosses midnight returns a negative number or a meaningless fraction instead of the actual duration worked.

2. Applying the midnight fix to every row, including day shifts

Adding 24 hours unconditionally to every calculation will double-count same-day shifts, since those never needed the adjustment in the first place — this is exactly why MOD or an IF check needs to be conditional, not blanket.

3. Ignoring daylight saving time changes

On the two nights each year when clocks shift forward or back, an overnight shift that crosses that specific boundary will genuinely be an hour longer or shorter than the clock times alone suggest, which a simple time-of-day formula can't detect on its own.

4. Mixing date-and-time values with time-only values

If one cell stores a full date and time while another stores only a time, subtracting them can produce wildly incorrect results, since Excel is comparing two different kinds of values rather than two clock times on the same scale.

Real-world formula examples

A few common overnight scheduling scenarios and the formula each one needs.

Plain subtraction vs MOD-wrapped formula

A side-by-side look at how the two approaches behave once a shift crosses midnight.

Skip the formulas: free overnight time calculator

If a timesheet just needs one overnight shift checked and opening a spreadsheet feels like overkill, the Rebrixe Overnight Time Calculator works entirely in your browser: enter a clock-in and clock-out time, and it handles the midnight rollover automatically, returning the exact hours and minutes worked. No formulas, no account, no data sent anywhere.

Frequently asked questions