Searching for an end of quarter date formula

Tori Heath

Hi Smartsheet Community,

I'm trying to set up a "reminder date" column that will automatically update as time goes by and will trigger reminder emails when the dates in that column are reached. Specifically, I'm having trouble finding a formula to generate dates tied to end of quarters. For example, "30 days after the end of the quarter" - in this case I'd like something that looks at what today's date is and can determine what the end of quarter date is and then add 30.

Cody Holmes

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!

Tori Heath

That is exactly it! Perfect. Thank you Cody, we really appreciate it.

P. Martin

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)

GEJames

https://community.smartsheet.com/discussion/comment/288280#Comment_288280

There is a slight error here, on line 9 the number should be "9" not "6"