IF(OR(AND formula

Khari Shiver

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.

Paul Newcome

You can also shorten it up a bit like so:

=IF(AND(YEAR([Last Hire Date]@row) = 2023, MONTH([Last Hire Date]@row)>= 4), value_if_true, value_if_false)

Paul Newcome

Try removing the quotes from around the numbers.

Paul Newcome

You can also shorten it up a bit like so:

=IF(AND(YEAR([Last Hire Date]@row) = 2023, MONTH([Last Hire Date]@row)>= 4), value_if_true, value_if_false)

Khari Shiver

I used the shortened. I'm not sure why the ">=" idea never occurred to me. Thank you for an extra set of eyes and your approach!

Paul Newcome

Happy to help. 👍️