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.
