IF statement with DATE

Jennifer Lenander

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!

Mark Cronk

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

Mark Cronk

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

Jennifer Lenander

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

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

Mark Cronk

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

Jennifer Lenander

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

Mark Cronk

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

Mark

Jennifer Lenander

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