IF Statement formula

louisaIjoma

Can anyone help me with IF statement please for the following. This is my first post, so would love if anyone could assist.

I am trying to generate a schedule health column, I need a formula to bring through the following)

IF status is "complete" turn “Green”

IF status is "Not started" and the "end date" is within 3 days turn “red” otherwise “blue”

IF status is "In Progress" and the end date is within 3 days and % complete is less than 75% turn "red" otherwise turn “yellow”

Any help would be greatly appreciated.

Genevieve P.

Hi @louisaIjoma

Welcome to the Smartsheet Community! I'd be happy to help you with this formula.

Nested IF statements can be tricky, so I'll outline how we can build each of your statements and criteria by breaking out your phrases above and showing the corresponding formula structure.

The order of how we list the statements is important, because as soon as a Nested IF finds one correct statement it will stop reading the rest of the formula. This means if you put that IF Status = "Not Started" turn "Blue" before you say IF Status = "Not Started" and the End Date is within 3 days, you will only ever see Blue regardless of the End Date.

We can combine rules if they have the same output (ex. you have two scenarios that say "Red", so we can put them together).

Green

IF status is "complete" turn “Green”

=IF(Status@row = "Complete", "Green",

IF status is "Not started" and the "end date" is within 3 days turn “red”

IF status is "In Progress" and the end date is within 3 days and % complete is less than 75% turn "red"

IF(OR(

AND(Status@row = "Not Started", [End Date]@row <= TODAY(3)),

AND(Status@row = "In Progress", [End Date]@row <= TODAY(3), [% Complete]@row < 0.75)), "Red",

Blue

IF status is "Not started" and the "end date" is within 3 days turn “red” otherwise “blue”

IF(Status@row = "Not Started", "Blue",

Yellow

IF status is "In Progress" and the end date is within 3 days and % complete is less than 75% turn "red" otherwise turn “yellow”

IF(Status@row = "In Progress", "Yellow"

Then we can add all these statements together and close off the formula!

FULL FORMULA

=IF(Status@row = "Complete", "Green", IF(OR(AND(Status@row = "Not Started", [End Date]@row <= TODAY(3)), AND(Status@row = "In Progress", [End Date]@row <= TODAY(3), [% Complete]@row < 0.75)), "Red", IF(Status@row = "Not Started", "Blue", IF(Status@row = "In Progress", "Yellow"))))

Keep in mind if there is a row where none of these criteria are met, the cell will simply be blank. Let me know if this works for you!

Cheers,

Genevieve

louisaIjoma

OMG thank you so much @Genevieve P. that worked! and the explanation was FANTASTIC!

There is just one extra thing, if the status is 'Not started' and there is no start or end date in place? (meaning those fields are blank) where would I put the below into the formula? Or is the below incorrect ?

IF(ISBLANK([Start Date]@row), "Blue"

Genevieve P.

I'm so glad I could help!

Ah, good catch. A blank date will be seen as "in the past" so you'll get "Red" if it's blank instead of Blue.

Lets actually move our "Not Started" statement earlier then, and say if either the Start or End dates are blank, return blue.

My preferred way to check for blanks is to see if it = ""

IF(OR(AND(Status@row = "Not Started", [Start Date]@row = ""), AND(Status@row = "Not Started", [End Date]@row = "")), "Blue"

Full Formula

=IF(Status@row = "Complete", "Green", IF(OR(AND(Status@row = "Not Started", [Start Date]@row = ""), AND(Status@row = "Not Started", [End Date]@row = "")), "Blue", IF(OR(AND(Status@row = "Not Started", [End Date]@row <= TODAY(3)), AND(Status@row = "In Progress", [End Date]@row <= TODAY(3), [% Complete]@row < 0.75)), "Red", IF(Status@row = "In Progress", "Yellow"))))

Does that return the correct status ball for each row?

louisaIjoma

@Genevieve P. hey

Yes you are correct it turns RED instead of blue.

With the new formula it turns BLUE now YAY!, however the rows that have the status of Not started but have dates in it is showing no colour.

Not too sure if im explaining that correctly.

Genevieve P.

Yes, great explanation!

Let's add back in our previous statement for Blue after the Red statement so it makes sure to check the dates, first. This means we'll need an extra closing parentheses at the end.

=IF(Status@row = "Complete", "Green", IF(OR(AND(Status@row = "Not Started", [Start Date]@row = ""), AND(Status@row = "Not Started", [End Date]@row = "")), "Blue", IF(OR(AND(Status@row = "Not Started", [End Date]@row <= TODAY(3)), AND(Status@row = "In Progress", [End Date]@row <= TODAY(3), [% Complete]@row < 0.75)), "Red", IF(Status@row = "Not Started", "Blue", IF(Status@row = "In Progress", "Yellow")))))