Formula to Give Last Day of Current Quarter

NL5555

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.

MCorbin

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))