Date & Time
Group times & timestamps into unequal shift or daypart buckets (MOD & XLOOKUP)
Map irregular clock windows (e.g., 06:30 Morning, 14:00 Swing, 21:30 Night) from full date-timestamps by stripping date serials with MOD(A2, 1) and XLOOKUP(..., -1).
Assign daily clock windows using integer seconds
The clock windows are half-open: midnight through just before 06:30, 06:30 through just before 14:00, 14:00 through just before 21:30, and 21:30 until midnight. Exact start times belong to the new window. These daily labels do not assign an overnight shift to its previous work date.
Serial dates do not contain time-zone or daylight-saving information. Convert timestamps to the intended local clock before using this example. Text timestamps are rejected; numeric time-only fractions and nonnegative date serials are accepted.
The example deliberately rounds timestamps and boundary times to the nearest second before comparison. This avoids tiny floating-point differences when a full date is combined with a time. Subsecond events therefore follow this rounding policy, including rounding across midnight. Do not use it when subsecond classification matters.
| Bucket Start TIME (D) | Daypart / Shift Label (E) | Sample Timestamp (A) | Mapped Bucket |
|---|---|---|---|
| 00:00 (TIME(0,0,0)) | Graveyard | 2026-10-30 03:15 | Graveyard |
| 06:30 (TIME(6,30,0)) | Morning (7.5 hrs) | 2026-10-30 10:15 | Morning |
| 14:00 (TIME(14,0,0)) | Swing (7.5 hrs) | 2026-10-30 14:00 (Exact boundary) | Swing |
| 21:30 (TIME(21,30,0)) | Late (2.5 hrs) | 2026-10-30 22:45 | Late |
Assign full timestamp or pure time to unequal shift bucket
F2:F5 converts each start time to seconds. Require four strictly increasing rounded boundaries beginning at zero and nonblank labels. XLOOKUP match_mode -1 chooses the latest start not after the rounded event time.
=IF(OR(COUNT(A2)<>1,A2<0),"Invalid timestamp",IF(OR(COUNT($D$2:$D$5)<>4,$D$2<>0,MIN($D$2:$D$5)<0,MAX($D$2:$D$5)>=1,$F$2>=$F$3,$F$3>=$F$4,$F$4>=$F$5,COUNTBLANK($E$2:$E$5)>0),"Invalid schedule",XLOOKUP(MOD(ROUND(MOD(A2,1)*86400,0),86400),$F$2:$F$5,$E$2:$E$5,"Unknown",-1)))Count events landing in a specific daypart bucket
With B2:B5 storing each timestamp's mapped shift, COUNTIF tallies volume per shift.
=COUNTIF(B2:B5, "Morning")Try the example
Use the original values or change them to test your understanding.