Results 1 to 12 of 12
  1. #1
    smk
    Guest

    formating columns - HELP!!!!!

    This is my second post of this and I've not had any replies-
    Is it me or is it Excel??? I can certainly apprecitate some direction from anyone that would like to help, please!
    My system is: Office 2000, Windows98, regional settings are 1930 to 2029, default '1900':
    Upon formatting a column to 'date' the displayed date is
    NOT the date I've entered. This is on ANY worksheet. The populated date is c. 45,410 days ahead, (i.e., I typed 8/17/99 and upon entering, 12/15/23 populates the cell. I'm learning Excel and this is NOT helping me. This appears to be a 'system' problem. Do I need the Analysis Pack Add-In??? Thanks for your help,

    Steve

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formating columns - HELP!!!!!

    There are a number of possibilities. First, to "Options" in the "Tool" menu. In the dialog box that results click on the "Transition" tab. At the bottom of that display see if you have either "Transition formula evaluation" or "Transition entry" selected. If you do, deselect them and try the entry again. Either of those options can cause this symptom.

    If that is not it, then we need a little more information. Format the cells containing the date to display a 4 digit year ("mm/dd/yyyy") and tell us what that year really is. It is 0023, 1923, 2023, etc.?
    Legare Coleman

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: formating columns - HELP!!!!!

    Every single date entered is always jumped 45,410 forward?
    Is this the English/European version of Windows and Excel?

    When you enter =now() and format it as a number, what value is returned? (18 January 2001 should be day 36909.) If you first format a blank cell as a number and then enter =now(), does the cell return the same value? What shows if you enter 36909 as a number and then reformat it to be a date? Does the date change if you take the number cell reading 36909 and reformat it to date? Can you post exactly what date format you are using according to Format Cells, Number?

    Can you check to see if you have any weird code running (intentionally or not); use Alt-F11 and examine all the modules through View Project Explorer.

    In Excel Tools, Options, Transition do you have any Sheet Options checked?

    What date does your PC clock show? Are you using the Gregorian Calendar (though that shouldn't make a difference, I'm reaching)?
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    smk
    Guest

    Re: formating columns - HELP!!!!!

    Thanks for responding!!!
    The Sheet Options 'transition...' have both always been de-selected. I've tried the four digit date format with same results, '1932'. When I first encountered this problem last week it returned '2032'. I'm baffled. Please notice my reply to JohnBF as he is also responding to my dilemma. I appreciate your assistance.

    Steve

  5. #5
    smk
    Guest

    Re: formating columns - HELP!!!!!

    Thanks for responding!!
    System purchased in US: W98, Pentium II, Excel 2000 in Office 2000 Pro; Settings: Gregorian Calendar; English (US); Short Date: mm,dd,yy, (I

  6. #6
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formating columns - HELP!!!!!

    Apparently, there is some confusion between setting the date format (which controls how the date will actually be displayed in the cell) with how the actual date 'data' must be entered.

    011801 entered in to a date formated cell is interpreted as a numeric date serial number which does indeed translate to April 22, 1932. The '011801' must be entered as 01/18/01 for Excel to recognize it as a date. Try it this way instead.

  7. #7
    smk
    Guest

    Re: formating columns - HELP!!!!!

    Thanks for the info.
    As I've stated before, I can manually type the slashes, dashes, etc. but would really like to enjoy the wonders of formatting. As it is, the date format puts the slashes in for me but gives me an incorrect date. Something's goofy with the 'serial numbering' of the date database, it seems to me. Thanks for your help.

    Steve

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    eastern Connecticut, Connecticut, USA
    Posts
    113
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formating columns - HELP!!!!!

    Check the topic "Enter and edit data" in Excel Help. This topic also includes an expansion covering dates. Bottom line: You must enter date data with separators (which are chosen by your Regional settings in Windows). This is not like setting up a custom format that will stick things like hypens in the middle of a number so that you can type in phone numbers without the hyphens -- you have to enter dates with the separators.

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formating columns - HELP!!!!!

    From your description of what you are doing, nothing is goofy, it is working exactly as it should be working. The date format is the DISPLAY format and has nothing to do with interpreting what you type into a cell, it only determines how what is in a cell is displayed. When you enter something like 11801 into a cell, Excell does not have any way of knowing that you mean this to be a date. It will put that number into the cell. If the cell is formatted as a date, then Excel will display a date that is 11,801 days from 1/1/1900 which is not 1/18/2001. When entering a date into a cell, you must type in the separator ncharacters. If you want it to work the way you described, it can be done by writing a Change event macro that will take what you type and insert the separators.
    Legare Coleman

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: formating columns - HELP!!!!!

    SMK, now I understand what you were expecting I'm sorry to advise you that Gene and Legare are correct; you have to type the m, d and y separators. Excel isn't quite smart enough to do what you are expecting, among other things because it doesn't know if 11101 is (in US mm/dd/yyyy notation) is 11/01/2001 or 01/11/2001 or 11/01/1901 or 1/11/1901, never mind that you think the format setting tells Excel what to expect.

    Sorry, we all have our excel Annoyances to bear.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: formating columns - HELP!!!!!

    CMK, one other thing, you don't need the Analysis Toolpack for what you were trying to do, but it does have a number of useful extensions; I load it for some of the date functions.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    smk
    Guest

    Re: formating columns - HELP!!!!!

    I would like to thank all of you, Legare, Gene and John for
    your help and enlightenment. I'll most assuredly have more
    questions as I progress in my Office 2000 studies, so will
    be in touch. Maybe when I get there, I'll be able to someone else with these applications. Thanks again, all.

    Steve

Posting Permissions

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