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!
