Calculating Service Award eligibility in Excel

Here's one for the Excel gurus out there ;-)

Our company runs an annual service awards ceremony in June based on their hire date for staff who fit the following condition:
They reach either 5, 10, 15, 20, 25, 30, 35, 40, 45 or 50 years of service ONLY IF they reach it from July 1st of the previous year to June 30th of the current year.

If hire date is in column A, I would like a formula in column B that would only show the correct milestone reached for those that meet the condition, else place the text "NO".

Examples:
Assume Eligibility Begin Date in cell A1 (This year it will be 7/1/2010 (July 1st, 2010)
Assume Eligibility End Date in cell A2 (This year it will be 6/30/2010 (June 30th, 2011)

Hiredate (MM/DD/YYYY),Milestone
7/1/2010      << Eligible Begin Date
6/30/2011      << Eligible End Date

Results desired:

HIRE DATE,Milestone
4/24/2006,5
11/30/1991,NO
6/16/2006      ,5
11/3/2003      ,NO
1/8/2001,10
5/14/1996,15
8/30/2005      ,NO
11/14/2006,NO
11/14/2006,NO
10/15/1991,NO
4/8/1991,20
10/31/2006,NO
5/8/2006,5

Make sense?




ShepVillageAsked:
Who is Participating?
 
byundtConnect With a Mentor Commented:
rspahitz,
When you revised your last formula before posting, you missed updating a couple of cell addresses. I believe you meant to refer to cell A2:
=IF(AND(NOW()-YEAR(A2+365.25/2+1)>365,MOD(YEAR(NOW())-YEAR(A2+365.25/2+1),5)=0),YEAR(NOW())-YEAR(A2+365.25/2+1),"NO")

Brad
0
 
rspahitzCommented:
I came up with this formula for cell B2 that works on everything except 8/30/05:

=IF(MOD(YEAR(NOW())-YEAR(A2+365/2),5)=0,YEAR(NOW())-YEAR(A2+365/2),"NO")

0
 
byundtCommented:
I put the end of the eligibility date in cell F1. I then copied down the following formula:
=IF(ISNA(MATCH(DATEDIF(A2,F$1,"y"),{5,10,15,20,25,30,35,40,45,50},0)),"NO",DATEDIF(A2,F$1,"y"))

Note that you show 8/3/0/2005 as NO, but I believe the correct answer is 5

Brad
ServiceAwardsQ26878130.xlsx
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
byundtCommented:
ShepVillage,
Although you did not include a hire in the current year in your sample data, you will probably need to test that possibility. My formula responds "NO" in such situations.

Brad
0
 
rspahitzCommented:
good point, byundt...

=IF(AND(YEAR(NOW())-YEAR(A16+365/2)>1,MOD(YEAR(NOW())-YEAR(A16+365/2),5)=0),YEAR(NOW())-YEAR(A16+365/2),
0
 
rspahitzCommented:
Mine works on the premise that the eligibility year is odd by 1/2 year so I offset it and then check.  It's possible that it will fail on boundaries due to leap years, especially for the older employees.  This should probably be checked.

A simple version of my formula, to determine how many years the person has is this:

=YEAR(NOW())-YEAR(A5+365/2)
then it's easy enough to create another formula to see if it's a multiple of 5 great than 0.
0
 
rspahitzCommented:
Sorry...* year is oFF by 1/2 year...
0
 
rspahitzCommented:
For more precision, this might work better:

=IF(AND(NOW()>365,MOD(YEAR(NOW())-YEAR(A2+365.25/2+1),5)=0),YEAR(NOW())-YEAR(A2+365.25/2+1),"NO")

and this:

=YEAR(NOW())-YEAR(A2+365.25/2+1)
0
 
byundtCommented:
rspahitz,
Is there a typo in your last post? I'm not understanding why you compare NOW() to 365. It will always be larger than 365, since it has a current value of 40612.

Brad
0
 
rspahitzCommented:
You're right...I fixed it in a different formula and pasted the wrong one.  It should have been the year -minus the service start date:

=IF(AND(NOW()-YEAR(A25+365.25/2+1)>365,MOD(YEAR(NOW())-YEAR(A5+365.25/2+1),5)=0),YEAR(NOW())-YEAR(A5+365.25/2+1),"NO")

this would definitely justify having an interim cell for calculating the actual number of years that they employ has been working, then check for 5-year marks, which I guess it closer to the approach you took.
0
 
byundtCommented:
ShepVillage,
Do be sure to test the suggested formulas with dates on the eligibility boundaries, such as 6/30/06 and 7/1/05. Both of these ought to be eligible for the 5 year award.

Likewise, 6/30/05 and 7/1/06 ought to be "NO".

Brad
0
 
rspahitzConnect With a Mentor Commented:
Yes, byundt.

Here's another approach, using a cutoff of anything prior to July versus anything from July on:

=IF(YEAR(NOW())-YEAR(A2)<1,"NO",   IF(MOD(YEAR(NOW())-YEAR(A2)-IF(MONTH(A2)<7,0,1),5)=0,   YEAR(NOW())-YEAR(A2)-IF(MONTH(A2)<7,0,1),   "NO"))

Basically, first check to make sure you have at least one year in which case just show NO; otherwise check if the number of years is divisible by 5; if so, show that number otherwise show NO.
To check for the number of years, subtract service date's year from the current year and subtract another 1 if month is from July to Dec (<7 subtract 0 otherwise subtract 1)
0
 
ShepVillageAuthor Commented:
Thanks - I've much by seeing your back and forths!

Regards, Alan
0
 
byundtCommented:
rspahitz,
You still have an issue with dates in the last half of the previous year, such as 8/30/2010. Your latest formula returns 0. The fix is:
=IF(YEAR(NOW())-YEAR(A2)<2,"NO",   IF(MOD(YEAR(NOW())-YEAR(A2)-IF(MONTH(A2)<7,0,1),5)=0,   YEAR(NOW())-YEAR(A2)-IF(MONTH(A2)<7,0,1),   "NO"))

With the above tweak, your formula agrees with mine in all of my test cases.
Brad
0
 
byundtCommented:
Shorter version of rspahitz' formula:
=IF(OR((NOW()-A2)<370,MOD(YEAR(NOW())-YEAR(A2)-(MONTH(A2)>6),5)<>0),"NO",YEAR(NOW())-YEAR(A2)-(MONTH(A2)>6))
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.