Excel : date format; pulling from DB 1

[tk808]

Hello,

I have a date format issue with excel 2k7 pulling from a sql DB.

A reporting spreadsheet.xls pulls dates in the DD/MM/YYYY format. Excel thinks that this format is MM/DD/YYYY so any changes to the date display format in cell properties is wrong (days and months are switched).

Any idea on how to make Excel realize that it's dealing with the DD/MM/YYYY format?

Thanks in advance for the insight!

 

[PHV]

How is the spreadsheet pulling the dates ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Hi,

Use an UNAMBIGUOUS data format in your query.

Skip,
[sub]
[red][/red]
[/sub]

 

[tk808]

Thanks for the really quick reply guys. I'm sure the unambiguous statement will work but without access to the code, here's what can be done as a workaround:

Excel 2007:
select the column > click on the "Data" menu > "text to columns" > select default settings for steps 1 and 2 in the wizard > At step 3 select the "date' radio button > change the format to DMY.

Yes, this is a quirky workaround but it does help with large amounts of data and no quick access to code.

 

[SkipVought]

Not so quick.

You have to close the barn door before the horse is gone.

My suggestion, makes sure that the data that you are going to parse using Data/Text to columns... is in an UNAMBIGUOUS format. Without that, you are not sure, until you check.

Having said that, you can take your AMBIGUOUS mm/dd/yyyy formatted input TEXT and parse it, using Data/Text to columns, choosing the MDY PARSING format and then DISPLAY the column in a dd/mm/yyyy format or any other format you choose.

Skip,
[sub]
[red][/red]
[/sub]

 

[tk808]

Yes, the data in the DB is stored in UK date format DD/MM/YYYY. The tool we use pulls the data appropriately but with our US region systems it gets interpreted wrong as MM/DD/YYYY.

So yes, it's confirmed that the data is originally ambiguous dd/mm/yyyy format then converting to DMY it switches the DD/MM and appears to be in US date format in the xls sheet.


Thanks!

 

[SkipVought]

That is ONLY a format. CHANGE IT! Format/Cells - CUSTOM

Skip,
[sub]
[red][/red]
[/sub]