Automating RYG "Light Status" for Dates using IF AND Formulas

CarloV

Hi all,

I have several tasks that have two columns stating start and complete dates. I would simply want to show that the task status with the RYG symbols:

Red = Not Started, Not Complete

Yellow = Started, but not complete

Green = Complete

The formula works but is not perfect. It seems to 'break' by showing a blank value when I have a complete date listed but no start date. I can simply fix it by populating the start date with a dummy # but I would prefer to leave it blank to show that start date is unknown.

Here is the formula I am using (I've tried removing the 1st logical expression in the beginning but can't get it to work):

=IF(AND(ISDATE([Date Submitted]1), ISDATE([Date Completed]1)), "Yes", IF(AND(ISDATE([Date Submitted]1), ISBLANK([Date Completed]1)), "Hold", IF(AND(ISBLANK([Date Submitted]1), ISBLANK([Date Completed]1)), "No")))

Paul Newcome

I may be reading it wrong, but can't it be simplified to

=IF(ISDATE([Date Completed]1), "Yes", IF(ISDATE([Date Submitted]1), "Hold", "No"))

??

Mike Wilday

To work with a RAG column type you'd need to replace "Yes" with "Green", "Hold" with "Yellow" and "No" with "Red".

Did that work for you?

CarloV

Hi Mike - I prefer the Yes/Hold/No symbols (still resemble Red, Amber, Green). As you can see below, the formula works but I have blanks in my database.

Mike Wilday

Oh.... I see.

The formula above you listed isn't even looking at the start date. My question to you is one of the other dates that you are referencing missing from the equation on those lines? Date submitted or Date completed? Or is there a combination of those two fields that you aren't accounting for in your formula?

It looks like your missing if date submitted is blank and date completed is a date. Could that be the issue on that blank row?

CarloV

Ah, sorry for the confusion Mike... I am simply treating the Date Submitted as my Start Date, and Date completed as my Complete Date. No reference to other columns.

I believe my formula is not accounting for the scenario where I have the Date Completed column filled out, but not the Date Submitted column. I just don't know how to fix my current formula to account for it.

Mike Wilday

What symbol do you want to appear? Replace the bold yes with whatever symbol you want to appear.

=IF(AND(ISBLANK([Date Completed]1), ISDATE([Date Completed]1)), "Yes", (IF(AND(ISDATE([Date Submitted]1), ISDATE([Date Completed]1)), "Yes", IF(AND(ISDATE([Date Submitted]1), ISBLANK([Date Completed]1)), "Hold", IF(AND(ISBLANK([Date Submitted]1), ISBLANK([Date Completed]1)), "No"))))

Paul Newcome

I may be reading it wrong, but can't it be simplified to

=IF(ISDATE([Date Completed]1), "Yes", IF(ISDATE([Date Submitted]1), "Hold", "No"))

??

Mike Wilday

@Paul Newcome I think you're right. That would catch all of the requirements. I need to work on simplification. haha. Thanks.

Paul Newcome

@Mike Wilday No worries. Haha.

CarloV

Thanks @Paul Newcome ... simple always wins!