excel date/time formatting query

rude awakening

Hey Guys
Just wondering if anyone knows a shortcut and/or formula to do this:
I have a file downloaded with around 11000 entries, The date and time is in US format i.e:
02/19/2011 15:30:00
02/20/2011 14:30:00
03/01/2011 15:30:00

I need to convert this to UK format i.e.:
19/02/2011 15:30:00
20/02/2011 14:30:00
01/03/2011 15:30:00

I have done a google search but can only find formulas to change cells with just the date.

Thanks a mill
RA

fionny

Its a tricky one ive tried it for you using text to columns but there seems to be some random quirk that after you have re-assembled the dates and pasted them inot a formatted column you still need to click into the cell and press enter for it to take effect..

Which is obviously not an option with lots of cells... would you consider keeping the time in a seperate column to the dates?

Victor

Do not change the data, as it is already correct.

Simply change the formatting - right click - format - number - data & time and then select the correct format.

It is somewhat unusual to have date and time in the same column, but not really problematic.

fionny

Victor wrote: »

Do not change the data, as it is already correct.

Simply change the formatting - right click - format - number - data & time and then select the correct format.

Doesnt work Victor I was going to suggest the same but excel doesnt see it as a date, even if you tell it to format it as the american format first. Tried it myself.

Victor

Is this homework?

I suspect the problem is the non standard (date and time in one column) formatting.

Potentially add two new columns, one for date, one for time.

Use the @round function to calculate the date in whole days. Then subtract one from the other to get the time (expressed as a part of a day). Format the new columns appropriately.

Possibly add a check column to make sure that it all adds up properly.