How to write an If formula based on dates (or blanks) in 2 different columns

Theresa Bruns

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.

Paul Newcome

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

Paul Newcome

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) = "", "", ...................

Theresa Bruns

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.

.

Paul Newcome

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

Theresa Bruns

Thank you! It works perfectly!

Paul Newcome

Happy to help. 👍️