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.

Best Answer

  • Nick Korna
    Nick Korna ✭✭✭✭✭✭
    Answer ✓
    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:


Answers

  • Paul McGuinness
    Paul McGuinness ✭✭✭✭✭✭
    Options

    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 ✓
    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:


  • David G11
    Options

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

  • David G11
    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!

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!