Excel Default date for date format

[Guest]

Everytime I enter a date in a cell, Excel thinks it is 2001. How to I get
the default date to think it is 2005?

 

[David McRitchie]

Perhaps it is January you are looking at.
What happens if you use a cell format of
ddd mmm dd, yyyy
entered via format, cells, custom, ...

The default date format is the short date format
in your Regional Settings (if you have Windows on a PC).
Settings, control panel, Regional Options Date

Another possibility is that you used a constant digit instead
of the letter within a custom number format.

 

[Guest]

Oops! Also David. I did check the regional setting and they are set for
today's date and year.

 

[Guest]

Let me explain a little further. When I enter a month and day, the computer
assumes it is 2001. I am using the date format.

I want to be able to enter jan 12 and hit enter and it populates the date
with the current year. Right now I have to change each time I hit enter
..

 

[Ron Rosenfeld]

Let me explain a little further. When I enter a month and day, the computer
assumes it is 2001. I am using the date format.

I want to be able to enter jan 12 and hit enter and it populates the date
with the current year. Right now I have to change each time I hit enter
.

Please post back:

1. Your regional settings (Control Panel/Regional and Language Options)-- the
country and Short Date options.
2. Exactly what keystrokes you enter.
3. The exact cell format: Select the cell, then Format/Cells/Custom and post
what is in the Type: box.
4. Exactly what is displayed in the formula bar just above the worksheet area.


--ron

 

[David McRitchie]

If you are spelling out the month as 3 letters it works for me.
In fact if i enter 01/12 I do get 2005-01-12 which is my default format.

Did you try what I suggested, actually the following would be a better test
A1: Jan 8
B1: =A1 format as ddd mmm dd, yyyy
C1: =A1 format as mm/dd/yyyy or as dd/mm/yyyy or as yyyy-mm-dd

 

[David McRitchie]

In addition to what Ron posted also
5) What do you have for your short date format in Regional
formatting, it should not show digits only letters and separators.

 

[Ragdyer]

If the date of his postings are correct, must not we assume that "system"
settings are OK, and something is not right in XL *only*

 

[Ron Rosenfeld]

In addition to what Ron posted also
5) What do you have for your short date format in Regional
formatting, it should not show digits only letters and separators.

Well, if you missed it in my #1, possibly the OP will miss it, too.

Or did you mean something different than I?

Thanks.


--ron

 

[David McRitchie]

Yes Rob, I meant something different than your #1, it is
possible to put digits into a format, even though it would
be incorrect. That is why I also asked that the format be
shown. Actually I did mention this in my first response, but
haven't seen anything back from the poster so I asked to see
exactly what is in the Regional short date form -- which only
affects display not input and not content.

I didn't understand another comment (RD) about if you see the
date in the postings it must be right. Because the posters
formatting or even his local time zone is not shown in what
you see. It is your own system and it is long date form that
I am seeing in Outlook Express anyway.

--

 

[Ron Rosenfeld]

Yes Rob, I meant something different than your #1, it is
possible to put digits into a format, even though it would
be incorrect. That is why I also asked that the format be
shown. Actually I did mention this in my first response, but
haven't seen anything back from the poster so I asked to see
exactly what is in the Regional short date form -- which only
affects display not input and not content.

I didn't understand another comment (RD) about if you see the
date in the postings it must be right. Because the posters
formatting or even his local time zone is not shown in what
you see. It is your own system and it is long date form that
I am seeing in Outlook Express anyway.

I thought I meant the same thing -- looking at the first page of the Regional
settings -- but obviously I did not express it unambiguously. Thank you for
clearing that up.

But the Regional settings, at least here in the US, does affect Excel input.
--ron

 

[David McRitchie]

Hi Rob,
You're right the Regional Date Format will affect the
default acceptance order of month, day and year for input;
whereas, a cell date format will only supply the format to
display not the acceptance order of month, day, year.

Default order will be overriden if the entry would be invalid
so Excel will "correct" your date i.e. 2005
can't be a month or day of month so it must be a year,
and make up an order to match what it sees.