Formula to return value in another cell based on several criteria

n7teixeira

I am trying to create a formula in column 1st yr (105 hours max) that will return the value in the Vacation Balance column ONLY IF the Years of Service is <= 1 years 11 months 31 days, AND IF the Country is USA OR PRI.

I am using the formula below but it is returning vacation balances whose Years of Service is greater than 1 years 11 months 31 days. Any ideas what formula I can use?

=IF(OR([Years of Service]@row <= "1years 11 months 31 days", Country@row = "USA", Country@row = "PRI"), [Vacation Balance]@row)

AravindGP

Hi @n7teixeira,

You will need to convert the text in Years of Service to an absolute number to do a less than formula. i.e., 1 year should be written as 365 (an absolute number). If you're looking for an exact match, having it as a text will work. For example, if you are looking for exactly 1years 11 months 30 days AND country being USA or PRI, you can use this formula

=IF(AND([Years of Service]@row = "1years 11 months 31 days", OR(Country@row = "USA", Country@row = "PRI")), [Variation Balance]@row, "")

The formula will update the value in Variation Balance when the years of service is an exact text match with Country being USA OR PRI and return a blank value if the conditions fail.

n7teixeira

thank you @AravindGP , can you clarify what you mean by converting the text in Years of Service to an absolute number? I do need the Years of Service column to show all values in # years # months # days format.

The formula I am trying to create needs to return the Vacation Balance for any cell whose Years of Service is under 2 years (I may have complicated it with using 1years 11 months 31 days).. does that make sense?