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".
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)
7/1/2010 << Eligible Begin Date
6/30/2011 << Eligible End Date