Another status light formula question

Joanna Lupker

Hello all,

I'm having trouble with my status light formula.

I have the following columns:

Date Due

% Complete

Status (red/yellow/green/gray)

This is my current status formula:

=IF(OR(ISBLANK([% Complete]@row), ISBLANK([Date Due]@row)), "Gray", IF(AND([% Complete]@row < 1, TODAY() > [Date Due]@row), "Red", IF(AND([% Complete]@row > 0, TODAY() < [Date Due]@row), "Yellow", IF([% Complete]@row = 1, "Green", "Gray"))))

This is a logic break down and a chart of what's not working:

Here is a screenshot of my current sheet for a visual:

Any help anyone could give would be so appreciated.

Thanks!

Heather Duff

@Joanna Lupker I finally got a chance to mock it up rather than guessing. I think this may work:

=IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row < 1, [% Complete]@row > 0, ISBLANK([Date Due]@row)), "Yellow", IF(AND([% Complete]@row = 0, OR([Date Due]@row > TODAY(), ISBLANK([Date Due]@row))), "Gray", IF(AND([Date Due]@row < TODAY(), [% Complete]@row < 1, [% Complete]@row >= 0), "Red", IF(AND([% Complete]@row > 0, OR([Date Due]@row > TODAY(), ISBLANK([Date Due]@row))), "Yellow", "Error")))))

Don't ask me to translate what it means because I have no idea 😂 I just know it worked in every scenario I entered. Let me know if it works for you!

Heather Duff

Hi Joanna,

Nested IF statements go in order that they are listed. So if the formula returns the first criteria as true, it stops there. Let's try rearranging and see if it fixes the issues:

=IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row < 1, TODAY() > [Date Due]@row), "Red", IF(AND([% Complete]@row > 0, TODAY() < [Date Due]@row), "Yellow",IF(AND(isblank([% Complete]@row)),TODAY()<[Date due]@row),"Yellow",IF(OR(ISBLANK([% Complete]@row), ISBLANK([Date Due]@row)), "Gray", "Gray" )))))

Let me know if it works!

Best,

Heather

Joanna Lupker

Thanks Heather! I appreciate the reply. I just removed an extra bracket at the end :)

That formula though turns the status red if both the date due and % complete are blank (when is should be gray to indicate that its not started)

Also, when it should be yellow (to show task in progress--ie. date due is in the future and % complete between .01 - 99%) it appears as an incorrect argument.

Green works though! :)

Heather Duff

@Joanna Lupker

I was getting myself all tangled up, so here's my attempt at starting from scratch:

=if([% complete]@row=1,"Green",if(AND(OR([% complete]@row=0,ISBLANK([% complete]@row)),OR([date due]@row>today(),ISBLANK([date due]@row))),"Yellow",if(AND(OR([% complete]@row<1,ISBLANK([% complete]@row)),[date due]@row<today(),"Red","Grey")))

This translates to:

If % complete is 100, show green.

Otherwise, if % complete is either 0 or blank AND the date due is either greater than today or blank, show yellow.

Otherwise, if % complete is either blank or less than 100 AND the date due is less than today, show red.

Otherwise, show grey.

Let me know if this works for you.

Best,

Heather

Joanna Lupker

@Heather D

No worries!

So this is what I'm using my Status column to symbolize :

Red = late

yellow = in progress

green = done

gray = not started

So what I'm hoping for the status is this:

• If % complete is less than 100% OR is blank, AND today is greater than date due , show Red
• If % complete is 100% and any value for date due OR is blank , show Green
• If % complete is between 0.01% and 99.99% and date due is greater than today OR is blank, show Yellow
• Else show as Gray (ie. If 0% complete OR is blank and date is greater than today OR is blank)

This is basically because dates due are often unknown for my work.

Also, unfortunately I'm still getting an incorrect message when the % complete column is set to anything except for 100% (where the status will return green)

Heather Duff

@Joanna Lupker I finally got a chance to mock it up rather than guessing. I think this may work:

=IF([% Complete]@row = 1, "Green", IF(AND([% Complete]@row < 1, [% Complete]@row > 0, ISBLANK([Date Due]@row)), "Yellow", IF(AND([% Complete]@row = 0, OR([Date Due]@row > TODAY(), ISBLANK([Date Due]@row))), "Gray", IF(AND([Date Due]@row < TODAY(), [% Complete]@row < 1, [% Complete]@row >= 0), "Red", IF(AND([% Complete]@row > 0, OR([Date Due]@row > TODAY(), ISBLANK([Date Due]@row))), "Yellow", "Error")))))

Don't ask me to translate what it means because I have no idea 😂 I just know it worked in every scenario I entered. Let me know if it works for you!

Joanna Lupker

@Heather D you are an angel! Thank you so much!! This works amazingly.

Really appreciate your help on this one!😀

Heather Duff

@Joanna Lupker Wonderful! So glad we got it figured out. :)