Searching for an end of quarter date formula

Hey Tori,

So your business requirement, I hate to tell you, is going to be a complex answer. But I think I can help 😎. So basically the crux of your issue demands on you being able to generate a DYNAMIC quarter date. Because once you get that, you can easily generate the 60 days after and one week before, etc. dates.

So rather than post a hundred formulas for you, I wanted to post the formula to generate the dynamic quarter date, which will serve as your frame of reference for all other quarter-based calculations. I have posted a picture with the formula formatted (because we all know how unruly reading formulas can be), but I am going to paste it below so that you can copy-paste it too.

=IF(MONTH(TODAY()) <= 3, DATE(YEAR(TODAY()), 3, 31), IF(MONTH(TODAY()) <= 6, DATE(YEAR(TODAY()), 6, 30), IF(MONTH(TODAY()) <= 6, DATE(YEAR(TODAY()), 9, 30), IF(MONTH(TODAY()) <= 6, DATE(YEAR(TODAY()), 12, 31)))))

Necessary caveats:

There are some necessary assumptions with my formula:

1. Quarters are conventions and different organizations have different conventions. I work in the public sector and our fiscal year begins in October, for example. My formula assumes quarters based on just the calendar year. You may need to change the months and days being fed into the date function to generate the quarter-based anchor.
2. The formula above and pictured is an end-of-the-quarter date, hence the 30s and 31s being piped in as the day portion in the DATE function. To change them to the beginning, simply change them to 1s and add one to your month portion of the date function. For example DATE(2021, 3, 31) is the end of the quarter, and DATE(2021, 4, 1) is the beginning.
3. If you wanted to do, say, one week before the end of the quarter, all you would really need to do is add the -7 at the end of the DATE functions in my formula.
4. To explain the nested IF functions for you: Basically, they look at today's date to determine what month you are currently in and then determine where the quarter-anchored date should be. This is also an assumed condition and may need to change based on your organization's understanding of a quarter. But the logic, I hope, should be self-evident.

I know this is a little complex and highly technical but if you need more explanation, don't hesitate to reach out!

This was very useful.

I was able to use this formula in another formula to track the occurrences of certain phrases over the current quarter made during each month in that quarter.

At the top it calculates out the months in the current 3 month period:

=IF(MONTH(TODAY()) <= 3, 1, IF(MONTH(TODAY()) <= 6, 4, IF(MONTH(TODAY()) <= 9, 7, IF(MONTH(TODAY()) <= 12, 10))))

Across the top I changed the number for where I want it to fall in the current 3 month period.

Then I used this number to count the occurrences of the phrases on another sheet for the remaining rows:

=COUNTIFS({Sheet Reference Column}, CONTAINS($[Column]@row, @cell), {Sheet Reference Fiscal Year}, YEAR(TODAY()), {Sheet Reference Month}, $[1st]$1)