Power Query Problems

How to Create a List of Quarterly End Dates in Excel

Aamir Naveed Akram Oct 22, 2026 868 views

Question details

The user needs to automatically generate a list of qualifying quarterly end dates (such as March 31, June 30, September 30, and January 1) along with corresponding sequence numbers for multiple given time periods.

How to Create a List of Quarterly End Dates for Multiple Excel Periods

Product: Excel
Device & OS: not provided
Scenario: Generating structured financial or reporting timelines where specific quarter-end dates must be sequenced accurately across multiple different date ranges.
Observed behavior: Requires a scalable method to process multiple period inputs and output every qualifying quarter-end date and its sequence number automatically.

Before you start

Ensure your starting and ending period dates are properly formatted as standard Excel dates and organized within an official Excel Table (Ctrl+T) so data connections can reference them dynamically.

Solution 1Recommended

Generate Quarterly Dates Using Power Query

Use a pre-configured Power Query setup to automatically evaluate date ranges and generate the necessary quarterly end dates.

Power Query is highly effective for generating date lists from start and end parameters. By utilizing a Power Query workbook, you can input your periods into a source table and simply refresh the data model to get the expected sequence.

Add records to the designated table

Open your Excel workbook and enter your multiple starting and ending periods into the designated source data table.

Refresh the data query

Navigate to the 'Data' tab on the Excel ribbon and click 'Refresh All'. Power Query will process the new periods and automatically output the corresponding quarterly dates and sequence numbers.

Generate Quarterly Dates Using Power Query

Update Automation: Whenever you add new date periods to your source table, a simple 'Refresh All' will instantly update your output list without writing new formulas.

Solution 2

Use Dynamic Array Formulas and LAMBDA

For Microsoft 365 users, dynamic array formulas offer a real-time, calculation-based approach without needing manual data refreshes.

Solution 3

Automate Using a VBA Macro

Run a VBA script to loop through periods and evaluate qualifying quarter-end dates systematically, which is useful for older Excel versions.

Free Microsoft Office alternative

Looking for a Lightweight Alternative for Data Processing?

Power Query and complex LAMBDA formulas in Microsoft Excel can be resource-intensive or difficult to maintain. WPS Office provides a free, lightweight, and highly compatible alternative, offering standard date functions, VBA macro support, and PivotTables to manage your quarterly period data effortlessly.

1. Download and install WPS Office: Get the free office suite from the official WPS website and complete the lightweight installation.
2. Open your existing Excel workbook: Launch WPS Spreadsheet and open your .xlsx files seamlessly without losing your data or standard formatting.
3. Manage your dates easily: Utilize built-in date functions like EOMONTH or run your existing VBA macros to calculate and sequence your quarterly periods.

Fully compatible with Microsoft Excel (.xlsx) formatsProcess financial and quarterly dates using built-in standard functionsSupports existing VBA macros for automated date generationLightweight installation with zero subscription fees

microsoft office alternative - wps office

Frequently Asked Questions

Why didn't my Power Query table update automatically after adding new dates?

Power Query tables do not calculate in real-time like standard Excel formulas. You must manually trigger an update by going to the Data tab and clicking 'Refresh All'.

Can I calculate quarterly end dates using standard Excel functions?

Yes, you can use the EOMONTH function combined with SEQUENCE and rounding math to find quarter ends. However, wrapping this logic across multiple varied periods requires dynamic arrays (Excel 365) or VBA to spill the results properly.

What if my company's fiscal quarters are different from standard calendar quarters?

If your fiscal year shifts the quarter-end dates (e.g., ending in April, July, October, January), you will need to adjust the date parameters in your Power Query logic or VBA macro to match your specific fiscal calendar.

Excel & Data Problems