How to write an If formula based on dates (or blanks) in 2 different columns
I’m adding a priority status for accounts based on the start date. However, I believe I may have too many factors to get a status and need your help. Originally, I had a priority status based on the date in Program Start Date. This was the formula I was using that worked great: =IF([Program Start Date]@row = "", "", IF([Program Start Date]@row < TODAY(), "Gray", IF([Program Start Date]@row <= TODAY(+14), "Red", IF([Program Start Date]@row <= TODAY(+28), "Yellow", IF([Program Start Date]@row > TODAY(+28), "Green"))))).
I now have to figure the dates in an additional column so I created setup case start date with a vlookup to pull in any dates that may or may not have been updated from another source. If there is a date in setup case start date , it should overrule the Program Start Date or the blank in the Program Start Date. If setup case start date = “#NO MATCH” or is blank, and there is a program start date, the program start date remains the date. If neither has a date, then the status should be blank. I used this formula, but it doesn’t work for all scenarios. =IF([setup case start date]@row < TODAY(), "Gray", IF([setup case start date]@row <= TODAY(+14), "Red", IF([setup case start date]@row <= TODAY(+28), "Yellow", IF([setup case start date]@row > TODAY(+28), "Green", IF([Program Start Date]@row < TODAY(), "Gray", IF([Program Start Date]@row <= TODAY(+14), "Red", IF([Program Start Date]@row <= TODAY(+28), "Yellow", IF([Program Start Date]@row > TODAY(+28), "Green", IF([Program Start Date]@row = "", "", IF([setup case start date]@row = "#NO MATCH", ""))))))))))
Here is an example of where it works and doesn’t work.
Is it possible to write a formula based on so many different factors? Thank you as always for your help.
Best Answer
-
It looks like your newest formula should be working. is it not?
My suggestion was referring to your first formula.
=IF([Program Start Date]@row = "", "", IF([Program Start Date]@row < TODAY(), "Gray", IF([Program Start Date]@row <= TODAY(+14), "Red", IF([Program Start Date]@row <= TODAY(+28), "Yellow", IF([Program Start Date]@row > TODAY(+28), "Green")))))
What I was suggesting was that you basically replace "[Program Start Date]@row" with an IF statement that will basically output the [setup case start date]@row if it is not blank and the [Program Start Date]@row otherwise.
=IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) = "", "", IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) < TODAY(), "Gray", IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) <= TODAY(+14), "Red", IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) <= TODAY(+28), "Yellow", IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) > TODAY(+28), "Green")))))
Answers
-
I would start by using an IFERROR to replace the #NO MATCH with a blank.
Then you would take your existing formula and replace all [Program Start Date]@row references with IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row).
For example...
=IF([Program Start Date]@row = "", "", ...................
changes to
=IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) = "", "", ...................
-
Thank you Paul for the help on changing the #NO MATCH to blank. That makes a big difference in what I'm presenting. This is what I changed it to so it reflects blanks:
=IF([Program Start Date]@row = "", "", IF([setup case start date]@row = "", "", IF([setup case start date]@row < TODAY(), "Gray", IF([setup case start date]@row <= TODAY(+14), "Red", IF([setup case start date]@row <= TODAY(+28), "Yellow", IF([setup case start date]@row > TODAY(+28), "Green", IF([Program Start Date]@row < TODAY(), "Gray", IF([Program Start Date]@row <= TODAY(+14), "Red", IF([Program Start Date]@row <= TODAY(+28), "Yellow", IF([Program Start Date]@row > TODAY(+28), "Green")))))))))).
With the above, when there is a date in each, it changes it correctly and provides the status that reflects the setup case start date. But when there is a blank in either the setup case start date or the program start date, the status is blank.
Unfortunately, I'm still confused on how to change the above formula. I am unclear on your instructions "replace all [Program Start Date]@row references with IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row)".
I appreciate your help.
.
-
It looks like your newest formula should be working. is it not?
My suggestion was referring to your first formula.
=IF([Program Start Date]@row = "", "", IF([Program Start Date]@row < TODAY(), "Gray", IF([Program Start Date]@row <= TODAY(+14), "Red", IF([Program Start Date]@row <= TODAY(+28), "Yellow", IF([Program Start Date]@row > TODAY(+28), "Green")))))
What I was suggesting was that you basically replace "[Program Start Date]@row" with an IF statement that will basically output the [setup case start date]@row if it is not blank and the [Program Start Date]@row otherwise.
=IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) = "", "", IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) < TODAY(), "Gray", IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) <= TODAY(+14), "Red", IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) <= TODAY(+28), "Yellow", IF(IF([setup case start date]@row <> "", [setup case start date]@row, [Program Start Date]@row) > TODAY(+28), "Green")))))
-
Thank you! It works perfectly!
-
Happy to help. 👍️
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
- 146 Just for fun
- 63 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!