Status light, nested IF statement

Joanna Lupker

Hello everyone,

I'm looking to find some help with my status light formula.

My columns are as follows:

• Date Due (date column)

• Progress (drop down column with: "Not Started", "In Progress", "Complete" as options)

• Status (drop down symbol column with "Red", "Yellow", Green", "Gray" circles -- to become a formula column hopefully)

Here is the logic:

• If status@row is "Not Started", OR "In Progress", OR is blank, AND today is greater than date due , show "Red" (ie. if today is past the due date and NOT complete)

• If status@row is "complete" and any value for date due OR is blank , show "Green" (ie. if it's complete, no matter the date)

• If status@row is "In Progress" and date due is greater than today OR is blank, show "Yellow" (ie. if it's in progress and the date due has not occurred)

• If status@row is "Not Started" OR is blank and date is greater than today OR is blank, show "Gray" (ie. if no progress has been made yet and the date due has not occurred or has not been assigned)

As seen below, here is the formula I have so far:

=IF(Progress@row = "Complete", "Green", IF(AND(Progress@row = "In Progress", ISBLANK([Date Due]@row)), "Yellow", IF(AND(Progress@row = "Not Started", OR([Date Due]@row > TODAY(), ISBLANK([Date Due]@row))), "Gray")))

I've tried a few different things and this one in it's current state (at the very bottom) just appears blank. I suspect I have not captured the logic correctly or in the right order.

Any help would be so great!

Thanks :)

Paul Newcome

Try this one...

=IF(Progress@row = "Complete", "Green", IF(TODAY() > [Due Date]@row, "Red", IF(Progress@row = "In Progress", "Yellow", "Gray")))

Joanna Lupker

Thanks @Paul Newcome unfortunately though I'm getting an #unparseable message though.

Also, I do want to make sure I capture some of the instances when the date due field or the progress field are blank. For example , a gray status light with no Date Due assigned

Paul Newcome

My apologies. I used [Due Date] but just realized you used [Date Due]. Try updating the column reference to that to see how it works for you.

Joanna Lupker

No worries @Paul Newcome. Unfortunately it's the same thing. I'll keep working on it. Thanks for your time.

Paul Newcome

Can you copy/paste the exact formula you are getting the error on directly from your sheet to here or (preferably) provide a screenshot of the formula within the sheet similar to your above screenshot?

Aside from the [Due Date]/[Date Due] mix-up, the syntax of the formula should not be producing that particular error.

Joanna Lupker

Oh sorry about that @Paul Newcome I must've typed something wrong.

The formula is functional. It doesn't however capture the logic I've outlined. For instance in that last task ("Smartsheet community help" is where I've been testing the formula) the status should be red because the task is late, but it is yellow.

Again, here is the logic

• If status@row is "Not Started", OR "In Progress", OR is blank, AND today is greater than date due , show "Red" (ie. if today is past the due date and NOT complete)

• If status@row is "complete" and any value for date due OR is blank , show "Green" (ie. if it's complete, no matter the date)

• If status@row is "In Progress" and date due is greater than today OR is blank, show "Yellow" (ie. if it's in progress and the date due has not occurred)

• If status@row is "Not Started" OR is blank and date is greater than today OR is blank, show "Gray" (ie. if no progress has been made yet and the date due has not occurred or has not been assigned

Hope this makes sense.

But maybe I'm trying to do too much in one formula. If you have other suggestions, I'm open to it.

Thanks

Paul Newcome

That last task is not late until tomorrow. You have the date set for 24 March. So Yellow is correct.

Joanna Lupker

Hmm you're right...for some reason the date always changes back to the current date. I guess I'll have to investigate that before I can look at the formula again.

Joanna Lupker

@Paul Newcome I was able to fix the date column not to change :) But the formula still doesn't capture the logic regarding blank due date, ie. should be yellow if "in progress" but no "date due".

Anyway I knew this might be a bit of a tricky/ long if statement, so I'll keep trying different things.

Paul Newcome

What is it doing when [Date Due] is blank?