Concatenating Date Field with Text Field

[Ann]

I am trying to concatenate a date value ex. 07/07/2003
with text ex. Job 1 Finished. When I use the concatenate
function it changes the date to the numerical value.

Is there any way I can concatenate a date to text and keep
the date value? I want my new field to look like
"07/07/2003 Job 1 Finished" not "37809 Job 1 Finished".

The reason I want to do this is that I ran out of
resources in an Excel pivot (too many rows or columns. I
can get around that by combining a couple of my fields
together. I can do this before I bring my data into Excel
but would like to know if there is a way to accomplish the
date and text combining in Excel.

Thank you for any help with this.
Sincerely,
Ann

 

[Bob Phillips]

Ann,

Assuming the date is in A1, use
=TEXT(A1,"dd/mm/yyy")&" Job 1 Finished"

 

[Gord Dibben]

Ann

=TEXT(A1,"mm/dd/yyyy")& " Job 1 Finished"

Where A1 contains the date.

Gord Dibben Excel MVP - XL97 SR2 & XL2002

 

[tom]

Use the text function.

If A2 = 'Job 1 Finished
and A3 = 07/07/03

then the formula you need is =a2&" "&text(a3, "dd-mm-yy")

 

[Ian Harris]

Try this, where A1 is the date and B1 is the narrative.

=CONCATENATE(TEXT(A1,"dd/mm/yy"),B1)

 

[vcastro]

Concatenate dates

I have a problem with excel.

On a report I have a cover that has a text that says:

"Enclosed you will find the report for the (name of the job) performed at (location) on (date)"

I used concatenate to get "name of the job", "location" and "date" (using the text function").

This works perfect if the date of the assigment is one day only, however I cannot find a way to do it when the job takes longer than a day.

The report has the cover and 2 more spreadsheets one for the beggining and one for the final the cover is linked to these 2 spreadsheets.

I would like to find a way that allows me using concatenate to say for instance:
"performed at x on December 30, 2007" if the job takes one day, or
if the job takes more than one day"
"performed at x on December 30 to 31, 2007" or "performed at x on December 31, 2007 to January 01, 2008"