IF stmt and date comparison ...challenge

[WOWO10]

I am using a specified date in a cell (C3) and comparing it to set dates to deterimine which rate table to use. However, I am not able to get C3 to be read as a date and compared as a date.
I have tried formating the cell as a date

any tips?


=IF($C3<&quot;#7/1/2002#&quot;,VLOOKUP($H3,SFY2002_Rates,4,FALSE),IF($C3<&quot;#6/30/2003#&quot;, VLOOKUP($H3,SFY2003_Rates,4,FALSE),&quot;CHECK WARRANT DATE&quot)

 

[RamziSaab]

=IF($C1<A1,&quot;it is&quot;,&quot;its not&quot

just make a new column and put the dates u want to compare...it will work then, and u can change the date

 

[Zathras]

The use of # as a date delimiter is an Access thing, not for Excel.

This is untested, but if you change the date references from &quot;#7/1/2002#&quot; to DateValue(&quot;7/1/2002&quot it should work:

=IF($C3<DateValue(&quot;7/1/2002&quot;),VLOOKUP($H3,SFY2002_Rates,4,FALSE),IF($C3<DateValue(&quot;6/30/2003&quot;), VLOOKUP($H3,SFY2003_Rates,4,FALSE),&quot;CHECK WARRANT DATE&quot;))

A much better approach would be to use the suggestion by RamziSaab and put the dates in cells and then use cell references. Something like this:
[blue]

A1: 7/1/2002
A2: 6/30/2003
D3: =IF($C3<A1,VLOOKUP($H3,SFY2002_Rates,4,FALSE),IF($C3<A2, VLOOKUP($H3,SFY2003_Rates,4,FALSE),&quot;CHECK WARRANT DATE&quot;))

[/color]