How to Split Date Ranges into Individual Daily Rows in Excel
Question details
The user wants to transform a dataset by expanding records with start and end dates so that every single day within that range gets its own dedicated row with a duration of 1, retaining the original record details.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Expanding summarized date durations, such as employee leave records or event schedules, into granular daily rows for more detailed tracking and analysis.
- Observed behavior
- The current dataset lists only the start date and end date on a single row. The goal is to generate multiple rows from this single record, one for each date in the range.
Ensure your Start Date and End Date columns are properly formatted as Dates (not Text) before processing, and duplicate your original dataset to safely verify the results.
Use Power Query to Expand Date Ranges
Power Query is the most efficient and robust method to dynamically expand date ranges into individual rows.
By utilizing Power Query's custom column feature, you can generate a list of sequential numbers representing each day between the start and end dates, and then expand 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').
In the custom column formula box, type: {Number.From([Start Date])..Number.From([End Date])} and click OK. This generates a list of serial date numbers for each row.
Click the expand icon (two diverging arrows) on the header of the newly created 'Daily Dates' column, then select 'Expand to New Rows'.
Click the data type icon on the new column header and change it to 'Date'. Add a new custom column for 'Duration' and set its value to 1, then click 'Close & Load' on the Home tab to output the expanded dataset.
Use Excel Formulas (SEQUENCE, INDEX, MATCH)
If you prefer not to use Power Query, you can achieve this using dynamic array formulas and helper columns directly in your worksheet.
Manage and Expand Your Data using WPS Spreadsheet
WPS Spreadsheet provides powerful data processing tools and comprehensive formula support. You can easily apply advanced dynamic array functions like SEQUENCE, INDEX, and MATCH to split date ranges into daily rows without complex scripting.
- 1. Open your dataset: Launch WPS Spreadsheet and open the workbook containing your start and end dates.
- 2. Set up a cumulative helper column: Add a column to calculate the length of each date range and use a running total formula to define the boundaries for each record.
- 3. Apply dynamic formulas: Use the SEQUENCE function combined with INDEX and MATCH to generate a row for every single day across all date ranges dynamically.
- 4. Format as dates: Select the newly generated date values, right-click, choose 'Format Cells', and apply your preferred Date format.
Frequently Asked Questions
Why are my dates turning into random numbers when I use the Power Query custom column?
In Power Query, dates are stored as numerical serial numbers internally. When you use the {Number.From([Start Date])..Number.From([End Date])} formula, it outputs these raw serial numbers. You simply need to click the data type icon on the column header and change it back to 'Date' after expanding the rows.
Can I split date ranges into rows if my start and end dates are exactly the same?
Yes. Both the Power Query method and the formula-based method will handle this perfectly. The duration will simply be evaluated as 1 day, and the process will generate exactly one row for that specific date without errors.
How do I exclude weekends and holidays when expanding date ranges?
After expanding the date range into individual daily rows, you can add a new column using the WEEKDAY function (or Date.DayOfWeek in Power Query) to identify the day of the week. You can then filter out Saturdays and Sundays, or merge the dataset with a list of recognized holidays to exclude them.
Does this process alter my original data source?
No. Whether you use Power Query or the formula method, your original data table remains intact. Power Query loads the expanded data into a brand-new worksheet, while formulas generate a separate output range.
