IF OR THEN Formulas

Kris Peeters · 05/05/20

Uploaded May 5, 2020141.77 KB

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!

Paul Newcome · 05/05/20

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?

Paul Newcome · 05/05/20

I took another look at the screenshot, and it looks like the spacing may be the issue.

Try this one with the spacing corrected (I originally pulled the text from your post instead of your screenshot).

=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 · 05/05/20

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

Paul Newcome · 05/05/20

@Kris Peeters The above will only work for those cells that have ONLY one or the other option by themselves. If they are part of multiple options selected, that too will cause the formula to return a blank. If you need to adjust the date if one of those two is selected along with (an)other option(s), we would need to work in a CONTAINS function like so:

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

NOTE: If BOTH are selected, 5 years will be populated due to the order of the IF statements.

Paul Newcome · 05/05/20

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 · 05/05/20

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 · 05/05/20

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

Paul Newcome · 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 · 05/05/20

@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 · 05/05/20

@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 · 05/05/20

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 · 05/05/20

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?

Paul Newcome · 05/05/20

I took another look at the screenshot, and it looks like the spacing may be the issue.

Try this one with the spacing corrected (I originally pulled the text from your post instead of your screenshot).

=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 · 05/05/20

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

Paul Newcome · 05/05/20

@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 · 05/05/20

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

Paul Newcome · 05/05/20

@Kris Peeters The above will only work for those cells that have ONLY one or the other option by themselves. If they are part of multiple options selected, that too will cause the formula to return a blank. If you need to adjust the date if one of those two is selected along with (an)other option(s), we would need to work in a CONTAINS function like so:

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

NOTE: If BOTH are selected, 5 years will be populated due to the order of the IF statements.

Kris Peeters · 05/06/20

@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 · 05/06/20

Hahaha. I'm happy to help! 👍️

Formulas and Functions

Formulas and Functions

IF OR THEN Formulas

Best Answers

Answers

Help Article Resources

Quick Links

Categories

Trending in Formulas and Functions

Formula - use % complete to determine whether in progress

Formula error

TRYING TO FIND THE ERROR IN THIS FORMULA