Results 1 to 15 of 15
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Location
    Cockermouth, Cumbria, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Dates (XP (but others also?))

    I have regional settings set to English UK in COntrol panel
    I have cells formatted to Englih UK date 14/01/2001
    When i type in dates that are obviously English (e.g. 27/01/2004) all
    is OK
    If I type in a date that is ambiguous, say 04/05/2004 (4th May) it is
    formatted to American and displays as 5th April

    Any ideas anyone. I am pulling my hair out. This spreadsheet is used
    by around 100 tutors submitted test data for importing to the
    database, and the dates are crazy)

    Ron

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Excel Dates (XP (but others also?))

    Can you check Control Panel > Regional Settings to see what date format is selected there.

    StuartR

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Dates (XP (but others also?))

    Excel uses the date format set in the Regional Options / Regional and Language Options control panel, except for cells whose number format property has been set explicitly to a format that deviates from the Control Panel setting.

  4. #4
    New Lounger
    Join Date
    Jun 2004
    Location
    Cockermouth, Cumbria, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (XP (but others also?))

    As stated in the original post, the control panel settings are English, as are the cell formats in Excel, and all is fine unless the date is ambiguous, when it switches to American

  5. #5
    New Lounger
    Join Date
    Jun 2004
    Location
    Cockermouth, Cumbria, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (XP (but others also?))

    I have just noticed that when I check the cell formatting after Excel has converted, the date the format type has an asterix (*) next to it. Not sure what this means. So, all formats everywhere set to English dd/mm/yyyy, I type in 11/05/2004 (meaning 11th May) Excel immediatley converts it to American (5th November), on checking the cell format it as *03/14/2001

  6. #6
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (XP (but others also?))

    I have xl 2000 and don't have this problem, under these circumstances.
    I do have the problem if I use a macro to import a load of dates from a csv file from elsewhere (but not if the copy and paste is done by hand.)
    A thread on this topic a few weeks ago suggested that vba is exclusively US English as far as dates go.
    As a shot in the dark, I suggest you do a custom format of the cells as ddd d mmm yyyy so that the date entered as, say, 3/6/5 shows as Fri 3 Jun 2005 - OR NOT!
    At least the error, if there is one, is then more obvious.
    You've probably noticed that if the date is in the current year, then merely entering it as 3/6 is enough (it displays as Thu 3 Jun 2004.)
    The way the date is displayed is immaterial to any arithmetic done on the date. It's still a date number, really.
    Best of luck!

  7. #7
    New Lounger
    Join Date
    Jun 2004
    Location
    Cockermouth, Cumbria, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (XP (but others also?))

    I will try the custom idea. The problem is that although it does not matter to much inExcel, the dates are imported into a much larger database that keeps track of student test dates and scores. The variance in date types is maintained, which therfore means test dates are inaccuratley portrayed.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Dates (XP (but others also?))

    Although I have no reason to doubt your statements, the format "*03/14/2001" suggests strongly that there must be a US setting somewhere in your system. The * before the format indicates that the format follows the date setting of the operating system.

    You might check the keyboard input language; that's what determines the default language in Word, perhaps it plays a role in Excel too (it doesn't influence the date setting on my system, but it takes only a minute to check.) Under Windows XP, the keyboard input language is set in the Languages tab of the Regional Settings control panel; in older Windows versions, it is in the Keyboard control panel.

  9. #9
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (XP (but others also?))

    Flying blind, here, Ron, but if Hans' solution doesn't do the trick, but if the custom format trick works at least in Excel, is there any way of exporting the dates as text, and converting them back to real dates in the database application?

  10. #10
    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: Excel Dates (XP (but others also?))

    Ron,
    I'm guessing you are using Win XP too - if not you may have to vary instructions slightly. In the Control Panel, next to the dropdown that says settings are English (United Kingdom), there should be a Customize button. If you click on that and then check the date tab, does the short date format show as dd/MM/yyyy or MM/dd/yyyy? It is possible to customize this but the dropdown list will still show it as English (United Kingdom).
    Might be worth checking.
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (XP (but others also?))

    A few questions.
    1) When you type 27/01/2004 into an English-US cell, you would get 27/01/2004 even when the format is set to date dd/mm/yyyy. Excel would not be able to convert it and thus would leave it as-is which would appear to be English-UK correct. Maybe those entries that you believe are working correctly are not really formatted in English-UK and are just being read as text? If you try entering 27-01-2004 does it convert to 27/01/2004 or does it keep the dashes? I'm just trying to decipher whether the file is being read/formatted in both English/UK and English/US. If you would find that only English/US is really being used it would narrow down your search for the problem.

    2) Does this only occur on this particular spreadsheet or have you noticed problems on other files you run on your system?

    3) Did you create this spreadsheet or did whoever made it place some coding that you are unaware of into it which adjusts formatting for some reason?

    yoyoPHIL

  12. #12
    New Lounger
    Join Date
    Jun 2004
    Location
    Cockermouth, Cumbria, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (XP (but others also?))

    By jove, I think he's got i ;-))
    I have experimented at length and deduced that you cannot type in an English date, just format to look like an Englsh date. So when I type in 28/12/2004, meaning 28th December, even though the cell is formatted to English UK, EXCEL still expects the 28 to be a month, which of course it cannot be 'cos there are only 12, it therefore treats the whole cell as text !!!!
    Thanks Bill !
    So us English / English folk cannot really use English, unless we know how to play along to Bill's little game.
    Incdentally if I type in 28 dec, it works fine.
    Now all I need to do is determine how to best sort this all out

    Thanks for all your help, much appreciated

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Excel Dates (XP (but others also?))

    There MUST be something wrong with your settings. It IS possible to enter dates in UK date format. However, if you have checked all the suggestions made in this thread, I don't know what else to try.

  14. #14
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (XP (but others also?))

    Its PHIL not Bill but at least you didn't call me Woody (Hans has been mislabeled Woody already).

    What I get from your answer to my first question was that your spreadsheet is acting as though it is set to English/US and not English/UK as you think. As Hans stated, "Excel uses the date format set in the Regional Options / Regional and Language Options control panel, EXCEPT for cells whose number format property has been set explicitly to a format that deviates from the Control Panel setting."
    If your control panel setting is set to English/UK then I believe you have a setting somewhere which tells the file/sheet to use English/US.
    Did you try opening a new file and entering dates and did it act the same way? If the new file acted correctly, that is, the date was properly configured in English/UK, then the problem is within your original file and not the system settings. Hopefully this gets you on the right track to finding the problem.

    yoyoPHIL

  15. #15
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Blacktown, Sydney, New South Wales, Australia
    Posts
    175
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Excel Dates (XP (but others also?))

    Had a similar problem down under. Despite settings of English (Australian), I was getting US Date formats. I used a sledge hammer and created a custom date format. Saved heaps of time mucking around and a few inches on my hairline

Posting Permissions

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