excel date/time formatting query - boards.ie
Boards.ie uses cookies. By continuing to browse this site you are agreeing to our use of cookies. Click here to find out more x
Post Reply  
 
Thread Tools Search this Thread
01-05-2012, 22:12   #1
rude awakening
Registered User
 
rude awakening's Avatar
 
Join Date: Dec 2008
Location: The Dark Side
Posts: 618
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
rude awakening is offline  
Advertisement
01-05-2012, 23:12   #2
fionny
Registered User
 
Join Date: Jun 2007
Location: Cork
Posts: 1,987
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?
fionny is offline  
01-05-2012, 23:12   #3
Victor
Moderator
 
Victor's Avatar
 
Join Date: Dec 2000
Location: Dublin
Posts: 64,748
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.
Victor is offline  
01-05-2012, 23:13   #4
fionny
Registered User
 
Join Date: Jun 2007
Location: Cork
Posts: 1,987
Quote:
Originally Posted by Victor View Post
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.
fionny is offline  
01-05-2012, 23:22   #5
Victor
Moderator
 
Victor's Avatar
 
Join Date: Dec 2000
Location: Dublin
Posts: 64,748
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.
Victor is offline  
Post Reply

Quick Reply
Message:
Remove Text Formatting
Bold
Italic
Underline

Insert Image
Wrap [QUOTE] tags around selected text
 
Decrease Size
Increase Size
Please sign up or log in to join the discussion

Thread Tools Search this Thread
Search this Thread:

Advanced Search



Share Tweet