Review a Years of Service Column and populate another cell with specific anniversaries

[CVMTFD]

I have a Years of Service column calculating correctly, I also have another column that will populate each row with "10 YRS" "15 YRS" "20 YRS" and "25 YRS" if applicable by comparing 10years 0 months etc. A user wants the column to populate for the entire 12 months of the milestone anniversary year. So they want it to say "10 YRS" (or any true statement regardless of the number) even if the service column has a result of 10 years, 1 months. I have figured out that where I have 10years, 0months, I can continue with adding 1month, 2 months up to 11 months and it will work but the formula will be very long since I have to do it for 15, 20 and 25. Is there an easier way?

 

[DSCfromCFA]

How is the years/months data displayed?

If it is a decimal 10.1,10.2, 10.3 etc. then VLOOKUP with Approximate Match looking at a list of the cardinal service years (10,15,20 etc) will do the trick.

shane

 

[CVMTFD]

No, it's in xxyears,xxmonths display.

 

[DSCfromCFA]

Thanks. That makes it a little more difficult, but the following formula will work:

xxyears,xxmonths in column A
This formula in column B =VLOOKUP((LEFT(A1,2)&(MID(A1,9,2)))/100,$J$1:$J$3,1,TRUE)&" YRS" and dragged down
The cardinal years in column J - smallest to largest. Note: include 0 in this list if the years of service column includes years,months less than the first cardinal year.

Note this assumes that the single digit years and months have a leading zero. If that is not the case, then the above will not work as given.

shane

 

[CVMTFD]

Thanks, Shane. The single digits do not have a leading zero. This is the formula.
=DATEDIF(A6, $C$2, "y")&"years,"&DATEDIF(A6, $C$2, "ym")&"months"
Can you please tell me how to modify to produce either the decimal you first mentioned or the lead zeros? C2 is the current pay period and A6 is the first line of employees' hire date.

 

[DSCfromCFA]

G'day again,

Sorry for the delayed response. Adding leading zeros requires a lot of manipulation, far more than may capacity to do in a reasonable sized formula. However the YEARFRAC (year fraction) function can produce an elapsed time in years and points of years - but NOT in the tidy xxyears,xxmonths you currently use.

=YEARFRAC(A6,$C$2) will produce something like 10.25 for 10 years 3 months, and much longer decimal values as each day increments. However, you could leave your current formula for display purposes, and use the YEARFRAC formula for the Years of Service calculation via the VLOOKUP function formula given above.

Cheers

shane

 

[CVMTFD]

Thanks Shane. Appreciate the help.