Check specific date

[yippie_ky_yay]

Hello all,

This seems simple but I can't seem to get the result. All I need to do is check a specific column (in this case, J) for the date, and if that date is greater then April 30, 2002 THEN show whatever is in the corresponding row in column K (otherwise show nothing).

I know I must be close with:
=IF(J7>4/30/2002,K7,"")

I guess my problem is comparing dates!

Thanks in advance,

-Sean

 

[maxflia10]

Sean,

The formula works for me. What kind of error message are you getting?

 

[ThePencilQueen]

Try using 37376 instead of the date format. (If you format your data as a number this is what 30th Apr 2002 is equal to (the number of days since 1st Jan 1900))

 

[yippie_ky_yay]

Thank you both for answering my post! Using the numeric value (37376) as ThePencilQueen suggested did the trick and everything works great!

Thanks again,
-Sean

 

[maxflia10]

Sean

What date system have you checked off in Tools/Options?

 

[Mark W.]

If you'll use the following your formula will be more "readable"...

=IF(J7>"4/30/2002"+0,K7,"")

 

[yippie_ky_yay]

Neat trick Mark - thanks!

maxflia10 - in case you're still interested - I'm using '97 and under tools options the only reference to a date system that I can see is a check box for "1904 date system" which I currently do not have selected (nor understand - I'll look it up later when I have more time).

Thanks again everyone!
-Sean

 

[Mark W.]

Not only is...

=IF(J7>"4/30/2002"+0,K7,"")

...more readable, but it also makes your workbook more portable because the datevalue above is calculated "on the fly". The 1904 date system is the default on Macintoshes. Windows defaults to 1900 (unchecked). When using the 1904 date system January 1, 1904 is assigned the datevalue, 1. The datevalue, 1, is ordinarily assigned to January 1, 1900 on a Windows PC. So, if you hard code a datevalue in a formula and move that workbook to a system using another date system your formulas will be off by 4 years. For more on date systems see the Excel Help topic for "Tips on entering dates and times" and "How Microsoft Excel stores dates and times".
This message was edited by Mark W. on 2002-10-30 11:18