Date reference to another cell

[Peter Bircher]

Hi all
[Excel 2010 on windows 7]

I am having a problem with the following:

On a sheet called Client Details in cell B23 is a date, 31 October 2011

In a cell on another sheet I want this to show:
The agreement is scheduled to commence on 31 October 2011 including
providing the management representation letter

I inserted this:
="The agreement is scheduled to commence on " & format('Client
Details'!B23,"dd MMM yyyy") & " including providing the management
representation letter."

This is what I get
#NAME?

If I don't format the the 'Client Details'!B23 I get a Julian date number,
40847 not very useful!

Please can somebody help

Thanks
Peter
www.automatexcel.co.za

 

[merjet]

Replace
format('Client Details'!B23,"dd MMM yyyy")
with
MONTH('Client Details'!B23) & "/" & DAY('Client Details'!B23) & "/" &
YEAR('Client Details'!B23)

 

[Peter Bircher]

Thank you soooo much Merjet
Works perfectly!

Peter
AutomateXcel

"merjet" wrote in message

Replace
format('Client Details'!B23,"dd MMM yyyy")
with
MONTH('Client Details'!B23) & "/" & DAY('Client Details'!B23) & "/" &
YEAR('Client Details'!B23)