Help with an If / AND / OR Formula please

David G11

Hi all,

I am looking for some help with an If / AND / OR Formula please.

My issue is as follows:

I am looking for a formulate to automatically populate a 'Status' column based on '% Complete' and 'Finish' column(date column).

I would like the formula to display the following outcomes for each task's 'Status'

1. 'Complete' when '% Complete' is = 100%
2. 'In-Progress' when '% Complete' is greater than 0%, less than 100% and does not fall under the definition of 'Off-Track'
3. 'Off-Track' when '% Complete' is <70% and task is falling due in less than 14 days time
4. 'Delayed' when '% Complete' is not equal to 100% and 'Finish' date is in the past
5. 'Not Started' when '% Complete' is 0% and does not fall under the definition of 'Off-Track' or 'Delayed'

The current formula I have in place is working for 1,2,5 above:

=IF([% Complete]8 = 0, "Not Started", IF([% Complete]8 = 1, "Complete", "In Progress"))

Any help would be greatly appreciated.

Thanks all.

Nick Korna

Hi @David G11 ,

This formula should work:

=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row < 1, (Finish@row - TODAY()) < 0), "Delayed", IF(AND([% Complete]@row < 0.7, (Finish@row - TODAY()) < 14), "Off-Track", IF([% Complete]@row = 0, "Not Started", "In Progress"))))

Results/sample data:

Paul McGuinness

HI @David G11

This formula should do the trick, tested as below and working.

Formula is -

=IF(AND([% Complete]@row < 1, Finish@row < TODAY()), "Delayed", IF(AND([% Complete]@row < 0.7, Finish@row < TODAY() + 14), "Off-Track", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress"))))

Hope that helps

Thanks

Paul

Nick Korna

Hi @David G11 ,

This formula should work:

=IF([% Complete]@row = 1, "Complete", IF(AND([% Complete]@row < 1, (Finish@row - TODAY()) < 0), "Delayed", IF(AND([% Complete]@row < 0.7, (Finish@row - TODAY()) < 14), "Off-Track", IF([% Complete]@row = 0, "Not Started", "In Progress"))))

Results/sample data:

David G11

@Nick Korna @Paul McGuinness - thank you both so much for the help !!

David G11

Hi All,

Could you please help me as to what this formula would look like if wanted to edit out "Delayed" as an option? Have tried a few times, but getting an error message.

=IF(AND([% Complete]@row < 1, Finish@row < TODAY()), "Delayed", IF(AND([% Complete]@row < 0.7, Finish@row < TODAY() + 14), "Off-Track", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress"))))

Thank you!

Genevieve P.

Hi @David G11

You should be able to simply remove out the first part of the formula and then remove the additional closing parentheses at the end! Try this:

=IF(AND([% Complete]@row < 0.7, Finish@row < TODAY() + 14), "Off-Track", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress")))

If this hasn't helped, it would be useful to see a screen capture of the formula in your sheet and an explanation of what you want the formula to do.

Thanks!

Genevieve