US versus UK dates in Excel

[HairyHippy]

I have a spreadsheet that has US dates. They are in the format MM/DD/YY but when I open the file in UK Excel it thinks this is text and not a date. I don't seem to be able to convert these to dates or to UK dates.

Has anyone got any ideas as to why this would be and a potential solution?

 

[GlennUK]

Is the file that you're opening really a spreadsheet?

The behaviour is the type of thing I've encountered when opening text files.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[HairyHippy]

Good point Glenn.

It's actually a download from Business Objects. So I assume that it's doing some sort of conversion.

So basically how do I get Excel to realise that these are dates and not text in the format 05/20/06 for example?

 

[GlennUK]

Are you downloading directly into Excel, or via a text or CSV file? If you are downloading directly into Excel, can you download to a text file first, and then open that in Excel instead?

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[HairyHippy]

The way it works I believe, I just get sent the file, is that in Business Objects one uses Copy All from the edit Menu and then one just pastes this into Excel.

 

[xlbo]

Just format the dates in the BOBJ report to be UK format then - should come across fine. If not, you may need to change the default setting in your .SBO file from mm/dd to dd/mm

As a point of interest - you very rarely actually need to use Business Objects - you can just put the SQL into MSQuery and return the results directly into excel instead...

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question