Boards.ie

rude awakening · 01-05-2012, 22:12

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 · 01-05-2012, 23:12

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** · 01-05-2012, 23:12

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 · 01-05-2012, 23:13

Quote:

Originally Posted by 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.

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** · 01-05-2012, 23:22

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.

01-05-2012, 22:12	#1
rude awakening Registered User Join Date: Dec 2008 Location: The Dark Side Posts: 486 Adverts \| Friends	excel date/time formatting query 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

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search

01-05-2012, 23:12	#2
fionny Registered User Join Date: Jun 2007 Location: Cork Posts: 1,807 Adverts \| Friends	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?

01-05-2012, 23:12	#3
Victor Moderator Join Date: Dec 2000 Location: Dublin Posts: 60,940 Adverts \| Friends Mod: Accommodation & Property, Commuting & Transport, Infrastructure	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.

01-05-2012, 23:22	#5
Victor Moderator Join Date: Dec 2000 Location: Dublin Posts: 60,940 Adverts \| Friends Mod: Accommodation & Property, Commuting & Transport, Infrastructure	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.