IF(OR(AND formula

Khari Shiver
Khari Shiver ✭✭✭✭✭
edited 10/13/23 in Formulas and Functions

Hello,

I am trying to identify instances where a date has a year of 2023 and a month that is not in Q1. When I find one, I want to subtract that date from another and divide it by 365. I cannot figure out the mistake I'm making in the formula. Below is what I have and I'm getting an invalid data type error.

=IF(OR(AND(YEAR([Last Hire Date]@row) = "2023", MONTH([Last Hire Date]@row) <> "1"), AND(YEAR([Last Hire Date]@row) = "2023", MONTH([Last Hire Date]@row) <> "2"), AND(YEAR([Last Hire Date]@row) = "2023", MONTH([Last Hire Date]@row) <> "3")), ([Proration Date Reference]@row - [Last Hire Date]@row) / 365, "1")

The weird thing is I was able to get it to work correctly by searching for the YEAR = 2023 and the MONTH = 4, YEAR = 2023 and the MONTH = 5 etc but it's a longer string. I should be able to rule out what I'm not looking for as it's a shorter list.

Thank you for your help.

Best Answer

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!