showing a date as text in Concatenate

[Brad]

For this formula:

=CONCATENATE(BB201, " on ",BE201)

where BE201 is a cell with either a date, or a date and time,

my problem is that while the value in the BE201 cell itself shows up
formatted as a date and not the Excel value for the date, when I grab it as
the second part of my concatenate string, it shows up as the numeric date
value.

Is there a way to have BE201 show up as text, say, 01/20/05 instead of
38747???

I have fiddled around with a few ideas, none of which seems to work. My
thanks in advance for thoughts or a solution.

Cheers! Brad

 

[Guest]

Use the following variant:

=CONCATENATE(BB201, " on ",TEXT(BE201,"mm/dd/yyyy"))

 

[Dave Peterson]

=concatenate(bb201, " on ", text(be201,"mm/dd/yyyy"))
or
=bb201&" on "&text(be201,"mm/dd/yyyy")

 

[Carim]

Hi Brad,

You are right Text() and Format() do not work ...
I can only get it to work that way ...

=CONCATENATE(BB201, " on ",DAY(BE201)&"/"&MONTH(BE201)&"/"&YEAR(BE201))

HTH
Cheers
Carim