value time formula help please
 

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

value time formula help please
 

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


"ferde" skrev:

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

value time formula help please
 

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 DataText to Columns
Select: DelimitedNext
Select: SpaceNext
In the Data Preview area click the word General above the last coulmn
In Column Data Format select: Do not import
Click Finish


--
Biff
Microsoft Excel MVP


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

value time formula help please
 

In C1:

=TIMEVALUE(MID(A1,FIND(" ",A1)+1,5)) and format as time
--
Gary''s Student - gsnu200823


"ferde" wrote:

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

value time formula help please
 

Thank you for the help.. very much appreciated

"ferde" wrote:

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