+ Reply to Thread
Results 1 to 12 of 12

Excel Default date for date format

  1. #1
    smurphy123
    Guest

    Excel Default date for date format

    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?

  2. #2
    David McRitchie
    Guest

    Re: Excel Default date for date format

    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?




  3. #3
    smurphy123
    Guest

    Re: Excel Default date for date format

    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?

    >
    >
    >


  4. #4
    smurphy123
    Guest

    Re: Excel Default date for date format

    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?

    >
    >
    >


  5. #5
    Ron Rosenfeld
    Guest

    Re: Excel Default date for date format

    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

  6. #6
    David McRitchie
    Guest

    Re: Excel Default date for date format

    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?

    > >
    > >
    > >




  7. #7
    David McRitchie
    Guest

    Re: Excel Default date for date format

    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




  8. #8
    Ragdyer
    Guest

    Re: Excel Default date for date format

    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

    >
    >



  9. #9
    Ron Rosenfeld
    Guest

    Re: Excel Default date for date format

    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

  10. #10
    David McRitchie
    Guest

    Re: Excel Default date for date format

    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




  11. #11
    Ron Rosenfeld
    Guest

    Re: Excel Default date for date format

    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

  12. #12
    David McRitchie
    Guest

    Re: Excel Default date for date format

    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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1