Date Function with literal date does not resolve True

[Cam]

I have a Data! sheet with a Cell C16 that holds a date (m/d/yy). On another
sheet I am trying to determine if the date on Data!C16 is >= May 1st 2003.
My IF statement looks like this.

=IF(Data!C16>=DATE(2003,5,1),SUM((D22*0.1)+(D23*0.1)+(D24*0.8)),"Do
something else")

It is important that I compare the date to 5/1/2003 (legislative reasons).
When I put 1/1/1989( m/d/yy) in Data!C16, the statement resolves TRUE!..It
should not.

I can put the static date (5/1/2003) in another cell with a leading
apostrophy and compare Data!C16 to that cell and it works, but I should not
have to.

What am I doing wrong?

Todd Canfield

 

[Frank Kabel]

Hi
how do your enter your date 1/1/1989 (also with a leading apostrophe?)

 

[Cam]

Frank,

No, the Data!C16 cell (1/1/89) does not have an apostrophe. That cells (and
many others) are populated with VB code from an Access Database.

Cam

 

[Dave R.]

Why not just use the number that Excel sees the date as?

5/1/03 is 37742, so try

=IF(Data!C16>=37742,SUM((D22*0.1)+(D23*0.1)+(D24*0.8)),"Do something else")

If that doesn't work, ensure the dates from the query are actually
recognized as dates by Excel, e.g. try changing their formatting. Or change
their format to numeric, and you should see numbers in the 30000s, if not
I'd check the setup of the table in Access.

 

[Cam]

37742 does not work either. From playing around with it more, it appears
the Data!C16 Cell is the culpret. When the data gets imported and
propagated throughout the workbook, the Data!C16 Cell has a date in it but
it is left justified (meaning I dont think its truly a date). When I type
in the cell a 'real' date (ie. 1/1/1989), the data becomes right justified
(like dates should be I think) and the formula I posted earlier works.

Its almost like the Data!C16 cell is not quite a date from the import. The
Access table field that the data is coming from is a Date/Time.

Anyone have any more ideas on why the cell is not quite a date cell until I
type over it? Thanks to all.

 

[Dave R.]

Hmm.. I routinely use queries that use dates from Access into Excel with no
problem. I would check the Access table and make sure the table itself is
storing the data as date/time and not just text. Is the data going anywhere
before it gets to Excel? My dates normally come in looking like:

1/30/2002 12:00:00 AM

...even though in the Access database they are only dates.

In the meantime, enter a 1 in any empty cell. Copy it, then goto your
Data!Cxx range, select the range of text dates, go to EDIT>Paste Special>
and select operation Multiply (or divide if the mood strikes you) then click
OK. This will convert the text dates to date values and using 37742 or even
the other way you looked up date, should work fine.

 

[Cam]

Thanks Dave, I got it now.

I know I posted this as an "Excel" problem, but I am actually using Formula1
(Most people have not heard of formua1 and the products are very similar in
many ways.

When I was importing to the date to the cell in the spreadsheet, I was using
the spreadsheet.TEXT property, which put it in as text. I changed the
property to spreadsheet.Entry and it worked. The ENTRY property sets the
text "in the same format as it would be displayed while in edit mode".

After you suggestions, I narrowed it down to the VB code that was putting
the date in the cell. Thanks for you help.