converting general text to date time

im trying to extract time date from:

05:20:2006:18:45:50

i could use the mod function but the data is not in date time format.

How to i convert the above into date time format so that i can extract
the time data from it?

thanks

One way with the string in A1

=--MID(SUBSTITUTE(A1,":","^^",3),FIND("^^",SUBSTITUTE(A1,":","^^",3))+2,255)


note that you have to format result as time like hh:mm:ss or [hh]:mm:ss

--

Regards,

Peo Sjoblom

Excel 95 - Excel 2007
Northwest Excel Solutions
www.nwexcelsolutions.com
"It is a good thing to follow the first law of holes;
if you are in one stop digging." Lord Healey


<meyerelie@gmail.com> wrote in message
news:1148237572.325219.297130@j33g2000cwa.googlegroups.com...
> im trying to extract time date from:
>
> 05:20:2006:18:45:50
>
> i could use the mod function but the data is not in date time format.
>
> How to i convert the above into date time format so that i can extract
> the time data from it?
>
> thanks
>

You could use TTC (Text to Columns).

Select the data, then:
<Data> <TextToColumns>, and click on "Fixed Width", then <Next>,

In the "Preview Window", click just in *front* of the "18",
So that the break line leaves the colon in the first column, and the second
column begins with the "18".
Then <Next>

In this last step of the Wizard, the first column (date) is selected by
default, so click on "Do Not Import".
You'll see the column header change to "Skip".
Now, click in the second (time) column to select it.
Then change the address in the "Destination" box from the default reference
(original data location), to an empty starting cell where you'd like the
times to end up.
Then click <Finish>.

Your original data will remain untouched, and the 'time' data will be in the
column you chose in the destination box.
Now, you can choose a format for how you wish to display the time data.

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

<meyerelie@gmail.com> wrote in message
news:1148237572.325219.297130@j33g2000cwa.googlegroups.com...
> im trying to extract time date from:
>
> 05:20:2006:18:45:50
>
> i could use the mod function but the data is not in date time format.
>
> How to i convert the above into date time format so that i can extract
> the time data from it?
>
> thanks
>