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?
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?
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" <[email protected]> wrote in message news:[email protected]...
> 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?
Oops! Also David. I did check the regional setting and they are set for
today's date and year.
"David McRitchie" wrote:
> 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" <[email protected]> wrote in message news:[email protected]...
> > 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?
>
>
>
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
..
"David McRitchie" wrote:
> 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" <[email protected]> wrote in message news:[email protected]...
> > 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?
>
>
>
On Sun, 23 Jan 2005 13:39:40 -0800, "smurphy123"
<[email protected]> wrote:
>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
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
---
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" <[email protected]> wrote in message news:[email protected]...
> 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
> .
>
> "David McRitchie" wrote:
>
> > 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.
> >
> > "smurphy123" <[email protected]> wrote ...
> > > 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?
> >
> >
> >
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.
"Ron Rosenfeld" <[email protected]> wrote in message news:[email protected]...
> On Sun, 23 Jan 2005 13:39:40 -0800, "smurphy123"
> <[email protected]> wrote:
>
> >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
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*
--
Regards,
RD
---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------?
"David McRitchie" <[email protected]> wrote in message
news:[email protected]...
> 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.
>
> "Ron Rosenfeld" <[email protected]> wrote in message
news:[email protected]...
> > On Sun, 23 Jan 2005 13:39:40 -0800, "smurphy123"
> > <[email protected]> wrote:
> >
> > >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
>
>
On Sun, 23 Jan 2005 19:30:15 -0500, "David McRitchie" <[email protected]>
wrote:
>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
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.
--
---
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
"Ron Rosenfeld" <[email protected]> wrote in message news:[email protected]...
> On Sun, 23 Jan 2005 19:30:15 -0500, "David McRitchie" <[email protected]>
> wrote:
>
> >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
On Sun, 23 Jan 2005 21:51:21 -0500, "David McRitchie" <[email protected]>
wrote:
>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
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.
"Ron Rosenfeld" <[email protected]> wrote
> But the Regional settings, at least here in the US, does affect Excel input.
> --ron
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks