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

[Kevin B]

Use the following variant:

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

--
Kevin Backmann


"Brad" wrote:

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

[Dave Peterson]

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



Brad wrote:
>
> 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

--

Dave Peterson

[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