Time as text conversion problems

[FraserKitchell]

Hi there,

I am having difficulty converting a time text string to a time format. Currently the time stamp is as follows:

2/8/10 11:00:00 AM PST
2/8/10 11:05:00 AM PST

I would like the data to be in two columns, the first a date only

2/8/10

and the second a full timestamp



11:00:00 AM (formatted for time, further functions to be driven off this time)

Problem is I cant make a few things happen: 1) data to columns with a space delimiter is great, but I cannot get the AM back into the formula and get to a correct timestamp.
2) I am unable to us a replace, concatentate, && type formulas it seems, Although promising, I also cant figure out a timevalue, etc type formula.

Let me know if you can think of something to work, happy solving,
Fraser

[zyzzyva57]

Select the Cell
Then under Data Tab, in the Data Group, select "Text to Columns" and then follow the Wizard

[FraserKitchell]

The fact that splitting this into columns isnt working for this particular time stamp is the problem. I've tried doing using that capability in many ways and none will quite do the job. Problem arises because I have to split on a space delimiter (can't do fixed widths).

Basically I need a way to convert a text date to a number.

[BjornS]

Hi there!
Here is a "corrected" solution, that i hope works.
Please check it out!

Note that there are three worksheets in the file!

Kind regards
Bjorn

[FraserKitchell]

Wow three great solutions...this works! Thanks Bjorn, I appreciate that.