Cell Format Issue

[Becky]

I get unexpected results when date formatting a worksheet cell in a MS Excel
spreadsheet - in other words it looks like a calculation is trying to take
place such that entering 1220 in the cell returns a date of 5/4/1903 and
entering 12202006 0r 122006 returns a negative date displayed as ########. I
have not experienced something like that before – and I use MS Excel way too
much!

The cells are formatted as dates.

Is there a way I can enter the dates without the slashes?

 

[Marcelo]

hi Becky,

Format as custom mdaaaa

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Becky" escreveu:

 

[Becky]

That did not work.
If I enter 1220 I get 54Monday in the cell and in the function area it says
5/4/1903
If I enter 122006 I get 114Tuesday in the cell and in the function area it
says 1/14/2234.

I know what the m and d are for what are the aaaa?

 

[Dave Peterson]

I bet it's a language difference. I'm guessing that the "a" in Marcelo's
native language represents some variation of year (like Annual). Why the USA(?)
excel treats it as a formatting character, I don't have a guess.

But if you enter 12202006 into a cell, then excel has no way of knowing that
you're entering a date. xl just figures you're typing a giant number
12,202,006.

If you try to format that as a date, then excel will do what you say. But excel
just uses a number to represent the number of days from a starting date (Dec 31,
1899 for most windows users).

So if you entered a real date 12/20/2006 and format it with a General format,
you'll see: 39071. And this is just the number of days since 12/31/1899.

====
But you do have some options to make data entry easier. You can use a worksheet
event that Chip Pearson created that allows you to enter the data the way you
want. But this event will convert it to a real date.

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

Or you could use another cell with a formula that converts it to a date.
This works for me with my USA Settings (mdy):
=--TEXT(A1,"00\/00\/0000")
(but I have to format the cell as date, else I'll see that number (like 39071).)

One more option is to preformat the cell/column as Text.
then enter your values always using 8 characters.
select the range (single column at a time)
data|text to columns
fixed width
remove any lines that excel guessed
and choose mdy
and format it the way you like.

 

[Becky]

Thanks, Dave
I will see if the user wants this code or not. This was very helpful.
Thanks again

 

[Gord Dibben]

Becky

You cannot format a cell to do this.

Chip Pearson's site has event code that will look after the change as you enter
the numbers.

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

Also check out his site to see how Excel handles dates.

http://www.cpearson.com/excel/datetime.htm#SerialDates


Gord Dibben MS Excel MVP

That did not work.
If I enter 1220 I get 54Monday in the cell and in the function area it says
5/4/1903
If I enter 122006 I get 114Tuesday in the cell and in the function area it
says 1/14/2234.

I know what the m and d are for what are the aaaa?

Gord Dibben MS Excel MVP

 

[Marcelo]

Yes Dave you are right,

Here in Brazil we speak portuguese and Year means ANO,

Sorry for the mistake..

--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Dave Peterson" escreveu:

 

[Dave Peterson]

Not a mistake--just an international difference.

(I always assume USA settings. So it's not just your problem <bg>.)