How to Convert Date Ranges into Daily Rows with Power Query in Excel
Question details
The user needs to transform records that have a start date, end date, and total hours into one row per calendar day using Power Query and calculate the daily hour amount.
- Product
- Microsoft Excel Power Query
- Device & OS
- not provided
- Scenario
- A user is analyzing holiday, leave, or overtime data stored as date ranges and needs granular daily records for reporting or analysis.
- Observed behavior
- The data is currently stored as date ranges; the goal state is one row per day with daily hours calculated from the range total.
Make sure the source data is formatted as an Excel table with columns for start date, end date, and total hours. Confirm that the date columns are recognized as real date values before loading the data into Power Query.
Expand Date Ranges into Daily Rows and Calculate Daily Hours in Power Query
Use this refreshable Power Query method when you need each start and end date range to become one row per calendar day and daily hours to be calculated automatically.
This method is reusable and refreshable. Power Query creates a list of dates for each range, expands the list into rows, and lets you compute daily hours without manually inserting rows.
In Excel, select any cell in the source table, go to the Data tab, and click From Table/Range to open the Power Query Editor.
In Power Query, go to the Add Column tab, click Custom Column, enter a column name such as Dates, and use the formula List.Dates([Start Date], Duration.Days([End Date] - [Start Date]) + 1, #duration(1,0,0,0)).
Click the expand icon in the new Dates column, choose Expand to New Rows, and set the expanded column data type to Date. Remove or rename the original date columns so the expanded date column becomes the daily date field.
Go to Add Column > Custom Column, name it Daily Hours, and enter a formula that divides Total Hours by the number of days in the range, such as [Total Hours] / (Duration.Days([End Date] - [Start Date]) + 1).
On the Home tab, click Close & Load, choose where to place the result, and use Data > Refresh All or the query refresh command when source date ranges change.
Use WPS Office as a Free Spreadsheet Alternative for Date-Based Reporting
Power Query is a Microsoft Excel feature and is not built into WPS Office in the same way. WPS Office is a free, lightweight Microsoft Office alternative that opens, edits, and saves Excel workbooks with familiar spreadsheets and formats, making it useful for everyday date-based reports and migration.
- 1. Download WPS Office: Visit the official WPS Office website, download the free installer for your device, and complete the installation.
- 2. Open your Excel workbook: Launch WPS Spreadsheets and open the .xlsx file that contains your date-range or daily-row data.
- 3. Edit and save the workbook: Use the familiar spreadsheet tools and compatible formulas for date calculations, then save the file in Excel format for sharing.
Frequently Asked Questions
Does WPS Office include Power Query?
No, Power Query or Get & Transform is a Microsoft Excel feature and is not currently built into WPS Office. If you need this exact expansion, perform it in Excel. WPS Office can still open and edit the resulting Excel files.
Can I convert date ranges to daily rows in WPS Spreadsheets using formulas?
Yes, for smaller datasets you can create a helper list of dates with formulas or manually expand rows, but it is not an automated Power Query equivalent. WPS Spreadsheets supports many Excel-compatible functions for date calculations.
How do I make the Power Query output refresh when source data changes?
In Excel, click Data > Refresh All or right-click the output table and choose Refresh. The query will re-run and update the expanded daily rows.
What formula splits total range hours into daily hours in Power Query?
Add a Custom Column and divide the total range hours by the number of days in the range, for example [Total Hours] / (Number.From([End Date] - [Start Date]) + 1).
What should I do if my dates import as text?
In Power Query, select the date columns and set Data Type to Date. If the text format is not recognized, use Date.From or change the locale in the column type menu.
