Changing time format without losing data

**lajones3** · 11-21-2006, 01:59 PM

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

**Carim** · 11-21-2006, 02:03 PM

Hi,

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

Carim

**Mark@Work** · 11-21-2006, 02:27 PM

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.

**lajones3** · 11-21-2006, 03:36 PM

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

**Carim** · 11-21-2006, 03:47 PM

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

**lajones3** · 11-21-2006, 03:52 PM

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

**VBA Noob** · 11-21-2006, 03:57 PM

Hi,

Uploaded it to

http://www.savefile.com/

VBA Noob

**lajones3** · 11-21-2006, 04:04 PM

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.

**VBA Noob** · 11-21-2006, 04:25 PM

You need to post the link it gives you for file

VBA Noob

**lajones3** · 11-21-2006, 04:27 PM

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

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

**VBA Noob** · 11-21-2006, 04:33 PM

Not working mate

Try http://cjoint.com/

VBA Noob

**lajones3** · 11-21-2006, 04:35 PM

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

try this.

**VBA Noob** · 11-21-2006, 04:51 PM

Hi,

The link should of being

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

Column B and D ar both formatted as Date and Time. There is no times as far as I can see formatted as 1300 instead of 13:00

The below will change 11/15/06 4:30 into 11/15/06 04:30 for you

=DATE(YEAR(B2),MONTH(B2),DAY(B2))+TIME(HOUR(B2),MINUTE(B2),SECOND(B2))

If you want the date and time in Col G and H use

=DATE(YEAR(B2),MONTH(B2),DAY(B2))

and

=TIME(HOUR(B2),MINUTE(B2),SECOND(B2))

VBA Noob

**lajones3** · 11-21-2006, 04:59 PM

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

**VBA Noob** · 11-21-2006, 05:18 PM

Hi,

Try this in G2 and drag down. Then select column and paste special as values and format as required

=IF(ISERROR(DATE(YEAR(B2),MONTH(B2),DAY(B2))+TIME(HOUR(B2),MINUTE(B2),SECOND(B2))),LEFT(B2,FIND(" ",B2))&SUBSTITUTE(RIGHT(B2,4),RIGHT(B2,2),":"&RIGHT(B2,LEN(B2)-FIND(" ",B2)-2)),(DATE(YEAR(B2),MONTH(B2),DAY(B2))+TIME(HOUR(B2),MINUTE(B2),SECOND(B2))))
VBA Noob

**lajones3** · 11-21-2006, 05:34 PM

Were you able to get this to work?

I tried and have been unsuccessful. I pasted the formula into G2 then copied it and "drug down". Then I copied column G and pasted special into column H. I had already used the Format cells command for column H to get the date format that I want.

I even tried formatting the cells after I entered the data with no luck.

Thanks, lj

**Carim** · 11-21-2006, 05:36 PM

There is absolutely no consistency in your data ...
Try to normalize it beforehand ...

HTH
Carim

**lajones3** · 11-21-2006, 05:37 PM

Would it be easier if I separated the data and had date and time in different columns. Then once I had it formatted correctly, merge the columns into one?

The lack of consistency is from trying so many different ways to format these cells. I hate to revert back to the original document and lose all of the cells that are formatted correctly, because those I used find and replace (time consuming). This data is also coming from a report writing program and being brought into Excel, so I have limited ability on how to format before the data is entered.

If you can help me with this I would gladly run the report and reimport it into Excel, if it would help.