| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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())... |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Conditional Formatting Function for Anniversary Dates | Excel Worksheet Functions | |||
| Conditional Result based on Multiple Columns | Excel Worksheet Functions | |||
| Conditional Format based on forumula result | Excel Discussion (Misc queries) | |||
| Result Based on Date Criteria | Excel Discussion (Misc queries) | |||
| conditional formatting based on another cells formula result | Excel Discussion (Misc queries) | |||
Linear Mode
