# Help with an If / AND / OR Formula please

Options

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.

• ✭✭✭✭✭✭
Options

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:

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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:

• Options

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

• Options

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!

Options

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!