Stuck on a formula involving dates

Hello formula gods, I need your help once again. :-)

I'm trying to write a formula that looks at 3 different date columns.

1.  Look at the 1. actual completion date, if not blank then make it green,  if it is blank then look at the revised due date column, if not blank then red for past due or yellow for reminder,  if 2. revised due date is blank then look at the 3. original due date column, and red for past due or yellow for reminder.

I have the formula written to look at the actual completion date and the original due date but I don't know how to work in the revised due date if that field isn't blank.


=IF(NOT(ISBLANK([Actual Completion Date]45)), "Green", IF(AND([Original Due Date]45 >= TODAY()), "Yellow", IF([Original Due Date]45 < TODAY(), "Red", IF(ISBLANK([Original Due Date]45, 0)))))

Any help is greatly appreciated!




Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!