ShepVillage
asked on
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?
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?
I put the end of the eligibility date in cell F1. I then copied down the following formula:
=IF(ISNA(MATCH(DATEDIF(A2,
Note that you show 8/3/0/2005 as NO, but I believe the correct answer is 5
Brad
ServiceAwardsQ26878130.xlsx
=IF(ISNA(MATCH(DATEDIF(A2,
Note that you show 8/3/0/2005 as NO, but I believe the correct answer is 5
Brad
ServiceAwardsQ26878130.xlsx
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
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
good point, byundt...
=IF(AND(YEAR(NOW())-YEAR(A
=IF(AND(YEAR(NOW())-YEAR(A
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.
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.
Sorry...* year is oFF by 1/2 year...
For more precision, this might work better:
=IF(AND(NOW()>365,MOD(YEAR
and this:
=YEAR(NOW())-YEAR(A2+365.2
=IF(AND(NOW()>365,MOD(YEAR
and this:
=YEAR(NOW())-YEAR(A2+365.2
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
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
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
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.
=IF(AND(NOW()-YEAR(A25+365
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.
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks - I've much by seeing your back and forths!
Regards, Alan
Regards, Alan
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
With the above tweak, your formula agrees with mine in all of my test cases.
Brad
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
With the above tweak, your formula agrees with mine in all of my test cases.
Brad
Shorter version of rspahitz' formula:
=IF(OR((NOW()-A2)<370,MOD(
=IF(OR((NOW()-A2)<370,MOD(
=IF(MOD(YEAR(NOW())-YEAR(A