Date & Time
Generate Drift-Free Future Time Intervals and Slot Schedules (1440-Minute Anchor Math)
Generate N-minute appointment, shift, or dispatch intervals from an anchor start time without cumulative floating-point rounding drift by indexing off Start + (Slot - 1) * Minutes / 1440.
Why recursivePrevCell + TIME(0, 15, 0) accumulates IEEE-754 floating-point drift
In spreadsheet date-time arithmetic, 1 day = 1.0 and 1 minute = 1 / 1440 (a repeating binary fraction). Chaining B3 = B2 + 15/1440 across dozens of rows compounds tiny binary rounding residuals that eventually cause exact XLOOKUP or >= comparisons on "13:00" to miss by 1e-16.
Computing each slot directly from the fixed anchor time—Start + (SlotIndex - 1) * StepMinutes / 1440—and extracting exact integer hours and minutes via HOUR and MINUTE produces crisp, drift-free "HH:MM" labels and end-of-slot windows.
| Slot Formula Component | Expression | Purpose |
|---|---|---|
| Serial Slot Start | $E$2 + (A2 - 1) * $E$3 / 1440 | Direct offset from anchor (no cumulative drift) |
| Zero-Padded HH:MM Label | RIGHT("0" & HOUR(t), 2) & ":" & RIGHT("0" & MINUTE(t), 2) | Locale-independent 24-hour clock string |
| Slot Window Range | Start_HHMM & " - " & End_HHMM | Readable agenda block ("09:00 - 09:45") |
Anchor-Indexed Time Slot Window Formula
Calculate both start_t and end_t from the anchor TIME(9, 0, 0) in $E$2 and interval minutes in $E$3.
=LET(start_t, $E$2 + (A2 - 1) * $E$3 / 1440, end_t, $E$2 + A2 * $E$3 / 1440, RIGHT("0" & HOUR(start_t), 2) & ":" & RIGHT("0" & MINUTE(start_t), 2) & " - " & RIGHT("0" & HOUR(end_t), 2) & ":" & RIGHT("0" & MINUTE(end_t), 2))Try the example
Use the original values or change them to test your understanding.