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
- Smartsheet Customer Resources
- 63.4K Get Help
- 394 Global Discussions
- 213 Industry Talk
- 449 Announcements
- 4.6K Ideas & Feature Requests
- 141 Brandfolder
- 132 Just for fun
- 131 Community Job Board
- 453 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!