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
Check out the Formula Handbook template!