IF OR THEN Formulas

Kris Peeters
Kris Peeters ✭✭✭✭

I'm having a hard time with adding a formula for the attached sheet. The formula should do the following: IF "Position Classification" = A-Adjunct THEN "Completion Date" is 5 years from "Date of Hire or Change" AND IF "Position Classification" = F-Faculty THEN "Completion Date" is 3 years from "Date of Hire or Change"

The formula would change the "Complete Date" automatically when the "Date of Hire or Change" is changed.

Thanks so much to anyone that can help me out!

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    You will need to make sure that the text matches exactly.

    If you have "A - Adjunct" in the cell, but "A-Adjunct" in the formula, it will leave a blank.

    It also looks in your screenshot like the [Position Classification] column is a multi-select dropdown. Are there other selections in the same cell?

  • Jon Baier
    Jon Baier ✭✭✭✭
    Answer βœ“

    @Kris Peeters Which formula did you use? Try Paul's first (he's a SS wizard 😎 )

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try something like this...

    =IF([Position Classification]@row = "A-Adjunct", DATE(YEAR([Date of Hire or Change]@row) + 5, MONTH([Date of Hire or Change]@row), DAY([Date of Hire or Change]@row)), IF([Position Classification]@row = "F-Faculty", DATE(YEAR([Date of Hire or Change]@row) + 3, MONTH([Date of Hire or Change]@row), DAY([Date of Hire or Change]@row))))

  • Jon Baier
    Jon Baier ✭✭✭✭

    Try this in the Completion Date column:

    =IF([Position Classification]@row = "A-Adjunct", (DATE(YEAR([Date of Hire or Change]@row) + 5, MONTH([Date of Hire or Change]@row), DAY([Date of Hire or Change]@row))), IF([Position Classification]@row = "F-Faculty", (DATE(YEAR([Date of Hire or Change]@row) + 3, MONTH([Date of Hire or Change]@row), DAY([Date of Hire or Change]@row))), ""))

  • Jon Baier
    Jon Baier ✭✭✭✭

    @Paul Newcome looks like we came up with the same formula a few minutes apart :-)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    edited 05/05/20

    @Jon Baier It looks like you may actually have one too many closing parenthesis after each of your DATE functions though. You are closing out the IF statements too early as a result.


    EDIT: Nope. I misread yours. I missed the opening parenthesis before the DATE functions.

  • Jon Baier
    Jon Baier ✭✭✭✭

    @Paul Newcome I ran into the DATE function not working right if I didn't have the it as (Date(.....)) in another sheet. Probably something else goofy there. But the above does seem to work


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jon Baier Yeah. That was my mistake. I edited the original comment. Sorry about that.

    I have never run into it needing the extra set of parenthesis like that, but I have run into other "oddities" when it comes to dates.

  • Kris Peeters
    Kris Peeters ✭✭✭✭

    I really appreciate all this help and believe I'm closer to a solution but when I add the formula to the Completion Date column, I get a blank cell. Please forgive as I'm fairly new to Smartsheets and probably doing something easy, incorrectly....help?

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer βœ“

    You will need to make sure that the text matches exactly.

    If you have "A - Adjunct" in the cell, but "A-Adjunct" in the formula, it will leave a blank.

    It also looks in your screenshot like the [Position Classification] column is a multi-select dropdown. Are there other selections in the same cell?

  • Jon Baier
    Jon Baier ✭✭✭✭
    Answer βœ“

    @Kris Peeters Which formula did you use? Try Paul's first (he's a SS wizard 😎 )

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @Jon Baier It seems as if it was a spacing issue. We initially used "A-Adjunct" when it needed to be "A - Adjunct".


    Haha. Wizard is a new one for me, but I have been called the "Mad Scientist" once or twice. πŸ‘¨β€πŸ”¬

  • Kris Peeters
    Kris Peeters ✭✭✭✭

    @Paul Newcome Yes, I have several options in my Position Classification column but the date only is contingent upon those two things.

  • Kris Peeters
    Kris Peeters ✭✭✭✭

    @Paul Newcome Thanks for all you help and it's true what @Jon Baier said, you are a WIZARD!!! I made a separate column for my additional position classes and only use Adjunct and Faculty in the one. This is going to be an amazing help. Thank you, thank you, thank you!!!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Hahaha. I'm happy to help! πŸ‘οΈ

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!