modify date time cell to an excel format date time

Hi All, I import data into excel on a regular basis in a csv format. However when the timestamp is imported it is in a format in which excel cannot recognize.
It looks like this
2012-07-19T23:00:01.01+10
or like this
2012-07-19T23:15:01+10 (milliseconds not in format this time!)
which is year month day hour minutes seconds/milliseconds + gmt offset
I don't need the gmt offset or the seconds or milliseconds which sometimes are not even present.
I have very little excel skill and its way above me. I have been able to strip it down using text to columns but this then puts it into two columns one with the date, the other with the time
Ideally I would like to click on the column and convert all the cells in that column to an excel date/time format or it happen automatically as I import the data
The timestamp is always the A column when imported.
I use a web query like this to import the data http://dbname/archive/fetch?swid=/U4...t/plain&after= 2012-01-01T00:00)
Any help would be much appreciated.
cheers rileyp

One way of doing this would be using the below formula in a blank cell on the same row and copying down.
You could then use paste special values to paste the new column back over the original.

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2))+TIME(MID(A1,12,2),MID(A1,15,2),0)

It's not a perfect solution, but it can be expanded upon.

Thanks so much for the fast and comprehensive reply. I'll give it try.

cheers rileyp

Edit. It works perfectly.
Thanks
Edit 2 I'd like to hit the star.. but where?

Hi

Another way with the data you supplied in post #1. assuming your data is in column A, in B1 and copy down:

Formula:

Please Login or Register  to view this content.

Format the cells as "dd/mm/yyy hh:mm" or whatever you require.

Hi rileyp

Select the cells to format, CTRL + 1 on the keyboard to call up the Format Cells dialogue box > Number Tab > Custom > Then in Type: "dd/mm/yyy hh:mm" (without the quotes") > OK

Format Cells.PNG

rileyp

No problem and thanks for the feed back.

Originally Posted by Kevin UK

Hi

Another way with the data you supplied in post #1. assuming your data is in column A, in B1 and copy down:

Formula:

Please Login or Register  to view this content.

Format the cells as "dd/mm/yyy hh:mm" or whatever you require.

You can eventually omit the double unary operator (--) in this case, the arithmetic operators will coerce the strings to numbers

Hi rileyp,

I've not messed around with your code too much. Just got rid of a couple of dead lines and added an 'offset' to account for the changes in row numbers for each import. Hopefully this should do what you need now.

Personally I'd have defined the last row and used that to dictate which cells should have the formula pasted into, but I'm by no means a VBA expert so didn't want to go changing all of your code for something you might not be familiar with.

S.

Please Login or Register  to view this content.

New question = new thread - Please ask a mod to move it for you