# IF OR THEN Formulas

Options
✭✭✭✭

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!

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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))))

• ✭✭✭✭
Options

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))), ""))

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 05/05/20
Options

@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.

• ✭✭✭✭
Options

@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

• ✭✭✭✭✭✭
Options

@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.

• ✭✭✭✭
Options

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?

• ✭✭✭✭✭✭
Options

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?

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

@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. 👨‍🔬

• ✭✭✭✭
Options

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

• ✭✭✭✭
Options

@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!!!

• ✭✭✭✭✭✭
Options

Hahaha. I'm happy to help! 👍️

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!