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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 449 Show & Tell
 30 Member Spotlight
 1 SmartStories
 305 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!