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

Question

People,

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

I am trying to use ONLY FORMULAS to convert a string or numeric date to a date-formatted object. Using a user interface for formatting is not an option.

Answer 1

Use TEXT ()

Example:

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

Answer 2

It 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)))