Using RAGB plus adding Blank Cell action

I am trying to use the RAGB indicators to look at if a Due date is set and then setting the Indicator to Red if in the past, Yellow if in the next 3 days and Green if in the future beyond 3 days. We are also using the Blue to indicate if the task is complete. This works fine with the following formula, however for blank Due Dates it shows Yellow

=IF([Task Completed]1 = "Yes", "Blue", IF(TODAY() - [Due Date]1 > 0, "Red", IF([Due Date]1 - TODAY() < 3, "Yellow", "Green")))

What I would like to also do is look to see if the Due Date is not Set and Return a Value of Not Set if the cell is blank and keep the above indicators when they meet the criteria.

Can anyone advise as how to do this?

Find more posts tagged with

Formulas

Comments

Mike Zeinstra

Have you investigated the use of the ISBLANK formula? What I've used is if the cell value = 0 "Yellow" (or similar color code)

Chris Winward

I have tried adding the ISBLANK to the formula however I cannot get it to work as it does not seem to like the 5th Element in the formula. The first 4 being the RAGB indicators which I need to show. I think it maybe just my lack of knowledge of the formulas

J. Craig Williams

Chris,

Try this:

=IF([Task Completed]1 = "Yes", "Blue", IF(ISBLANK([Due Date]1), "Not Set", IF(TODAY() > [Due Date]1, "Red", IF([Due Date]1 - TODAY() < 3, "Yellow", "Green"))))

Note that your use of "< 3" for yellow results in a yellow for 12/11, 12/12/, 12/13 but not not 12/14.

For some people, 12/14 is within 3 days.

(Not implying this is wrong, just a statement)

Hope this helps.

Craig

Chris Winward

Craig

That works perfectly, thank you so much for taking the time to look at this for us. Very much appreciated.