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.
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
The formula for a Feb quarter in cell C2 (the answer to the query) is
Can you guess the formula for cell D2, for a Jan Quarter?
Basically the month number of the first month quarter is the number you deduct the MONTH result from within the MOD function.