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'

  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.

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    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), "Off-Track", IF([% Complete]@row = 0, "Not Started", "In Progress"))))

    Results/sample data:


Answers

  • Paul McGuinness
    Paul McGuinness Overachievers

    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
    Nick Korna ✭✭✭✭✭✭
    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), "Off-Track", 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), "Off-Track", 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), "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

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!