Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text Time Coversion Arithmatic | Excel Discussion (Misc queries) | |||
converting text in cell to a date time | Excel Worksheet Functions | |||
Converting text string to a its proper time format | Excel Discussion (Misc queries) | |||
How to calculate Date & Time differences | Excel Worksheet Functions | |||
Formulas dealing with text data | Excel Worksheet Functions |