value time formula help please

[ferde]

A--------------------------B-------------------C
OLD DATA NewDate NewTime
8/19/2008 16:00 PDT 8/19/08 16:00
12/30/2008 0:00 PST 12/30/08 #VALUE!
12/31/2008 3:15 PST 12/31/08 #VALUE!
12/29/2008 23:15 PST 12/29/08 #VALUE!
12/30/2008 8:30 PST 12/30/08 #VALUE!

NewDate formula=VALUE(LEFT($A2,FIND(" ",$A2,1)))
NewTime formula=VALUE(MID($A2,(FIND(" ",$A2,1))+1,FIND(" ",$A2,1)-4))

Can someone help me with a formula that will return split up (column A
--date/time) into two columns where the date is in column B and the time is
in column C. The formula I am using is returning a value error for the time.

Thank you for your help

 

[excelent]

B1=--SUBSTITUTE(A1,RIGHT(A1,4),"")
C1=TIME(HOUR(B1),MINUTE(B1),SECOND(B1))


"ferde" skrev:

 

[T. Valko]

Try these...

For the date:

=--LEFT(A2,FIND(" ",A2))

Format as Date

For the time:

=MOD(LEFT(A2,LEN(A2)-3),1)

Format as Time

You could even do this without formulas...

Select the range in question. Assume it's A1:A10. Make sure B1:B10 is empty
to recieve the extracted time. You can enter a new empty column B if you
need to. Then:

Goto the menu Data>Text to Columns
Select: Delimited>Next
Select: Space>Next
In the Data Preview area click the word General above the last coulmn
In Column Data Format select: Do not import
Click Finish

 

[Gary''s Student]

In C1:

=TIMEVALUE(MID(A1,FIND(" ",A1)+1,5)) and format as time

 

[ferde]

Thank you for the help.. very much appreciated