Weird dates appear in cells when I use Date format! Why?

L

lizzie

when I change the formatting of a group of cells to a Date format and hit the
Enter key, some unknown random date appears in all the cells. Even when I go
into the cell and change the date, it doesn't change it to the correct date.
Why might this be happening?
 
D

Dave O

Will you please post an example of what is entered in the cell, how the
cell is formatted, and what appears in the cell when you press Enter?
 
D

Dave Peterson

What's in one of the cells that gets that funny value?

If you format the cell as General, what do you see in the formula bar?
 
D

David Biddulph

lizzie said:
when I change the formatting of a group of cells to a Date format and hit
the
Enter key, some unknown random date appears in all the cells. Even when I
go
into the cell and change the date, it doesn't change it to the correct
date.
Why might this be happening?
Click to expand...

Presumably because what you're putting into the cells isn't being treated as
a date. You may need to give some examples.
Which date format have you chosen? What are your regional settings? What
have you tried to put in as your date?
 
T

Thelma

I am having the same problem. Date format is 6/14/2006. When I enter 050106
the date is changed to 3/7/2037. I have tried all the different number
formats and this still happens. General format shows 050106 (duh!) The only
way I can get around it is to enter 05/01/06, but it would be better if I
could enter it without the /

Regional settings are normal.

I would appreciate any help you guys could offer.

Thanks so much!
 
J

JE McGimpsey

Cell format has no effect on how an entry is parsed by XL (except if the
format is Text - then it's not parsed at all).

Since dates to XL are just numbers (integer offsets from a base date),
XL has to have some sort of delimiter in order to tell whether you're
entering a date or a number.

To do what you want requires an event macro. See

http://cpearson.com/excel/DateTimeEntry.htm
 
Top