why does =date always return the 1900-Jan-0

S

shazmar

Hi,

I'm trying to understand why this occurs. I have read info regarding
serial dates, how excel stores dates etc but can't find an answer to
this. It has cropped up while using criteria in advanced filters - I
would have expected to be able to filter on =date. I can get round it
by using two date criteria i.e. >=date and <=date but it just seems odd
that use of =date returns 00/01/00.

If I change to 1904 date system it becomes 01/01/04

Any ideas?

Thanks
 
B

Bernie Deitrick

Try using

=DATE(year,month,day)

or referring to a cell with the date of interest.

HTH,
Bernie
MS Excel MVP
 
S

shazmar

Thanks - yes both of those are fine.

I'm still at a loss as to why entering =somedate (i.e. =07/10/06)
returns the first system date. I've tried it in Excel 2000, 2002 and
2003. With filtering using =somedate as a criteria seems such a logical
request?
 
R

Ron Rosenfeld

Hi,

I'm trying to understand why this occurs. I have read info regarding
serial dates, how excel stores dates etc but can't find an answer to
this. It has cropped up while using criteria in advanced filters - I
would have expected to be able to filter on =date. I can get round it
by using two date criteria i.e. >=date and <=date but it just seems odd
that use of =date returns 00/01/00.

If I change to 1904 date system it becomes 01/01/04

Any ideas?

Thanks
Click to expand...

Excel stores dates as serial numbers You must have the Name "date" defined as
equal to zero. Otherwise you would get a #NAME! error. Day 0 is defined as
you note above in the two different systems.

If you are not using the word date, but rather referring to some cell in which
you have stored a date, then the contents of that cell are evaluating to zero.
In other words, it may look like a date, but so far as Excel is concerned, it
is not a date.
--ron
 
B

Bernie Deitrick

Gillian,

Yes, it is very logical to expect that, but whoever accused Excel of being _completely_ logical?

Entering =7/10/6 means to Excel "divide 7 by 10 and then that number by 6. ( That's what the equal
sign does.) Just entering 7/10/6 in a cell causes Excel to treat it as a date, but entering 33/42/6
in a cell gets treated as a string, since there is a "date entry interpretation engine", for lack of
a better term. In filters, you need to be a bit more precise about what you mean, since there is no
"engine" like in cell entry.

HTH,
Bernie
MS Excel MVP
 
S

shazmar

Thanks for the replies - got it!

Bernie said:
Gillian,

Yes, it is very logical to expect that, but whoever accused Excel of being _completely_ logical?

Entering =7/10/6 means to Excel "divide 7 by 10 and then that number by 6. ( That's what the equal
sign does.) Just entering 7/10/6 in a cell causes Excel to treat it as a date, but entering 33/42/6
in a cell gets treated as a string, since there is a "date entry interpretation engine", for lack of
a better term. In filters, you need to be a bit more precise about what you mean, since there is no
"engine" like in cell entry.

HTH,
Bernie
MS Excel MVP
Click to expand...
 
Top