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

Options
Theresa Bruns
Theresa Bruns ✭✭
edited 06/14/22 in Formulas and Functions

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    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
    Options

    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
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓
    Options

    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
    Options

    Thank you! It works perfectly!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!