Thread: Changing time format without losing data

I have a worksheet that has data in the format of
1/1/06 1330. Representing the date and time
I need the data to be formatted as
1/1/6 13:30
so that I can use functions to calculate the difference between cells in time.

I tried using the formulate cells command and it replaces all of the data with zeros, but in the correct format.

Any help is appreciated. A macro, a copy and paste work around a formula??

Thanks, lj

Hi,

I am assuming all your data is formatted as Text ... right ?

Carim

Assuming your value is in A2, then in B2 (for example) enter:

=DATEVALUE(LEFT(A2,FIND(" ",TRIM(A2))))+TIMEVALUE(LEFT(RIGHT(A2,4),2)&":"&RIGHT(A2,2))

Select the custom format dd/mm/yy hh:mm (or mm/dd/yy hh:mm if not UK)

Note that this might fall over if the input format changes too much...

Mark.

I believe my cells are formatted as text. How can I check this?

I tried the formula above and it did not work. All of my date/time data is in one cell, so I'm not sure if that would make a difference.

lj

Well it makes a big difference ...
all the data is in one text cell and as Scott pointed out with a variable structure ...

Could you upload a zipped version of your file ?

HTH
Carim

I can't upload zip files, I don't have that ability on this computer.

I could just cut it to a few lines of data and upload it that way?

Where would I upload it?

lj

Okay, the file has been uploaded to
Savefile.com

The data I am looking at is in column B and D.

Some of the data looks properlly formatted. I used the find and replace for these.

That is a direct link to the file, I just called it Savefiles.com

Here it is again.
www.savefiles.com/files/279197

http://cjoint.com/data/lvvI4kDwr6.htm

try this.

This only worked for cells where I had used the find and replace to add the ":"

Look at line 45 to see the way the data was originally entered.

Thanks
lj