'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Automatically Populate Excel Time Slots from Another Sheet
Question details
The user wants to automatically generate 15-minute time intervals between a specific start and end time, based on a selected person's schedule stored on a different worksheet.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Building an automated scheduling workbook where selecting a client or employee name retrieves their start and end times from a master schedule and fills the adjacent cells with a range of 15-minute availability slots.
- Observed behavior
- Currently, the user is looking for a functional way to use two retrieved cells (start and end times) to automatically expand and populate the available time range into a continuous grid of slots.
Ensure both your source schedule sheet and the target sheet have their time-related cells formatted uniformly using standard Time formats (e.g., h:mm AM/PM) to prevent calculation errors when generating the intervals.
Use Dynamic Array Formulas to Generate Time Slots
The most efficient way to generate 15-minute intervals is by combining lookup functions like XLOOKUP with the SEQUENCE function to dynamically spill the results.
Dynamic array formulas allow a single function to return multiple values into adjacent cells automatically. By looking up the start and end times, you can calculate the exact number of 15-minute slots required and use SEQUENCE to list them out.
In your target sheet, use the XLOOKUP or VLOOKUP function to find the selected person's name and retrieve their specific start and end times from the master schedule sheet.
Subtract the retrieved start time from the end time. Multiply the result by 24 (hours) and then by 60 (minutes), and divide by 15 to determine the total number of 15-minute rows needed.
In the cell where the time slots should begin, type a formula similar to =SEQUENCE(total_intervals, 1, start_time, TIME(0,15,0)). This instructs Excel to start at the lookup time and add 15 minutes per row.
Select the cells that the formula populates. Go to the Home tab, click the Number Format dropdown, and select 'Time' (or press Ctrl+Shift+@) so the results display as readable hours and minutes.
Generate Time Intervals Using Power Query
Ideal if you need to process large, complex schedules for multiple people simultaneously and output them into a structured data table rather than relying on on-sheet formulas.
Create Automated Schedules Easily with WPS Spreadsheet
WPS Spreadsheet fully supports advanced dynamic array formulas and functions like XLOOKUP, allowing you to instantly generate 15-minute time slots from another sheet with zero hassle and perfect precision.
- 1. Open your schedule: Launch WPS Spreadsheet and open the scheduling workbook containing your master timesheet.
- 2. Select the target cell: Click on the cell in your daily view sheet where you want the 15-minute intervals to begin populating.
- 3. Enter the dynamic formula: Input your combined SEQUENCE and XLOOKUP formula to fetch the start time and generate the 15-minute increments using TIME(0,15,0).
- 4. Watch the slots spill: Press Enter, and WPS Spreadsheet will automatically spill the accurately calculated time slots into the adjacent cells downwards.
Frequently Asked Questions
Why are my generated time slots showing as strange decimal numbers?
Spreadsheet software stores time as a fraction of a 24-hour day (e.g., 12:00 PM is 0.5). To fix this, highlight the populated cells, right-click to choose 'Format Cells', and apply a 'Time' format so they display correctly.
Can I change the time intervals from 15 minutes to 30 minutes?
Yes. If you are using the SEQUENCE formula, simply change the fourth argument (the step value) from TIME(0,15,0) to TIME(0,30,0) to generate half-hour slots instead.
What if the person's schedule crosses midnight?
When calculating time intervals that cross midnight, the end time is mathematically smaller than the start time. You must add 1 to the end time before subtracting the start time (e.g., (End_Time + 1) - Start_Time) so the formula recognizes the time belongs to the next day.
Why is my SEQUENCE formula returning a #SPILL! error?
A #SPILL! error occurs when there is existing text, data, or formatting blocking the cells where the dynamic array needs to place the generated time slots. Clear the cells directly below your formula to allow the intervals to populate.
