value time formula help please



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



"ferde" skrev:

T. Valko

Try these...

For the date:

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

Format as Date

For the time:


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question