Date showing as numeric value in a text string formula

[Guest]

Cell [A1]
= " The " &TEXT(C1,"$#,###") & " widgets are for sale. However they expire
on " &$D$5

Cell [C1} = 5,000
Cell [D5] = 05/05/07
This cell is in date format.

Cell [A1] value (results) show up in cell [A3].

I can't get [A3] to show the date in date format. It shows up as 39207. Show
below:

The 5,000 widgets are for sale. However they expire on 39207.

 

[Peo Sjoblom]

Do the same as you did for the widget cell and wrap it in the TEXT function
but use "mm/dd/yy" instead

I noticed that you have a dollar sign in the TEXT function in the formula
you posted, if these are number of widgets then you should remove the dollar
sign

 

[Dave Peterson]

....expire on " & text($d$5,"mm/dd/yyyy") & "."

Cell [A1]
= " The " &TEXT(C1,"$#,###") & " widgets are for sale. However they expire
on " &$D$5

Cell [C1} = 5,000
Cell [D5] = 05/05/07
This cell is in date format.

Cell [A1] value (results) show up in cell [A3].

I can't get [A3] to show the date in date format. It shows up as 39207. Show
below:

The 5,000 widgets are for sale. However they expire on 39207.

 

[T. Valko]

You have use another TEXT function:

= " The " &TEXT(C1,"$#,###") & " widgets are for sale. However they expire
on " &TEXT($D$5,"mm/dd/yy")

 

[Gord Dibben]

= " The " &TEXT(C1,"$#,###") & " widgets are for sale. However they expire on "
&TEXT($D$5,"mmmm d yyyy")


Gord Dibben MS Excel MVP