How to Expand Excel Date Ranges into One Row per Date
Question details
The user needs to convert date ranges defined by start and end dates into multiple rows, generating one distinct row for each individual date within that range.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing absence records, timesheets, or scheduling data where activities are logged as a single entry with a start and end date, but daily granular data is required for analysis.
- Observed behavior
- The current worksheet displays data as a single row per date range, but the goal state is to output multiple expanded rows so that every date in the range has its own corresponding row.
Before attempting to expand your date ranges, ensure your 'Start Date' and 'End Date' columns are strictly formatted as valid dates rather than text to avoid calculation errors.
Use Power Query to Expand Date Ranges
Power Query is the most robust and automated method to generate multiple rows from a date range, as it automatically duplicates the adjacent row data.
This method utilizes Power Query's custom list generation feature to calculate the dates between the start and end point, then expands that list into individual rows.
Select your data table, navigate to the Data tab on the ribbon, and click 'From Table/Range' to open the Power Query Editor.
Go to the Add Column tab and click 'Custom Column'. In the dialog box, name the new column (e.g., 'Daily Dates') and enter the formula: ={Number.From([Start Date])..Number.From([End Date])}
Click the expand icon (two diverging arrows) located on the header of your new 'Daily Dates' column and select 'Expand to New Rows'.
Click the data type icon on the new column header and change it to 'Date'. Finally, go to the Home tab and click 'Close & Load' to output the expanded dataset into a new worksheet.
Use Helper Formulas for Simple Worksheets
A formula-based approach using ROW functions can be used for straightforward, smaller datasets if you prefer not to use Power Query.
Manage and Expand Data Efficiently with WPS Office
WPS Spreadsheet offers powerful built-in functions, dynamic arrays, and seamless Excel compatibility, making it easy to split date ranges and organize large datasets without complex configurations.
- 1. Open your dataset: Launch WPS Spreadsheet and open the worksheet containing your start and end dates.
- 2. Apply dynamic formulas: Use dynamic array formulas like =SEQUENCE(End_Date - Start_Date + 1, 1, Start_Date) in a blank column to quickly generate a list of dates.
- 3. Format and save: Format the generated results as Dates using the cell formatting options, then save your file seamlessly in .xlsx format.
Frequently Asked Questions
Does expanding rows duplicate the other data in the row?
Yes, when you expand rows using the Power Query 'Expand to New Rows' feature, the data in the adjacent columns (such as Person and Reason) is automatically duplicated for each new date row generated.
Why am I getting an error when expanding the date column in Power Query?
This usually happens if the Start Date or End Date columns contain text, blanks, or null values. Ensure both columns are explicitly formatted as Dates in Power Query before adding the custom Number.From column.
Can I use the SEQUENCE function instead of ROW to expand dates?
Yes, if your spreadsheet software supports dynamic arrays, the SEQUENCE function offers a much cleaner way to generate a list of dates between two given points without manually dragging the ROW formula down.
