S
smurphy123
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?
the default date to think it is 2005?
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
D
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.
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.
S
smurphy123
Oops! Also David. I did check the regional setting and they are set for
today's date and year.
today's date and year.
David McRitchie said: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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
smurphy123 said: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?
S
smurphy123
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
..
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
..
David McRitchie said: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.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
smurphy123 said: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?
R
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
D
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
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
D
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.
5) What do you have for your short date format in Regional
formatting, it should not show digits only letters and separators.
R
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*
settings are OK, and something is not right in XL *only*
R
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
D
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.
--
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.
--
R
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
D
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.
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.