find the years of serving and match back the person and display. -HOW to?

[bbsin]

hi 

I like to display if the person has been servicing

from 2 to 5  to display in the 5 year column

6 to 10 years to 10 year column and so on..  

can advise what is the formula and do advise better way to do it in excel?  Thank you

	Service Period till now
name			20 Year	10 Years	5 years
a	2 yrs 10 mths 8 days
b	8 yrs 0 mths 16 days
a	5 yrs 0 mths 8 days				5
b	8 yrs 0 mths 8 days
a	0 yrs 7 mths 4 days
b	0 yrs 5 mths 16 days

[#2]

@bbsin 

For 5 years

=IF(AND(LEFT(B3,1)*1>=2,LEFT(B3,1)*1<=5),A3,"")

For 10 years

=IF(AND(LEFT(B3,1)*1>=6,LEFT(B3,1)*1<=10),A3,"")

 For 20 years

=IF(LEFT(B3,1)*1>=11,A3,"")

[#3]

hi @Harun24HR,

Thank you this is what I need.

one more question, what if just want to find those in 5 yrs , 10 yrs and 20yrs, which part of the formula should be edit? Thanks

[#4]

@bbsin 

Then you need to edit TRUE part of IF() function. Replace A3 with your desired word like

=IF(AND(LEFT(B3,1)*1>=2,LEFT(B3,1)*1<=5),"5 Yrs","")