Dates, Time & Schedules
Generate a Dynamic Sequence of Time Slots Every N Minutes (SEQUENCE & TIME)
Spill an exact clinic, interview, or conference schedule of N appointment slots spaced every M minutes using SEQUENCE(Slots, 1, TIME(Start_Hr, Start_Min, 0), Interval_Min / 1440).
Build 15-, 20-, or 30-minute booking grids
Because spreadsheet clock times are fractions of a 24-hour day (1 hour = 1 / 24, 1 minute = 1 / 1,440), generating a booking grid of 8 appointment start times beginning at 09:00 spaced every 30 minutes is a single dynamic-array call: SEQUENCE(8, 1, TIME(9, 0, 0), 30 / 1440). Change the interval cell from 30 to 15 or 45 minutes and every time slot recalculates instantaneously. A displayed HH:MM time wraps after midnight and does not identify which day it belongs to; use date-time values if slots can cross days. The span below assumes each appointment lasts exactly the interval between starts.
You can also compute how many slots fit between Opening Hour (e.g., 09:00) and Closing Hour (e.g., 13:00) via INT(((End_Time - Start_Time) * 1440) / Interval_Min), and format any slot's serial number as portable 24-hour "HH:MM" text via RIGHT("00" & HOUR(t), 2) & ":" & RIGHT("00" & MINUTE(t), 2).
| Start Time & Slot Count | Interval Step (Min / 1440) | First Slot | Final Spilled Slot |
|---|---|---|---|
| 09:00 Start, 8 Slots | 30 / 1440 (Half-hour) | 09:00 | 12:30 (Slot #8) |
| 09:00 Start, 8 Slots | 15 / 1440 (Quarter-hour) | 09:00 | 10:45 (Slot #8) |
| 08:30 Start, 6 Slots | 45 / 1440 (45-min) | 08:30 | 12:15 (Slot #6) |
Final Appointment Start Time ("HH:MM") of the Spilled Time Sequence
Generates B4 slots starting at TIME(B2, B3, 0) stepped by B5 / 1440 days and formats the final slot as HH:MM.
=IF(OR(B2 < 0, B2 > 23, B3 < 0, B3 > 59, B4 < 1, B5 <= 0), "Invalid Schedule Inputs", RIGHT("00" & HOUR(MAX(SEQUENCE(B4, 1, TIME(B2, B3, 0), B5 / 1440))), 2) & ":" & RIGHT("00" & MINUTE(MAX(SEQUENCE(B4, 1, TIME(B2, B3, 0), B5 / 1440))), 2))Total Schedule Span Covered by All Slots (Hours)
Multiplies slot count B4 by slot duration B5 (minutes) and divides by 60 minutes/hour. This assumes appointment length equals the interval; spacing alone does not establish duration.
=IF(OR(B4 < 1, B5 <= 0), "Invalid Schedule Inputs", ROUND((B4 * B5) / 60, 2))Try the example
Use the original values or change them to test your understanding.