# IF and AND Functions

1. ## IF and AND Functions

Hi

I need help with a IF(AND Formula to Group Seniority together?

Length of service is = 7 years, 10 months

Seniority Group
less than 1 year = <1
More than 1 year to equal to 3 years = 1-3
More than 3 years to equal to 5 years = 3-5
More than 5 years to equal to 10 years = 5-10
More than 10 years = >10

=IF(N2<1,"<1",IF(AND(N2>1,N2<=3),"1-3",IF(AND(N2>3,N2<=5),"3-5",IF(AND(N2>5,N2<=10),IF(AND(N2>10),">10")))))
(the result came back us "false" - not sure why? )

Thanks
S  Register To Reply

2. ## Re: IF and AND Functions

Try it like this:

=IF(N2<1,"<1",IF(N2<=3,"1-3",IF(N2<=5,"3-5",IF(N2<=10,"5-10",">10"))))

This assumes that N2 contains a number, but if it contains 7 years, 10 months then you have text values and not numeric, so you will need a different approach.

Hope this helps.

Pete  Register To Reply

3. ## Re: IF and AND Functions

Do you know what would be the correct formula, yes it is a text not numeric. thanks  Register To Reply

4. ## Re: IF and AND Functions

You need to give some further examples of your data, so we can see the variations that can occur. For example, if the length of service is 9 months, would this be shown as 0 years, 9 months or as just 9 months, and would 1 year and 2 months be shown as 1 year, 2 months or as 1 years, 2 months?

Attach a sample Excel workbook showing some more examples, as described in the yellow banner at the top of the screen.

Pete  Register To Reply Originally Posted by Pete_UK You need to give some further examples of your data, so we can see the variations that can occur. For example, if the length of service is 9 months, would this be shown as 0 years, 9 months or as just 9 months, and would 1 year and 2 months be shown as 1 year, 2 months or as 1 years, 2 months?

Attach a sample Excel workbook showing some more examples, as described in the yellow banner at the top of the screen.

Pete  Register To Reply

6. ## Re: IF and AND Functions

you have a date of hire and a month column, you are using a datedif formula in col D, why don't you use that for your seniority column like so...
=IF(DATEDIF(C2,B2,"Y")<1,"<1",IF(DATEDIF(C2,B2,"Y")<=3,"1-3",IF(DATEDIF(C2,B2,"Y")<=5,"3-5",IF(DATEDIF(C2,B2,"Y")<=10,"5-10",">10"))))  Register To Reply

7. ## Re: IF and AND Functions

Another way:

=LOOKUP(DATEDIF(C2,B2,"m"),{0,12,37,61,121},{"<1","1-3","3-5","5-10",">10"})  Register To Reply

8. ## Re: IF and AND Functions

Thank you so muhc. This works perfect :-)  Register To Reply

9. ## Re: IF and AND Functions

Your welcome AND thank you for the rep!   Register To Reply