Aug 09 2022 02:16 AM
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 |
Aug 09 2022 02:32 AM
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,"")
Aug 09 2022 05:27 PM
Aug 09 2022 07:03 PM - edited Aug 09 2022 07:05 PM
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","")