Formula

RA

Hello there,

I'm new in SS. I'm trying to create a formula for the status column.

I have the following status:

On track

At Risk

Late

Not Started

=IF([Start Date]1 > TODAY(), "Not Started", IF([End Date]1 > TODAY(), "On Track", IF([End Date]1 = TODAY(), "At Risk", IF(AND([% Complete]1 = 1, [End Date]1 < TODAY()), "Complete", "Late"))))

I would like to have the below rules, but for some reason I don't get that all of them works.

I would appreciate is someone can give me an idea.

Thanks!

JamesB

@RA

Need to change the Late in the ordering.

=if([% Complete]@row=1,"Complete",if([End Date]@row<Today(),"Late",if(AND([Start Date]@row>TODAY(),[% Complete]@row=0),"Not Started",if([End Date]@row>Today(5),"On Track",if([End Date]@row<=TODAY(5),"At Risk")))))

JamesB

@RA

=if([% Complete]@row=1,"Complete",if(AND([Start Date]@row>TODAY(),[% Complete]@row=0),"Not Started",if([End Date]@row<=TODAY(5),"At Risk",if([End Date]@row<Today(),"Late",if([End Date]@row>Today(),"On Track")))))

RA

Hello @JamesB,

Thank you so much for your prompt response. For some reason it doesn't apply for Late. Please see example below:

The status should be "late", since the end date is past due and the % complete is <100%

Thanks.

JamesB

@RA

Let's break down the argument order, we may need to re-order it.

=if([% Complete]@row=1,"Complete",

if(AND([Start Date]@row>TODAY(),[% Complete]@row=0),"Not Started",

if([End Date]@row<=TODAY(5),"At Risk",

if([End Date]@row<Today(),"Late"

if([End Date]@row>Today(),"On Track")))))

In your Example above, your end date is less than or equal today plus 5, and the argument order it is ending at the 3rd IF statement, because that is true. So we need to check the On Track before the at Risk and we need to add 5 to the On Track statement.

Try this...

=if([% Complete]@row=1,"Complete",if(AND([Start Date]@row>TODAY(),[% Complete]@row=0),"Not Started",if([End Date]@row>Today(5),"On Track",if([End Date]@row<=TODAY(5),"At Risk",if([End Date]@row<Today(),"Late")))))

RA

Hi @JamesB I really appreciate your time. But unfortunately it still not working :-(

See examples below:

I'll keep trying to figure out what we are missing.

JamesB

@RA

Need to change the Late in the ordering.

=if([% Complete]@row=1,"Complete",if([End Date]@row<Today(),"Late",if(AND([Start Date]@row>TODAY(),[% Complete]@row=0),"Not Started",if([End Date]@row>Today(5),"On Track",if([End Date]@row<=TODAY(5),"At Risk")))))

RA

@JamesB you are AWESOME!! It's working perfectly now.

Thank you so much for your time :-)