Solved

# Calculating Service Award eligibility in Excel

Posted on 2011-03-10

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?