IF statement with DATE

Options

I'm trying to update a function to account for 2021 but I want it to work for dates in the past going back to 2019 so I can use a column formula and also ideally for the future (2022 and beyond). The logic is this:

If Engaged is checked, then the SUBX date should be the month after the date engaged (i.e., if the date engaged is 10/2/20, the subx date should be 11/1/20). However, if the the "Date re-enrolled" exists, then the subx date should be based off of that (and not the "date enrolled." AND, anyone who engaged in 2019 (any month) the SUBX date should be 1/1/20

The formula works for some dates but not all and I'm not sure what I'm missing. I'm using this formula in the SUBX column for all dates:

=IF(Engaged@row = false, "", IF([Date Re-Enrolled]@row <> "", DATE(YEAR([Date Re-Enrolled]@row), MONTH([Date Re-Enrolled]@row) + 1, 1), IF(YEAR([Date Engaged]@row) = 2019, DATE(2020, 1, 1), IF(YEAR([Date Engaged]@row) = 2020, DATE(2021, 1, 1), IF(YEAR(DATE(YEAR([Date Engaged]@row), MONTH([Date Engaged]@row) + 1, 1)))))))

And using that, the date with green checkmark is right. But the others are wrong and I don't understand why. The Subx dates should be:

11/1/20

12/1/20 (based on date re-enrolled)

1/1/21

1/1/21 (based on date of re-enrolled)

1/1/20

HELP!

Best Answer

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    OK. I suspect the error occurs because of your month +1 formula. If the date is December it will error out. Try this:

    =IF(Engaged@row = false, " ", IF(ISDATE([Date Re-enrolled]@row), IF(YEAR([Date Re-enrolled]@row) = 2019, DATE(2020, 1, 1), IF(MONTH([Date Re-enrolled]@row) = 12, DATE(YEAR([Date Re-enrolled]@row + 1), 1, 1), DATE(YEAR([Date Re-enrolled]@row), MONTH([Date Re-enrolled]@row) + 1, 1))), IF(ISDATE([Date Engaged]@row), IF(YEAR([Date Engaged]@row) = 2019, DATE(2020, 1, 1), IF(MONTH([Date Engaged]@row) = 12, DATE(YEAR([Date Engaged]@row) + 1, 1, 1), DATE(YEAR([Date Engaged]@row), MONTH([Date Engaged]@row) + 1, 1))))))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

Answers

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    edited 12/13/20
    Options

    Hi @Jennifer Lenander ,

    Try this:

    =IF(Engaged@row = false, "", IF(ISDATE([Date Re-Enrolled]@row), IF(YEAR([Date Re-enrolled]@row)=2019, DATE(2020, 1, 1), DATE(YEAR([Date Re-Enrolled]@row), MONTH([Date Re-Enrolled]@row) + 1, 1), IF(ISDATE([Date Engaged]@row), IF(YEAR([Date Engaged]@row) = 2019, DATE(2020, 1, 1), IF(YEAR([Date Engaged]@row) = 2020, DATE(2021, 1, 1), IF(YEAR(DATE(YEAR([Date Engaged]@row), MONTH([Date Engaged]@row) + 1, 1), ""))))))

    If engaged is false it stops. If Date Re-Enrolled is a date then it uses that. If not it uses Date Engaged, if it is a date. If neither Date Re-enrolled or Date Engaged are dates it returns a blank.

    I didn't test this so I may have a parens off and I'm a typo magnet.

    Hope it works.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Jennifer Lenander
    Jennifer Lenander ✭✭✭✭✭
    Options

    I like your thinking. It didn't work but I'm going to play with what you have here. The "ISDATE" function is a good idea. Thanks. I'll keep at it!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    1 more try:

    =IF(Engaged@row = false, "", IF(ISDATE([Date Re-Enrolled]@row), IF(YEAR([Date Re-enrolled]@row)=2019, DATE(2020, 1, 1), DATE(YEAR([Date Re-Enrolled]@row), MONTH([Date Re-Enrolled]@row) + 1, 1), IF(ISDATE([Date Engaged]@row), IF(YEAR([Date Engaged]@row) = 2019, DATE(2020, 1, 1), IF(YEAR([Date Engaged]@row) = 2020, DATE(2021, 1, 1), DATE(YEAR([Date Engaged]@row), MONTH([Date Engaged]@row) + 1, 1), ""))))))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Answer ✓
    Options

    OK. I suspect the error occurs because of your month +1 formula. If the date is December it will error out. Try this:

    =IF(Engaged@row = false, " ", IF(ISDATE([Date Re-enrolled]@row), IF(YEAR([Date Re-enrolled]@row) = 2019, DATE(2020, 1, 1), IF(MONTH([Date Re-enrolled]@row) = 12, DATE(YEAR([Date Re-enrolled]@row + 1), 1, 1), DATE(YEAR([Date Re-enrolled]@row), MONTH([Date Re-enrolled]@row) + 1, 1))), IF(ISDATE([Date Engaged]@row), IF(YEAR([Date Engaged]@row) = 2019, DATE(2020, 1, 1), IF(MONTH([Date Engaged]@row) = 12, DATE(YEAR([Date Engaged]@row) + 1, 1, 1), DATE(YEAR([Date Engaged]@row), MONTH([Date Engaged]@row) + 1, 1))))))

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Jennifer Lenander
    Jennifer Lenander ✭✭✭✭✭
    Options

    This is fantastic. Thank you so much. This works!!!!!!

  • Mark Cronk
    Mark Cronk ✭✭✭✭✭✭
    Options

    Trial and error. Glad we were able to help. Thanks for using the Community.

    Mark


    I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.

  • Jennifer Lenander
    Jennifer Lenander ✭✭✭✭✭
    Options

    I found one error. There was a paragraph in a wrong place. While the formula worked (no errors), the result was impacted. Just in case anyone uses this or a version of it:


    =IF(Engaged@row = false, " ", IF(ISDATE([Date Re-Enrolled]@row), IF(YEAR([Date Re-Enrolled]@row) = 2019, DATE(2020, 1, 1), IF(MONTH([Date Re-Enrolled]@row) = 12, DATE(YEAR([Date Re-Enrolled]@row) + 1, 1, 1), DATE(YEAR([Date Re-Enrolled]@row), MONTH([Date Re-Enrolled]@row) + 1, 1))), IF(ISDATE([Date Engaged]@row), IF(YEAR([Date Engaged]@row) = 2019, DATE(2020, 1, 1), IF(MONTH([Date Engaged]@row) = 12, DATE(YEAR([Date Engaged]@row) + 1, 1, 1), DATE(YEAR([Date Engaged]@row), MONTH([Date Engaged]@row) + 1, 1))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!