concatonate and format the date

[mart10]

I am using Excel 2003

I am using the following in a worksheet cell by referencing the data in another sheet :

=CONCATENATE("Headroom* per ",'Settings & Methodology'!$B$3+7," analysis")

I get the following :

Headroom* per 39846 analysis

I want to see :

Headroom* per 02-Feb-2009 analysis

How can I format the date part

 

[SkipVought]

Hi,

[tt]
=CONCATENATE("Headroom* per ",'Settings & TEXT(Methodology'!$B$3+7,"dd-mmm-yyyy")," analysis")
[/tt]
faq68-5827

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mart10]

doesnt like that . The 'Settings & Methodology' is the name of the sheet. Your syntax looks strange?

 

[SkipVought]

did you try to fix it???
[tt]
=CONCATENATE("Headroom* per ",TEXT('Settings & Methodology'!$B$3+7,"dd-mmm-yyyy")," analysis")
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[mart10]

ah yes, I missed a ' when rearranging your sysnatx to your 2nd post. Your 2nd one works

thanks

 

[combo]

.. or use custom format:
"Headroom* per "dd-mmm-yyyy "analysis"
Having ='Settings & Methodology'!$B$3+7 in the cell you will stay with date value behind.


combo