Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
 
Posts: n/a
Default 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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Peo Sjoblom
 
Posts: n/a
Default converting general text to date time

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


wrote in message
oups.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



  #3   Report Post  
Posted to microsoft.public.excel.misc
Ragdyer
 
Posts: n/a
Default converting general text to date time

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 !
---------------------------------------------------------------------------

wrote in message
oups.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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Text Time Coversion Arithmatic ZeroWayCool Excel Discussion (Misc queries) 1 April 2nd 06 02:52 PM
converting text in cell to a date time Herman Excel Worksheet Functions 3 October 22nd 05 07:18 PM
Converting text string to a its proper time format Edmund Wong Excel Discussion (Misc queries) 3 October 21st 05 12:37 AM
How to calculate Date & Time differences robs Excel Worksheet Functions 2 October 4th 05 04:22 PM
Formulas dealing with text data Bagia Excel Worksheet Functions 6 June 20th 05 10:29 PM


All times are GMT +1. The time now is 03:10 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"