Concatenate data that includes dates

D

dave caizley

I have a workbook that provides for a cell to have a date entered as part of
the approval process. I then want to include that date cell in a
concatenation with two otther cells to form a file name and then possibly by
way of a macro to copy and paste special that data so that it forms a text
string that someone can paste into an e-mail heading.

Every way I have tried it, the date always reverts to its core number format.

Am I trying to achieve the impossible witthin Excel 2003

Thanks in anticipation
 
M

Max

Think you can use TEXT to specify the format

Eg, in C2: =A2&" "&TEXT(B2,"dd-mmm-yyyy")
where B2 contains the date data in the concat

Adapt the format to suit
 
D

dave caizley

Hi Max

Thanks for the response. I wasnt aware of that method of concatenation so I
have learnt something. However, Excel wouldnt accept the formula. I have
tried to reformat the cell so that it gives that same date format as your
formula but it still wouldnt accept it.

Regards
 
D

dave caizley

Hi Max

I tried it once again outside of my worksheet and it does in fact seem to
work. I think I just need to clear the standard date formatting from my
worksheet and accept what is in the formula. Thanks for the help
 
M

Max

It should work as long as B2 contains a real date.

Maybe try it with a little coercion on B2:
=A2&" "&TEXT(B2+0,"dd-mmm-yyyy")

To clarify the line:
Adapt the format to suit
Click to expand...
actually means that you could adapt the date format suggested within the
TEXT to another that's actually desired in the concat string. It also doesn't
have to be the same date format as that in the source B2.
 
Top