Solved

Calculating Service Award eligibility in Excel

Posted on 2011-03-10
15
1,347 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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
 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup" or a blinking cursor with black screen. A loop for Auto repair will start but fix nothing.  You will be panic as there are no back…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

679 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