Be seen. Boost your questionâ€™s priority for more expert views and faster solutions

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

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?

=IF(MOD(YEAR(NOW())-YEAR(A

=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

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

=IF(AND(YEAR(NOW())-YEAR(A

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.

=IF(AND(NOW()>365,MOD(YEAR

and this:

=YEAR(NOW())-YEAR(A2+365.2

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

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

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

Here's another approach, using a cutoff of anything prior to July versus anything from July on:

=IF(YEAR(NOW())-YEAR(A2)<1

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)

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.

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.

Brad