Formula to Give Last Day of Current Quarter

I want a simple formula that will take today's date and calculate the last day of the current Quarter. Meaning, if today is 12/15/20 then the formula will give "12/31/20" and if today's date is 1/15/21 then it will give "3/31/21". I used a super complicated if statement but I don't want to have to update this each year.
Answers
-
I use a helper sheet for this. It's got all sorts of date related columns in it, including quarter start and end dates
To get the last date of the quarter based on your comparison date (today?):
Once you have your helper sheet (and assuming you set up columns like mine), you can do an Index/Match to find the End Date based on a calculation that determines what Quarter Today() is in:
=INDEX({Qtr End}, MATCH(IF(MONTH(TODAY()) <= 3, "Q1", IF(MONTH(TODAY()) <= 6, "Q2", IF(MONTH(TODAY()) <= 9, "Q3", "Q4"))), {QTR #}, 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 67.8K Get Help
- 474 Global Discussions
- 205 Use Cases
- 516 Announcements
- 5.5K Ideas & Feature Requests
- 87 Brandfolder
- 157 Just for fun
- 83 Community Job Board
- 521 Show & Tell
- 36 Member Spotlight
- 3 SmartStories
- 309 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!