Extracting End of Quarter Dates in Excel

Another MOD solution

A few years ago I wrote an article on extracting the end of quarter date from a date. I recently had a query that was related and I tweaked the previous solution to solve it.

The original article can be found at the button below.

Original Article Link

The example file for this post can be downloaded at the bottom of this post.

The recent query related to calculating the quarter end dates for quarters ending in Feb, May, Aug and Nov.

See the image below with all the quarters showing.

The formula for ”normal” quarters Mar, Jun, Sep and Dec is in cell B2 and has been copied down. It is

=EOMONTH(A2,MOD(3-MONTH(A2),3))

The formula for a Feb quarter in cell C2 (the answer to the query) is

=EOMONTH(A2,MOD(2-MONTH(A2),3))

Can you guess the formula for cell D2, for a Jan Quarter?

=EOMONTH(A2,MOD(1-MONTH(A2),3))

Basically the month number of the first month quarter is the number you deduct the MONTH result from within the MOD function.

Download Example File

Please note: I reserve the right to delete comments that are offensive or off-topic.

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.