Help with formula for extracting a date from another cell

[aallaman]

Hi Excel Geniuses!

I'm looking for an easy formula to take the long form date and time from column A and simplify it in column B. My first idea is in row 1, to simply reference A1 and format the results as a date. The second idea is to reference A2 and take just the first 10 values and return them in B2.

Any suggestions on how to do either of those things?

	A	B
1	2013-03-24T00:00:00-07:00	03/24/13
2	2013-03-24T00:00:00-07:00	2013-03-24
3

<tbody>
</tbody>

Thanks for your help!

 

[RJPotts]

What is the format of the cells in column A?

As it appears above the formula;

=DATE(LEFT(A1,4),MID(A1,6,2),MID(A1,9,2)) in B2 would work.

Best regards
Richard

 

[Teeroy]

You could use text to columns with "T" as the delimeter.
You could also do a similar thing with a formula in B1: =TEXT(LEFT(A1,SEARCH("T",A1)-1),"dd/mm/yy") which will let you specify the format you want.