Excel 2002 Web Query screwing up en-GB dates

Question

Good afternoon,

I have a web query in Excel 2002 going against a web page that returns a date column. The dates are returned as DD/MM/YYYY, as I would like to show them in my spreadsheet. My machine running Excel has its regional settings set to en-GB, and the only language set under Internet Options is UK English.

Nevertheless, the web query pulls the dates as MM/DD/YYYY, which is misinterpreted by Excel.

How can I get the Web Query to return the dates in my regional settings, MM/DD/YYYY?

Regards, Alan.

Answer 1

Maybe you are lucky and Excel understands either the HTML lang attribute or <meta http-equiv="content-language" content="..."> meta tag. To be honest - my guess is that it doesn't make a difference.

If you have control over the web site, you can try to add them and see if it makes a difference. OTOH - if you had control over the web page, you could change the date format.

The alternative would be to disable date recognition entirely and post-process it by hand after the query. To do this, recreate the web query and in the "New Web Query" dialog box, click the "Options..." button in the upper right. There is a "Disable date recognition" checkbox.

Answer 2

The webpage format is en-GB. I changed my regional settings in the Control Panel to en-GB. I changed my Internet Options language to en-GB.

Still, Excel's Web Query returns data in en-US. I don't see how any of what you suggested would help. In this case post-processing is not an option.

-Alan.

Answer 3

Can you modify the "web query"?

The universal date format is the better way for Office products to recognise dates/times. I've had similar problems working with GB to US dates and found that coding your dates into this format saves you a lot of trouble.

Universal Date format is : "yyyy-mm-dd hh:mm:ss"

Answer 4

Excel web query is awful in terms of international stuff. It didn't work for me either. What I finally had to do is just pull in the data manually, with the macro, parse it locale-wise and put onto the sheet. Fortunately, I queried a web service that returned XML so it was easy. If you parse HTML, do what Tomalak suggested.