State the clock and endpoint rules before calculating
Keep date/time values numeric, validate normalized imports and explain what an overnight or inclusive interval means.
| Start clock | End clock | Break minutes | Net hours |
|---|---|---|---|
| 08:30 | 17:00 | 30 | 8 |
| 22:00 | 06:00 | 45 | 7.25 |
| 09:00 | 09:00 | 0 | 0 |
| 12:00 | 12:15 | 20 | Review |
Accept the declared import grammar
A source such as 2024-02-29 00:01 has a date and a clock reading. Extract the year, month, day, hour and minute, construct a numeric timestamp, then format it back to the source grammar for validation.
DATE and TIME can normalize invalid components. A successful result from February 30 is insufficient: require the reconstructed text to equal the complete original input. Preserve blanks separately from malformed text.
A time without a date needs an assumption
For the shift exercise, an earlier end clock is on the next day and every span is shorter than 24 hours. Equal clocks mean zero duration. A time-only row cannot distinguish zero from 24 hours or identify daylight-saving elapsed time.
Convert the wrapped day fraction to whole minutes, review a negative or excessive break, then divide net minutes by 60. Seven hours fifteen minutes is 7.25 hours. This is clock arithmetic, without overtime or pay policy.
=MOD(B2-A2,1)*24Name inclusive endpoints
An inclusive date interval includes its start and end. Clip an original record to the reporting window with the later start and earlier end, then add one to the difference. A single shared endpoint counts one; valid disjoint intervals count zero.
Check reversed source records before clipping. The task returns Review because sorting the endpoints would silently reinterpret the original record. This calendar-date count includes weekends; working-day counts need their separate calendar.
=IF(B2<A2,"Review",MAX(0,MIN(B2,$G$3)-MAX(A2,$G$2)+1))Formatting cannot establish correctness
The practice sheet and downloads display timestamps and clock times with declared number formats. Their underlying values stay numeric, while Review remains literal text. A displayed date is not proof that the import was valid.
Use the changing datasets to check rollover, leap-day, midnight, zero-span, break and shared-endpoint cases. Timestamp grading uses a small absolute day tolerance so a seconds-level error cannot disappear behind a large date serial.
Check the idea
Two valid inclusive date intervals share exactly their endpoint. How many shared calendar dates are there?
Try the idea
References
Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.