Conditional Result based on Anniversary Date
 

I have a list of employee anniversary dates:
6/18/2003
1/28/1991
5/9/2005
12/14/2007
10/18/2006
4/1/2008
11/16/2004
Based on this list, if the anniversary month and day is reached I need 40
(hours) to be returned, otherwise 0 (hours). I dont care about the year, but
I need to keep the dates in this format.

Conditional Result based on Anniversary Date
 

GregFIJI wrote:
I have a list of employee anniversary dates:
6/18/2003
1/28/1991
5/9/2005
12/14/2007
10/18/2006
4/1/2008
11/16/2004
Based on this list, if the anniversary month and day is reached I need 40
(hours) to be returned, otherwise 0 (hours). I dont care about the year, but
I need to keep the dates in this format.


=IF(OR(MONTH(TODAY())MONTH(A1),AND(MONTH(TODAY()) =MONTH(A1),DAY(TODAY())=DAY(A1))),40,0)

Conditional Result based on Anniversary Date
 

That gets me alot closer but...
It still returns 40 on any hire date in 2008. None of the 2008 dates have
hit their anniversary yet!

"Glenn" wrote:

GregFIJI wrote:
I have a list of employee anniversary dates:
6/18/2003
1/28/1991
5/9/2005
12/14/2007
10/18/2006
4/1/2008
11/16/2004
Based on this list, if the anniversary month and day is reached I need 40
(hours) to be returned, otherwise 0 (hours). I dont care about the year, but
I need to keep the dates in this format.


=IF(OR(MONTH(TODAY())MONTH(A1),AND(MONTH(TODAY()) =MONTH(A1),DAY(TODAY())=DAY(A1))),40,0)

Conditional Result based on Anniversary Date
 

Sorry...should have been more specific about the "I dont care about the year"
part...I only want 40 (hours) to return regardless of the amount of years
from the hire date to today.

"GregFIJI" wrote:

I have a list of employee anniversary dates:
6/18/2003
1/28/1991
5/9/2005
12/14/2007
10/18/2006
4/1/2008
11/16/2004
Based on this list, if the anniversary month and day is reached I need 40
(hours) to be returned, otherwise 0 (hours). I dont care about the year, but
I need to keep the dates in this format.

Conditional Result based on Anniversary Date
 

I edited the formula from Glenn to hardcode the 2008 issue...can someone help
me get rid of the hardcoding?
=IF(YEAR(C2)=2008,"0",IF(OR(MONTH(TODAY())MONTH(C 2),AND(MONTH(TODAY())=MONTH(C2),DAY(TODAY())=DAY( C2))),40,0))

"GregFIJI" wrote:

Sorry...should have been more specific about the "I dont care about the year"
part...I only want 40 (hours) to return regardless of the amount of years
from the hire date to today.

"GregFIJI" wrote:

I have a list of employee anniversary dates:
6/18/2003
1/28/1991
5/9/2005
12/14/2007
10/18/2006
4/1/2008
11/16/2004
Based on this list, if the anniversary month and day is reached I need 40
(hours) to be returned, otherwise 0 (hours). I dont care about the year, but
I need to keep the dates in this format.

Conditional Result based on Anniversary Date
 

GregFIJI wrote:
I edited the formula from Glenn to hardcode the 2008 issue...can someone help
me get rid of the hardcoding?
=IF(YEAR(C2)=2008,"0",IF(OR(MONTH(TODAY())MONTH(C 2),AND(MONTH(TODAY())=MONTH(C2),DAY(TODAY())=DAY( C2))),40,0))

"GregFIJI" wrote:

Sorry...should have been more specific about the "I dont care about the year"
part...I only want 40 (hours) to return regardless of the amount of years
from the hire date to today.

"GregFIJI" wrote:

I have a list of employee anniversary dates:
6/18/2003
1/28/1991
5/9/2005
12/14/2007
10/18/2006
4/1/2008
11/16/2004
Based on this list, if the anniversary month and day is reached I need 40
(hours) to be returned, otherwise 0 (hours). I dont care about the year, but
I need to keep the dates in this format.

Maybe this?

=IF(YEAR(C2)=YEAR(TODAY())...