Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2004
    Location
    London
    Posts
    64

    Question Unanswered: Date Format In Excel

    Hi all,
    Has anyone come across any problems with Excel 2002 changing the format of dates for no apparent reason. This happens intermittently even though the date is entered correctly in the first instance (in thsi case a user form) however it will switch todays date (16/11/04) to a US date (11/16/04) for no reason. I cant believe this is some random glitch with Excel and although i have had this problem in the past it is very intermittent.

    Does anyone know of a workaround or solution for this at all ? ? ?

    Many thanks in advance

    Chris Durant
    christopher.durant@churchill.com

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    I don't know if this is the case with Excel 2002, but I was working for a U.S. based subsidiary of a U.K company in advance of Y2k and I remember Excel getting its default sequencing of dates (regardless of the formatting option which determines how they will display), from the Windows setup. Go to your Control Panel and select Regional Options and select the Date tab and make sure your Short Date follows your desired dd/mm/yyyy. If information is getting entered to this spreadsheet from a user form and thus from a different computer, I think you could have a problem if their regional options are not in sync with yours. Just a thought.

    TD

  3. #3
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Ive occasionally had this problem when working with dates and using VBA
    The way i got round it was to force excel to work with numbers then later format this to dates,
    to compund the problem it would only happen to some dates and not them all.
    If you are running any code at all then i wiuld suggest doing something like

    range.Numberformat = "0"
    'do your calcuulation or input or something then
    range.numberformat = "dd/mm/yyyy"

    Dave

  4. #4
    Join Date
    Sep 2004
    Location
    London
    Posts
    64
    Thanks for the replies guys, much apreciated and i managed to get the problem sorted.

    chris

Posting Permissions

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