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

CVMTFD

New Member
Joined
Mar 26, 2019
Messages
4
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?
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top