Convert string/numeric date in Excel to a date formatted object using only formulas

Folks,

I have a string date which I can convert to numeric using: =DateValue("1/2/2011") results in 39084

I'm trying to use ONLY FORMULAS to convert either the string or the numeric date into a date formatted object. Using the UI for formattin开发者_StackOverflow中文版g is not an option.

Use TEXT()

example:

=Text(DateValue("1/2/2011"), "yyyy/mm/dd")

This works:

A1 = Text("1/2/2011", "mm/dd/yyyy")
=DATE(VALUE(MID(A1,7,4)), VALUE(MID(A1,1,2)), VALUE(A1(B10, 4, 2)))