Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Date format in Excel97

    I have a simple spreadsheet for my personal cashbook, including some fairly simple formulas. The first column (A [img]/w3timages/icons/smile.gif[/img]) is formatted as a date all the way down and not used in any formula. However the only way I can enter the date I want is as an =date(yy,mm,dd) function. In other spreadsheets I can format the column as a date, enter the date as dd/mm/yy and bingo, I get what I want ie in the format I have set for the column.
    Can anyone suggest what I have done in my cashbook spreadsheet to force me to enter the full formula each time? Please...

  2. #2
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    Have you tried Format/Cells/Custom and entering
    yy/mm/dd
    or
    dd/mm/yy
    depending on your preference, instead of using the Date format?
    With either of the above, I find I can simply enter (for todays date) 9-1 or 09-01 and get 01/01/09 or 09/01/01
    (Local here is UK - dd/mm/yy)

  3. #3
    Lounger
    Join Date
    Jan 2001
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    Try checking the regional settings on your Control Panel.

  4. #4
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    Thanks, but no go. What you suggest and the "normal" method I tried (use Format cells as date dd-mmm-yy and set Control Panel global date format as dd/mm/yy and then just type 12/01/01 expecting the result 12-Jan-01) works happily in other spreadsheets on my machine but not this one! In this sheet it gives me 12-Jan-00! I cannot figure out why! I can only assume I have some underlying format on this column which I cannot overwrite. Cheers, Ken

  5. #5
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    Thanks. See my response to Lief. Cheers, Ken

  6. #6
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    Ken,

    An obvious point, but just to make sure we've covered all bases.

    I assume you highlighted the cells when you applied formatting.

    If so, and you have problems, you may be able to attacj the spreadsheet to find out what'snot working for you.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  7. #7
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    Are you saying the year is wrong?
    Is the date set correctly on your PC - (no offense meant [img]/w3timages/icons/smile.gif[/img])
    Otherwise, do you want to attach a (blank) copy to analyse?
    And can you just confirm:
    You want to literally enter <font color=blue>11/01/01</font color=blue> and have the result <font color=red>11-Jan-01</font color=red>

  8. #8
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    No. In the example I quoted it appeared that way. However I just tried a few other examples and it is all over the place - often giving me 00-Jan-00 which I think indicates that it is trying to read my entered data as a numeric string and then convert it to a date. My real puzzle is that in a new spread sheet if I format the cells as Date (dd-mmm-yy) and enter dd/mm/yy in the cell I get what I want! But not in my cashbook sheet!! Weird.
    Yes the date is correct - no offence taken - it wouldn't be my first stupid mistake if it had been!
    I'd prefer not to put our personal finances on the airwaves, so I'm sorry but I haven't attached a copy.
    And yes, I want to enter 12/01/01 and get the result 12-Jan-01. AND it works in a new (clean) s/sheet (in cell A1)! I suppose I could create a new spreadsheet, set it up the same as this one, taking care with what I do with column A [img]/w3timages/icons/grin.gif[/img] and enter some dummy data to try. I just feel there is some underlying formatting which is getting in my way.
    Cheers, Ken

  9. #9
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    Thanks. As I said to Leif the data is a bit too personal to publish abroad. I'll see if we get any other answers and also try my other solution (outlined in the reply to Leif). If it goes pear shaped as well I can publish it because the data will all be imaginery. Ciao, Ken

  10. #10
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    Sorry, I didn't answer your question. Yes I did highlight the appropriate cells. [img]/w3timages/icons/smile.gif[/img] Ken

  11. #11
    Plutonium Lounger Leif's Avatar
    Join Date
    Dec 2000
    Location
    U.K.
    Posts
    14,010
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    How are you entering the data - typing directly into the cell?
    Have you tried selecting column A from a good worksheet and pasting it into the problem one?

  12. #12
    New Lounger
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Date format in Excel97

    Yes, Leif, typing directly into the cell. I'll try your cut and paste suggestion tomorrow (and, if I have time, my proposed reconstruction type solution) and let you know. Thanks for your responses. I'm logging off now - need my beauty sleep - about 60 year's worth! Speak with you next time. Ciao, Ken

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

    Re: Date format in Excel97

    I see that you have gotten numerous attempts to solve this problem from others. We could probably go on like this and eventually find out exactly what is causing the problem. It would probably be something simple like having "Transition Formulas" selected in "Options." However, it could take a long time to narrow it down this way.

    I will suggest something that might get you working more quickly. Why not create a new workbook and copy the worksheet to the new workbook (everything but the column causing the problem). If the date entry then works in the new workbook, you can copy any other worksheets, VBA code, etc. to that workbook and delete the one that does not work.

    If you want to sent the offending workbook to use to look at, then make a copy of it and delete all of the data. See if the problem still happens, and if it does send us the workbook (not directly to me as I no longer have Excel 97 available).
    Legare Coleman

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Date format in Excel97

    Hi,
    Just to confirm - Legare's suggestion about Transition formulae (on the Transitions tab in Options, which I think is workbook specific) would produce exactly the behaviour you describe, so it's definitely worth checking before you copy everything to a new workbook.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    funnybone
    Guest

    Re: Date format in Excel97

    Hey, don't forget the Tools|Options|Transition|Sheet Options|Transition Formula Entry box might be checked!

Page 1 of 2 12 LastLast

Posting Permissions

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