IF statement with DATE
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
-
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
-
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.
-
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!
-
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.
-
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.
-
This is fantastic. Thank you so much. This works!!!!!!
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!