A negative subtraction is not always a bad shift
The exercise permits one midnight crossing. MOD supplies that stated interpretation.
Calculate hours for a shift across midnight
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill D2:D5 with numeric hours for shifts shorter than 24 hours. A and B are numeric clock times at minute precision, C is integer unpaid break minutes. The end may be on the next day; equal endpoints mean zero duration. Return Review for negative breaks or breaks longer than the shift. No timezone, daylight-saving, overtime or payroll policy is inferred.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: D2, D3, D4, D5. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Use one-day clock wrap, whole-minute arithmetic and a declared break rule to calculate numeric paid hours.
THE QUICK ANSWER01
Use one-day clock wrap, whole-minute arithmetic and a declared break rule to calculate numeric paid hours.
ƒx
=IF(OR(C2<0,C2>ROUND(MOD(B2-A2,1)*1440,0)),"Review",(ROUND(MOD(B2-A2,1)*1440,0)-C2)/60)For the example data
D2 8D3 7.25D4 0D5 Review
The formula uses the interactive worksheet above. Adjust ranges and criteria for your own data.
How it works
Clock wrap needs a business assumption
22:00 through 06:00 means eight clock hours under the less-than-one-day rule. Time-only values cannot identify a two-day shift. Use full dated timestamps when that distinction matters.
Minutes and decimal hours are different
Seven hours fifteen minutes is 7.25 hours, not 7.15. This exercise does not round pay or apply overtime; it returns the exact net minute count divided by 60.
Read the full step-by-step walkthrough
- Name the one-day assumption
The source has clock times without dates. An earlier end is on the next day; equal endpoints mean zero, not a full day. Longer shifts need dated timestamps.
- Wrap across midnight
MOD with divisor 1 retains the positive fraction of one day. Subtraction without this wrap makes an overnight shift negative.
- Work in integer minutes
The source is at whole-minute precision. Multiply the day fraction by 1440 and round to the nearest minute to remove floating-point residue.
- Review impossible breaks
A break must be nonnegative and no longer than the full shift. Return Review rather than allowing a negative paid duration.
- Convert net minutes to hours
Subtract valid break minutes and divide by 60. Return numeric hours, not HOUR, which extracts only the hour component.
Things to watch
Clamping a bad break hides a data issue
MAX(0,...) would turn an excessive break into zero. The task deliberately requires Review for that record.
About this example
Formula entry, fill and all six authored datasets were checked in Chrome on October 18, 2026. Numeric timestamp/clock display and original/current/frozen downloads have separate package/browser checks. Local clocks carry no timezone or daylight-saving interpretation; no payroll policy is inferred. Native Excel scope is recorded in the date/time validation report; Google Sheets remains unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.