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

Frequent Contributor



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



For 5 years


For 10 years


 For 20 years





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


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