Solved

Calculating Service Award eligibility in Excel

Posted on 2011-03-10
15
1,213 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 80

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 80

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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 80

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 80

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 80

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 80

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 80

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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

706 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

12 Experts available now in Live!

Get 1:1 Help Now