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

Question

hi&nbsp;&nbsp;I like to display if the person has been servicingfrom 2 to 5&nbsp; to display in the 5 year column6 to 10 years to 10 year column and so on..&nbsp;&nbsp;&nbsp;can advise what is the formula and do advise better way to do it in excel?&nbsp; Thank you&nbsp;&nbsp;Service Period till now&nbsp;&nbsp;&nbsp;&nbsp;name&nbsp;&nbsp;20 Year10 Years5 yearsa2 yrs 10 mths 8 days&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;b8 yrs 0 mths 16 days&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;a5 yrs 0 mths 8 days&nbsp;&nbsp;&nbsp;&nbsp;5b8 yrs 0 mths 8 days&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;a0 yrs 7 mths 4 days&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;b0 yrs 5 mths 16 days&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

harun24hr · Answer

bbsin&nbsp;&nbsp;For 5 years=IF(AND(LEFT(B3,1)*1&gt;=2,LEFT(B3,1)*1&lt;=5),A3,"")For 10 years=IF(AND(LEFT(B3,1)*1&gt;=6,LEFT(B3,1)*1&lt;=10),A3,"")&nbsp;For 20 years=IF(LEFT(B3,1)*1&gt;=11,A3,"")&nbsp;&nbsp;

bbsin · Answer

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

harun24hr · Answer

bbsin&nbsp;Then you need to edit TRUE part of IF() function. Replace A3 with your desired word like&nbsp;=IF(AND(LEFT(B3,1)*1&gt;=2,LEFT(B3,1)*1&lt;=5),"5 Yrs","")&nbsp;&nbsp;

Forum Discussion

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

3 Replies

Resources

	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