date formatting
 

so 1) everytime i enter just the year as in 2005, after leaving the cell,
it's changed to 6/27/1905. everytime i enter 2001, after leaving the cell,
it's changed to 6/23/1905. what's going on? and what do i have to do to
permanately change this?

2) when i enter just the month and the year as in 5/05, or even 5/2005, it
is changed to 1-May, but up in the formula box or whatever it is called, it
says 5/1/2006. it doesn't matter what month or what year, if i use the m/yy
or m/yyyy format, it always changes it to the first of the month of 2006.
again, what do i have to do to permenately change this?

in both cases, it doesn't matter if a copy and paste the date in or type it
in, and sometime (keyword: sometimes) the format painbrush works.

please, what is going on?

date formatting
 

I appreciate that this is a little frustrating.

1. Format a cell as Custom yyyy, then enter any date like 12/25/2006 and
the cell will display 2006

2. Format a cell as Custom mm/yyyy and enter 05/2005 and that's what you
will see.
--
Gary's Student


"ryan" wrote:

so 1) everytime i enter just the year as in 2005, after leaving the cell,
it's changed to 6/27/1905. everytime i enter 2001, after leaving the cell,
it's changed to 6/23/1905. what's going on? and what do i have to do to
permanately change this?

2) when i enter just the month and the year as in 5/05, or even 5/2005, it
is changed to 1-May, but up in the formula box or whatever it is called, it
says 5/1/2006. it doesn't matter what month or what year, if i use the m/yy
or m/yyyy format, it always changes it to the first of the month of 2006.
again, what do i have to do to permenately change this?

in both cases, it doesn't matter if a copy and paste the date in or type it
in, and sometime (keyword: sometimes) the format painbrush works.

please, what is going on?

date formatting
 

On Wed, 20 Dec 2006 12:13:01 -0800, Gary''s Student
wrote:

2. Format a cell as Custom mm/yyyy and enter 05/2005 and that's what you
will see.


Of course, if you enter 05/05 you will see 05/2006


--ron

date formatting
 

On Wed, 20 Dec 2006 11:45:00 -0800, ryan
wrote:

so 1) everytime i enter just the year as in 2005, after leaving the cell,
it's changed to 6/27/1905. everytime i enter 2001, after leaving the cell,
it's changed to 6/23/1905. what's going on? and what do i have to do to
permanately change this?

2) when i enter just the month and the year as in 5/05, or even 5/2005, it
is changed to 1-May, but up in the formula box or whatever it is called, it
says 5/1/2006. it doesn't matter what month or what year, if i use the m/yy
or m/yyyy format, it always changes it to the first of the month of 2006.
again, what do i have to do to permenately change this?

in both cases, it doesn't matter if a copy and paste the date in or type it
in, and sometime (keyword: sometimes) the format painbrush works.

please, what is going on?

What is going on is that the cell format only controls how the entry is
DISPLAYED. For entering sequences that look like dates, Excel supplies a
different set of rules then for entering numbers.

For example, when you enter 2005 into a cell that is formatted as a date, Excel
stores 2005 in that cell. It will display the 2005th day of its "calendar"
which starts at 1/1/1900 which turns out to be 27 june 1905.

When you enter a sequence such as 5/05, Excel tries to interpret this as a date
in the Windows regional format. If you are set to US (m/d/y), it will try to
interpret as month/day and append the current year. If the "d" cannot be in
the month (e.g. 2/30), then it will interpret the denominator as a year, and
translate from a two digit to a four digit year in accord with the setting
determining that.

If the numerator is not a valid month, (ie not 1-12), then the entire entry
will be interpreted as text.

The above parsing takes place entirely independent from how you format the
cell.
--ron

date formatting
 

ok, yeah microsoft is wonderful and all and they've brought us some fabulous
modern tidbits of technology.....but seriously, why can't i just get excel to
display exactly what i type? why does it always have to be formatted? does
it always have to be formatted?

ryan

date formatting
 

so, this may be a dumb question, but basically, i'm having all these
"problems" because the cell is formatted as a date? so if i just wanted to
throw all this out, basically, i should not format it as a date, like text or
something?

i also wanted to say that thus far you've been a life saver because i was
about to throw the computer out the 10th floor window of my building.

thanks

date formatting
 

On Wed, 20 Dec 2006 13:44:00 -0800, ryan
wrote:

so, this may be a dumb question, but basically, i'm having all these
"problems" because the cell is formatted as a date? so if i just wanted to
throw all this out, basically, i should not format it as a date, like text or
something?

i also wanted to say that thus far you've been a life saver because i was
about to throw the computer out the 10th floor window of my building.

thanks

Excel stores dates as serial numbers with 1 = 1 Jan 1900.

If you want to ensure that what you type in is what you will see, then
formatting the cell as TEXT, or preceding your entry with the single quote
(apostrophe) text qualifier will accomplish that.

If your entry is merely a number, without anything like a "/" that would cause
Excel to treat the entry as a date, then you could format the cell as General,
or Number with 0 decimal places.

However, if you want to use your entry as a date, then you will need to either
understand Excel's parsing rules;
make up your own parsing rules and implement them in VBA;
or enter dates unambiguously. (For example 1 Jan 2006 is an unambiguous
entry).


--ron