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

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?

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?

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?

lizzie wrote:
>
> 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?

--

Dave Peterson

"lizzie" <lizzie@discussions.microsoft.com> wrote in message
news:14A58730-76D7-4C38-B01E-2D2A7FDC90FE@microsoft.com...
> 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?

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?
--
David Biddulph

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!

"lizzie" wrote:

> 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?

You need to give a hint to excel that you're entering a date.

The slashes are one of the ways to give that hint to excel.

But if you really, really want to do this, you could use an event macro that
takes your input and creates the date.

See Chip Pearson's site for instructions:
http://www.cpearson.com/excel/DateTimeEntry.htm

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Thelma wrote:
>
> 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!
>
> "lizzie" wrote:
>
> > 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?

--

Dave Peterson

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


In article <99577DAC-4879-4F72-9AD3-F32C7A225DE0@microsoft.com>,
Thelma <Thelma@discussions.microsoft.com> wrote:

> 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!
>
> "lizzie" wrote:
>
> > 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?