When I concatenate a text and a date in Excel, the date is converted
automatically to a number... Is there anything I can do in order to
concatenate a text and a date without losing the original formatting of the
date?

=a1&b2&" this is more text " & text(b99,"mm/dd/yyyy")



Hi_no_Tori wrote:

When I concatenate a text and a date in Excel, the date is converted
automatically to a number... Is there anything I can do in order to
concatenate a text and a date without losing the original formatting of the
date?

--

Dave Peterson

Assuming that A2 contains the text, and B2 contains the date, try
something like...

=A2&" "&TEXT(B2,"mmm d, yyyy")

Hope this helps!

In article ,
Hi_no_Tori wrote:

When I concatenate a text and a date in Excel, the date is converted
automatically to a number... Is there anything I can do in order to
concatenate a text and a date without losing the original formatting of the
date?

Convert the date to text using the TEXT function.

If the date is in A1 you could use any one of the following:

=TEXT(A1,"mm/dd/yyyy")
=TEXT(A1,"dddd, mmmm d,yyyy")

"mm/dd/yyyy" produces a text date that looks like 01/01/2003
"dddd, mmmm d,yyyy" produces a text date that looks like Sunday, September
17, 2006

There are additional formatting masks that you can use to produce dates in
the format you wish.
--
Kevin Backmann


"Hi_no_Tori" wrote:

When I concatenate a text and a date in Excel, the date is converted
automatically to a number... Is there anything I can do in order to
concatenate a text and a date without losing the original formatting of the
date?

Let's say A1 contains
9/17/2006
and B1 conatins
hello

Then in C1 enter:

=TEXT(A1,"mm/dd/yyy") & B1

to display
09/17/2006hello

--
Gary's Student


"Hi_no_Tori" wrote:

When I concatenate a text and a date in Excel, the date is converted
automatically to a number... Is there anything I can do in order to
concatenate a text and a date without losing the original formatting of the
date?

Tori,

Another solution is to leave the date in the original cell by itself, and
add your text with custom formatting. Format - Cells - Number - Custom tab:

"The date is" mm/dd/yy "per se"

Include the quote marks. Change the mmddyy codes as needed for the date
formatting you want to see. The text will appear in the cell, but the cell
will actually contain only the date. So any formulas that refer to the cell
will get the date only.

--
Earl Kiosterud
www.smokeylake.com
-----------------------------------------------------------------------
"Hi_no_Tori" wrote in message
...
When I concatenate a text and a date in Excel, the date is converted
automatically to a number... Is there anything I can do in order to
concatenate a text and a date without losing the original formatting of
the
date?