Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    USA
    Posts
    51
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Formatting cell to date does not work

    I am using MS Excel 2007 Part of Microsoft Office Home and Student 2007. When I format a cell to display the date (no matter which format of date I select) it does not return the correct date format.
    Example: I format it to display 3/14/2001. I enter 73011. It should return 7/30/2011. It returns 11/22/2099.
    The locale (location) selected is English (United States).
    If i type it in using the /'s it comes out as expected. But I don't want to type the /'s
    what else can i do to make it format properly?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    When you enter 73011 Excel thinks you have just typed in a number, so it correctly formats it as a date. Dates are just numbers, and the number for the date 11/22/2099 is 73011.

    Formatting a cell as a date does not remove the need to type the / marks. In Access you can do this with an Input Mask, but I don't believe this can be done in Excel.
    Regards
    John



  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    As johnhutchison mentioned, the behavior you experience is as expected. Without the slashes (/) excel does not know you want the date July 30, 2011 and not the number 73,011. You can create a routine to automatically convert numbers that represent dates without the separators to actual values excel can interpret as a date. For details see MS MVP Chip Pearson's site at http://www.cpearson.com/excel/DateTimeEntry.htm

    Steve

  4. #4
    Star Lounger
    Join Date
    Feb 2004
    Posts
    92
    Thanks
    2
    Thanked 6 Times in 6 Posts
    By the way, you DO have to type the //s but you only have to type one. If you have the date format set to 7/16/2011 you only need type 7/16 then tab or enter and the cell will properly show 7/16/2011.

    Joel

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    And don't forget my favourite Ctrl + ;
    This enters the current date.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Star Lounger
    Join Date
    Feb 2004
    Posts
    92
    Thanks
    2
    Thanked 6 Times in 6 Posts
    Well yes, it still does enter the current date, but that's only good today! :-)
    Joel

  7. #7
    Lounger
    Join Date
    Dec 2009
    Location
    Breaux Bridge, La. USA
    Posts
    44
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [QUOTE= And don't forget my favourite Ctrl + ;
    This enters the current date. l[/QUOTE]

    Is there something like tis that works in Open Office Calc.?

Posting Permissions

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