Solved

Calculating Service Award eligibility in Excel

Posted on 2011-03-10
15
1,246 Views
Last Modified: 2012-06-21
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
Comment
Question by:ShepVillage
  • 7
  • 7
15 Comments
 
LVL 22

Expert Comment

by:rspahitz
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

by:byundt
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

Brad
ServiceAwardsQ26878130.xlsx
0
 
LVL 81

Expert Comment

by:byundt
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.

Brad
0
 
LVL 22

Expert Comment

by:rspahitz
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

by:rspahitz
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

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

Expert Comment

by:rspahitz
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 81

Expert Comment

by:byundt
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.

Brad
0
 
LVL 22

Expert Comment

by:rspahitz
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

by:byundt
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".

Brad
0
 
LVL 81

Accepted Solution

by:
byundt earned 250 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")

Brad
0
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 250 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

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

Regards, Alan
0
 
LVL 81

Expert Comment

by:byundt
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.
Brad
0
 
LVL 81

Expert Comment

by:byundt
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
The new Microsoft OS looks great, is easier than ever to upgrade to, it is even free.  So what's the catch?  If you don't change the privacy settings, Microsoft will, in accordance with the (EULA) you clicked okay to without reading, collect all the…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now