Date & Time
Group clock times into unequal shift or restaurant daypart buckets (MOD & XLOOKUP)
Classify timestamps into uneven operational windows (Early Bird, Lunch Rush, Afternoon, Dinner, Overnight) without nested IF chains.
Why FLOOR() fails on uneven dayparts—and MOD(Timestamp, 1) + XLOOKUP(-1) shines
FLOOR(Time, "1:00") works great for uniform 60-minute intervals, but real operations run on unequal dayparts: Overnight (00:00–05:59, 6 hrs), Breakfast (06:00–10:59, 5 hrs), Lunch Rush (11:00–13:59, 3 hrs), Off-Peak Afternoon (14:00–16:59, 3 hrs), Dinner Peak (17:00–21:59, 5 hrs), and Late Night (22:00–23:59, 2 hrs)!
First, strip any calendar date from a full datetime using MOD(A2, 1) so 2026-08-14 13:45 becomes pure clock fraction 0.5729 (13:45). Then run XLOOKUP(MOD(A2, 1), $D$2:$D$7, $E$2:$E$7, "", -1) against a 6-row threshold table starting at 00:00 (TIME(0,0,0) = 0)—every minute of the 24-hour clock snaps cleanly to its unequal daypart!
Use numeric spreadsheet times on one consistent local-time basis. MOD removes the date but does not convert time zones or handle daylight-saving transitions. The midnight threshold is required for full-day coverage; thresholds should be unique. Each bucket includes its start and excludes the next start.
| Daypart Start Threshold (D) | Bucket Name (E) | Duration | Sample Timestamp -> Bucket |
|---|---|---|---|
| 00:00 (=TIME(0,0,0)) | Overnight | 6 hrs | 02:15 -> Overnight |
| 06:00 (=TIME(6,0,0)) | Breakfast | 5 hrs | 08:30 -> Breakfast |
| 11:00 (=TIME(11,0,0)) | Lunch Rush | 3 hrs | 13:45 -> Lunch Rush |
| 14:00 / 17:00 / 22:00 | Afternoon / Dinner / Late Night | 3h / 5h / 2h | 22:00 -> Late Night |
Map any time or full datetime into unequal operational buckets
Accepts a numeric time fraction or date-time serial. Empty or textual inputs are flagged rather than treated as midnight.
=IF(ISNUMBER(A2),XLOOKUP(MOD(A2, 1), $D$2:$D$7, $E$2:$E$7, "", -1),"Enter a numeric timestamp")Verify exact boundary rollover at 14:00:00
Because match_mode = -1 means "exact match or next smaller", 14:00 sharp lands in Afternoon while 13:59 stays in Lunch Rush.
=IF(ISNUMBER(B2),XLOOKUP(MOD(B2, 1), $D$2:$D$7, $E$2:$E$7, "", -1),"Enter a numeric timestamp")Try the example
Use the original values or change them to test your understanding.