|
Hi - Dave here.
Happy Friday!
How can you generate a list of quarter dates in Excel with a formula?
This is an interesting problem that you can neatly solve with two old functions, EDATE and EOMONTH, and one new function, SEQUENCE.
You can see the result in the worksheet below, where the formula in cell D5 is:
=EDATE(B5,SEQUENCE(12,,0,3))
With a start date of 1-Jan-2024 in cell B5, the formula returns the next 12 quarter start dates. A similar formula in cell E5 generates the next 12 quarter end dates.
[ Read the full article and download the workbook]
This kind of formula automation is great for financial reporting or project timelines, helping to streamline tasks and avoid manual errors. It's also a nice example of how the EDATE and EOMONTH functions are different and how they can be "upgraded" with the SEQUENCE function to return multiple results. Download the workbook and try it out yourself.
Note: The formula above requires Excel 2021 or later. However, I've added traditional formulas on Sheet2 of the worksheet to show you how to perform this task in older versions of Excel.
Excel formulas
We maintain a list of over 500 working formulas here.
If you need more structure, we also offer video training.
Have a great weekend!
Dave
The Exceljet newsletter is free and sent weekly on Fridays. Each week, I take a deep dive into one useful Excel formula. You can sign up on our home page. |
