How to Generate a Date Sequence Between Two Dates in Excel
Question details
The user wants to automatically create a sequential list of dates between a specified start date and end date without manual dragging.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a timeline, calendar, or scheduling list based on dynamic start and end dates entered in specific cells.
- Observed behavior
- The user needs a modern formula approach to spill the date sequence automatically into adjacent cells.
Ensure your start date and end date are formatted as valid dates in your spreadsheet, and verify that there is enough empty space below or beside the formula cell to accommodate the spilled results.
Use the SEQUENCE Function to Spill Dates Down Rows
This is the most efficient method to automatically generate a vertical timeline between two dates.
The SEQUENCE function can calculate the exact number of days between your start and end date, generating an array of sequential dates automatically.
Enter your start date in a cell (e.g., C2) and your end date in another cell (e.g., C3).
Click to select the cell where you want the date sequence to begin spilling.
Enter the formula =SEQUENCE(C3-C2+1,,C2,1) in the formula bar and press Enter.
Select the newly spilled results, right-click, choose 'Format Cells', and apply your preferred Date format.
Spill Dates Across Columns Instead of Rows
If your spreadsheet layout requires a horizontal timeline, you can adjust the SEQUENCE function parameters to spill horizontally across columns.
Easily Manage Dynamic Data with WPS Spreadsheet
WPS Office fully supports dynamic array functions like SEQUENCE, allowing you to build automated timelines, calendars, and schedules effortlessly.
- 1. Open WPS Spreadsheet: Launch WPS Office and create a new blank spreadsheet.
- 2. Enter your dates: Input your start and end dates into cells C2 and C3.
- 3. Apply the SEQUENCE formula: Type =SEQUENCE(C3-C2+1,,C2,1) in your target cell to instantly generate your date sequence.
Frequently Asked Questions
Why am I seeing random 5-digit numbers instead of dates?
Spreadsheets store dates as sequential serial numbers (e.g., 44927). To fix this, simply select the cells containing the numbers, right-click, select 'Format Cells', and change the number category to 'Date'.
Why am I getting a #SPILL! error?
A #SPILL! error occurs when there is existing data blocking the cells where the SEQUENCE function needs to output its results. Clear the cells below (or beside) your formula to resolve this.
Can I use the SEQUENCE function inside an Excel Table?
No, dynamic array formulas that spill (like SEQUENCE) are not supported inside structured Excel Tables. You must use them in normal ranges outside the table boundaries.
