How to Calculate Hours Worked Across Midnight in Excel

Calculate night-shift lengths that cross midnight with MOD, convert them to decimal hours, and count premium hours after midnight.

You will learn to

• Calculate a shift length that crosses midnight
• Convert the shift to decimal hours
• Count only the hours after midnight

How it works

When a shift starts at 10:00 PM and ends at 6:30 AM, =C2-B2 is negative and Excel shows ##### or an error. Wrap it in MOD: =MOD(C2-B2,1) adds a full day whenever the result is negative, so it returns 8:30 for overnight shifts and still works for normal daytime shifts.

Multiply by 24 for decimal hours you can multiply by a pay rate: =MOD(C2-B2,1)*24. If the out time is earlier than the in time, the shift crossed midnight — and the out time itself (×24) is the number of hours worked after midnight.

=MOD(C2-B2,1)