Help with an If / AND / OR Formula please
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'
 'Complete' when '% Complete' is = 100%
 'InProgress' when '% Complete' is greater than 0%, less than 100% and does not fall under the definition of 'OffTrack'
 'OffTrack' when '% Complete' is <70% and task is falling due in less than 14 days time
 'Delayed' when '% Complete' is not equal to 100% and 'Finish' date is in the past
 'Not Started' when '% Complete' is 0% and does not fall under the definition of 'OffTrack' 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.
Best Answer

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), "OffTrack", IF([% Complete]@row = 0, "Not Started", "In Progress"))))
Results/sample data:
Answers

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), "OffTrack", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress"))))
Hope that helps
Thanks
Paul

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), "OffTrack", IF([% Complete]@row = 0, "Not Started", "In Progress"))))
Results/sample data:

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

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), "OffTrack", IF([% Complete]@row = 0, "Not Started", IF([% Complete]@row = 1, "Complete", "In Progress"))))
Thank you!

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), "OffTrack", 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
Help Article Resources
Categories
Check out the Formula Handbook template!