Multiple Date Ranges, If Then

This formula works. My question is - if I want to add date ranges for 2021, 2022, 2023, etc., what is the syntax to add the other date ranges onto the end of this formula?

=IF([Actual Finish]@row >= DATE(2020, 6, 30), [Actual Finish]@row <= DATE(2021, 7, 1), "2020")

Thank you!

Answers

  • Devin Lee
    Devin Lee ✭✭✭✭✭

    You can try something like this.

    =IF(MONTH([Actual Finish]@row) <= 6, YEAR([Actual Finish]@row), YEAR([Actual Finish]@row) + 1)

  • Sandra Unger
    Sandra Unger ✭✭✭
    edited 03/13/23

    Hmmm Devin, I'm not sure that's what I'm looking for or maybe I just need to think about this a different way.

    Perhaps I can describe it more clearly.

    If the "Actual Finish" date is between July 1, 2020 and June 30, 2021, I want it to put "2021" in the column titled "Completion Fiscal Year".

    If the "Actual Finish" date is between July 1, 2021 and June 30, 2022, I want it to put "2022" in the column.

    I can get it to work for one date range (1 year) but I want to add more date ranges for subsequent Fiscal Years.

  • Devin, I see what you are suggesting now. That is so much simpler. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!