Logical Function & Dates Formula

[itjr212]

Hello All,

I am trying to create a formula that says if an associate is hired before a certain date.

Ex:
less than 8/31/09, then 1
in between 9/1/09 and 10/31/09, then 2
in between 11/1/09 and 12/31/09, then 3
greater than 12/31/09, then 4

I used the formula
=IF(D2<8/31/2009,"1",IF(AND(D2<10/31/2009,D2>9/1/2009),"2",IF(AND(D2<12/31/2009,D2>11/1/2009),"",IF(D2>12/31/2009,"4",""))))

but this isn't working.

Here is a sample below:
Column d
<TABLE style="WIDTH: 149pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=198 border=0 x:str><COLGROUP><COL style="WIDTH: 88pt; mso-width-source: userset; mso-width-alt: 4278" width=117><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 88pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=117 height=17>Hire Date</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 61pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=81>#</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="38063">3/17/2004</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40190">1/12/2010</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="40070">9/14/2009</TD><TD class=xl27 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl28 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" align=right height=18 x:num="40121">11/4/2009</TD><TD class=xl29 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

Is there a way to create an If,Then formula for dates?

Thank you

itjr212

 

[Scott Huish]

=if(a2="","",match(a2,{0,40057,40118,40178},1))

 

[Scott Huish]

or if your dates will change build a table, with the first value being 0, then the next three dates like I did in Column J, then refer to that in the formula:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Hire Date	#								0
2	3/17/2004	1								9/1/2009
3	1/12/2010	4								11/1/2009
4	9/14/2009	2								12/31/2009
5	11/4/2009	3
Sheet1

 

[itjr212]

Thank you so much! Both Formulas worked. The main thing I had to do was to change the dates on my formula to text.

Thanks again!