IF AND Formula Workaround

terrencekm

Hi All

I am trying to get a formula working whereby if

1. END DATE is in the next 3 days
2. END DATE is not blank
3. Status is either (In Progress, Not Started)
4. At Risk flag will be indicated (, 1)

Many thanks

Paul Newcome

Give this a try...

 

=IF(AND(Status@row <> "Complete", [End Date]@row >= TODAY(-3)), 1)

L_123

=IF(AND([End Date]@row <= TODAY() + 3, NOT(ISBLANK([End Date]@row)), OR(Status@row = "In Progress", Status@row = "Not Started")), 1, 0)

 

Broken down:

=IF(

3 Criteria AND statement

AND(

First criteria end date is within next three days or in the past

[End Date]@row <= TODAY() + 3,

Second criteria end date is not blank

NOT(ISBLANK([End Date]@row)),

Third criteria: opens or statement that returns a true if status is in progress or not started

OR(

Status@row = "In Progress",

Status@row = "Not Started")

),

Returns 1 if criteria is met, 0 if not.

1, 0)