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?

score 2 · Accepted Answer · answered Nov 21 '08 at 00:40

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"

score 0 · Answer 2 · answered Oct 18 '13 at 19:38

In the VBA editor, try setting the

WebDisableDateRecognition

property inside the standard web query command :

With Selection.QueryTable
    .Connection = _
    "URL;http://whateverYourURLis"
    .WebSelectionType = xlSpecifiedTables
    .WebFormatting = xlWebFormattingNone
    .WebTables = "7"
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = True
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False
End With

(Notice you can get the same as above by simply recording a macro while making the web query...)

Worked well here, hope it does for you too.

Cheers!

score 0 · Answer 3 · answered Nov 19 '08 at 20:22

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.

score 0 · Answer 4 · edited Jun 03 '12 at 04:15

0

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.

edited Jun 03 '12 at 04:15

Siddharth Rout

147,039
17
206
250

answered Nov 19 '08 at 21:11

AlanR

1,162
4
14
26

Internet Language options are not an issue here at all, you can ignore this setting. Disabling date recognition may be your only chance, why is it not an option? – Tomalak Nov 20 '08 at 09:37
BTW you really should use comments (and not answers) as long as you are not answering your own question. – Tomalak Nov 20 '08 at 09:57

score 0 · Answer 5 · edited May 23 '17 at 11:51

0

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.

edited May 23 '17 at 11:51

Community

1
1

answered Nov 26 '08 at 22:24

GSerg

76,472
17
159
346

score 0 · Answer 6 · answered Nov 24 '10 at 15:17

0

In Excel 2007, Changing the date format in regional setting worked fine.

answered Nov 24 '10 at 15:17

iftee

369
1
13

Excel 2002 Web Query screwing up en-GB dates

6 Answers6