Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Thanked 0 Times in 0 Posts

    Year wrong when formatting cell

    I format my cell using Format/Cells - I select Date and I use the first option *3/14/2001.

    I then type in 61811 and it comes up 3/24/2069. Of course, I'm looking for 6/18/2011.

    I am using MS Excel Home and Student 2007 version.
    I verified that it's set for English (United States), the long and short dates are correct (using today's date just how I'd want them)...not sure what else to check.
    If I enter 6/18/11, it formats it correctly. But its faster not to have to enter the /'s. On the version I use at work (NOT the Home and Student version), I would not have to enter the /'s to get it to format the way I need it to.

    What else can I check?


  2. #2
    New Lounger
    Join Date
    Dec 2009
    Thanked 0 Times in 0 Posts
    Hi Ruth,

    Behind the scenes, dates are just regular numbers. 1 is equal to Jan 1, 1900, 2 is Jan 2, 1900. Not surprisingly, 61811 days later is March 24, 2069. So that's normal.

    There must be some special format to allow you to enter a date without delimiters, but I don't know how to do that. But if you can do it at work, you should be able to do it at home - there's no difference in the Excel in the different packages, as far as I know.

    (In that case, how do you handle something like 12311? Is that Jan 23, 2011 or Dec 3, 2011?)

    Edit: The little research I've done online suggests that there's no normal built-in way to enter dates the way you want, but that it can be done with VBA. So maybe somebody has added that to your Excel at work.
    Last edited by bjulien; 2011-06-18 at 23:40. Reason: More information

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Pittsburgh, Pennsylvania, USA
    Thanked 342 Times in 335 Posts
    As bjulien alludes to, there is code for "quick entry" of dates (see for example MS MVP Chip Pearson's site at:

    I would presume the same thing bjulien suggests, that this code is in the workbooks you use at work. I would suspect it is a feature of particular workbooks, though perhaps it was made part of the default template. The workbooks with this "feature" should work on any computer or excel version if macros are enabled.


Posting Permissions

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