Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# Calculating Service Award eligibility in Excel

Posted on 2011-03-10
Medium Priority
1,477 Views
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?

0
Question by:ShepVillage
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 7
• 7

LVL 22

Expert Comment

ID: 35098093
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

LVL 81

Expert Comment

ID: 35098094
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

ServiceAwardsQ26878130.xlsx
0

LVL 81

Expert Comment

ID: 35098118
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.

0

LVL 22

Expert Comment

ID: 35098153
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

LVL 22

Expert Comment

ID: 35098239
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

LVL 22

Expert Comment

ID: 35098245
Sorry...* year is oFF by 1/2 year...
0

LVL 22

Expert Comment

ID: 35098307
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

LVL 81

Expert Comment

ID: 35098374
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.

0

LVL 22

Expert Comment

ID: 35098473
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

LVL 81

Expert Comment

ID: 35098501
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".

0

LVL 81

Accepted Solution

byundt earned 1000 total points
ID: 35098563
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")

0

LVL 22

Assisted Solution

rspahitz earned 1000 total points
ID: 35098770
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

Author Closing Comment

ID: 35099088
Thanks - I've much by seeing your back and forths!

Regards, Alan
0

LVL 81

Expert Comment

ID: 35099093
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.
0

LVL 81

Expert Comment

ID: 35099446
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
If youâ€™ve ever visited a web page and noticed a cool font that you really liked the look of, but couldnâ€™t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yoâ€¦
###### Suggested Courses
Course of the Month9 days, 7 hours left to enroll