Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Cant change date format if in UK English (2003 SP3)

    We have a web reporting application that has an option to create an Excel file from the report results being displayed. It offers the ability to Open or Save as an Excel file.

    If I have my regional settings (set in Control Panel - Regional and Language Options) on the Regional Options tab set to English (United States), I do not have any problems with the file and I am able to change the formatting as needed.

    If the users Regional Settings are set as English (United Kingdom), it seems to be very hit or miss with the formatting.

    One of the columns we are struggling with is a date column. If the file is saved while in the UK version, either none of the cells formats can be changed and formulas on these cells do nothing but result in an error.

    If saved in the US version, we are not having any problems with the file.

    I am attaching a spreadsheet with 2 columns. The first column lists the dates as saved during the UK regional setting. The 2nd column is the US setting.

    Can someone help shed some light on why we are having the issues with the UK regional settings but not the US?


    The Excel file from the web site is created from some Java Script that saves the HTML file being displayed (as a HTML file) on the screen and save the file to the server. It then directs Excel to open the file from the server.
    Attached Files Attached Files
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

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

    Re: Cant change date format if in UK English (2003 SP3)

    Some of t he cells in column A (A3:A24 for example) are being saved as text not dates and that is why they can not be formtted. Since I don't understand the process for saving the workbook, I can't tell you why they are text and not dates. If web site is displaying the dates in US format (mm/dd/yy) then excel probably will not recognize those as valid dates if the file is saved on a system set to UK format (dd/mm/yy).
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cant change date format if in UK English (2003 SP3)

    That is one of the strange items. The first time I tried to replicate this, all was well with the formatting. Sometimes all of the cells appear as text or as in my example as you pointed out, some are, some are not.

    From what one of the developers informed me, when we run the report it is displayed as HTML on a web page. The button to save the file first saves the HTML file on the screen to the local sever & then directs Excel to open that file. We do have the option to save the file directly as an HTML or Excel file, but have the same issue.


    Let me ask this a different way, for those cells that are appearing as Text, what or how do I change the format in Excel on these cells so they can be treated as a date?
    I have tried to format as a date, general, custom date, tried the Text to Columns trick as General, Date or Text, none seem to work.

    How can I change the format on those cells so they can be treated as a date so I then can do some date sorting (like by month)?

    TIA!
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

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

    Re: Cant change date format if in UK English (2003 SP3)

    The issue may be that some dates can be recognized as dates in either US or UK format. For example, the US date 1/3/07 (Jan 3rd 2007) would also be recognized as a valid UK date (Mar 1st 2007). However, the US date 10/24/07 would not be recognized as a valid UK date since 24 is not a valid month. Hopefully, Jan Karel or Hans or someone else who is much more familiar with Excel and European dates will jump in with a better answer. About the only thing I can think of that might help would be to format the web site dates in the dd/mm/yy format before saving the file into HTML. Otherwise, I a afraid you are going to be stuck with running a macro that will change the text dates into Excel dates after opening the file in Excel.
    Legare Coleman

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

    Re: Cant change date format if in UK English (2003 SP3)

    Legare already provided the explanation. If the website doesn't have an option to display the dates according to the system settings, setting your PC to use US date format (temporarily) is probably the best workaround. Save the workbook. If you then switch back to UK date format and reopen the workbook, it'll probably be OK.

  6. #6
    Star Lounger
    Join Date
    Oct 2003
    Location
    Colorado, USA
    Posts
    57
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cant change date format if in UK English (2003 SP3)

    Thanks all to have replied. One item I noticed with the 2 files was Excel trying to parse the date based on the regional settings:

    Ok, I think I see what is going on. I am noticing that some of the dates when opened in Excel under the English UK format, the AM or PM is not being listed while on other it is.

    It looks like this is Excel trying to format the dates.

    The US English date format is MM/DD/YYYY
    The UK English date format is DD/MM/YYYY

    If Excel can parse the date, it is truncating the AM/PM from the date. If it can't, then it is leaving it there. Here are 2 examples:
    9/17/2007 10:36:00 AM - In the UK format this is not parsing as there is no month 17
    12/9/2007 1:15 - This is Excel in the UK format parsing 9 September 2007 into December 12th which is incorrect as this is for 9 September.

    From what I can see & what I have been told in the other replies, if the web-site will not allow you to change the display format of the dates to match your region, you first must change your Regional Settings to match the web site, download the file.

    I have trialed this by first changing my regional settings to English US, download and saved the file after viewing the file to verify the dates appear correctly. I then closed down Excel, changed my regional settings to English UK and re-opened the file. Immediately I saw that the date formats being displayed changed from MM/DD/YYYY or DD/MM/YYYY without doing any formatting changes.

    I also started to play with the Language Bar in Windows XP (To enable the Language Bar (Right click on the Taskbar, select Toolbars, Language Bar). By using this the user can toggle between regional settings.)

    Fortunatly one of the web sites programmers found out that if you have the date format - DD-MMM-YYYY, Excel can read & parse this correctly regardless of the end users regional settings as the Month portion is spelled out.

    With this change, all seem to work!

    I wanted to add this just in case someone does a search & finds this thread, here are a few possible solutions in addition to what Hans & Legare supplied.
    Stealing from one is call plagiarism, stealing from many is called research. ;-)~

Posting Permissions

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