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

Frequent Contributor

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 Year10 Years5 years
a2 yrs 10 mths 8 days     
b8 yrs 0 mths 16 days     
a5 yrs 0 mths 8 days    5
b8 yrs 0 mths 8 days     
a0 yrs 7 mths 4 days     
b0 yrs 5 mths 16 days     
3 Replies

@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,"")

Harun24HR_0-1660037518179.png

 

 


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

@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","")