Formula to Give Last Day of Current Quarter

Options

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

  • MCorbin
    MCorbin Overachievers Alumni
    Options

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!