IF statement to update RAG status

RobynTocco

Hi there, 

I would like to update my RAG Status icons (Green, Red, Yellow, and Grey balls) using a formula based on Due Date, Revised Due Date, and Submission Date inputs. My formula is as follows, and currently generates a #INVALID error message:

=IF([Submission Date]1 > 0, "Green", IF([Revised Due Date]1 - [Due Date]1 > 0, "Gray", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Not Started")))))

Any ideas on what I am doing wrong? My Due Date, Revised Due Date, and Submission Date columns are all in Date format. My statement also seems to work fine when I remove the first Submission Date part of the formula. 

What I want it to do is go green if there is a submission date (regardless of whether the item was late or not), go gray if there is a revised due date and no submission date (regardless of whether the item was late or not), go yellow if it is due today and no revised due date or submission date has been populated, go red if the due date was in the past and no revised due date or submission date has been populated, and say "Not Started" if the due date is in the future and a revised / submission date has not been populated.

Thanks!

 

L_123

you can't compare a number to a day without separating the date (In your current formula you do this in the first 2 if statements). Here is an updated version of your formula that works, though you will need to edit it to make it do what you want

=IF(ISDATE([Submission Date]1), "Green", IF(NETDAYS([Revised Due Date]1, [Due Date]1) > 0, "Gray", IF([Due Date]1 < TODAY(), "Red", IF([Due Date]1 = TODAY(), "Yellow", IF([Due Date]1 > TODAY(), "Not Started")))))

RobynTocco

thanks Luke_TK. It is going green now when I have a submission date, but is giving me a #invalid error message rather than defaulting to the false part of my first IF statement... How do you get the formula to default to the rest of the IF statement?

L_123

I just tested it and the if(isdate() defintely pushes to false on a blank cell. I think it is probably a typo or error in the next if statement that is being caught and pushing the error. can you post your updated formula?

RobynTocco

Ah got it sorted! Thanks for the help!