MyDate = Date
ActiveWorkbook.Names.Add Name:="FinYear", _
RefersTo:="=""" & Format(MyDate,"dd mmm yyyy") & """

? Activeworkbook.Names("FinYear").RefersTo
="26 Sep 2004"

Looks like it works to me. Perhaps you wanted something else.

--
Regards,
Tom Ogilvy


"Pavlos" wrote in message
...
Sorry, but I tried this and it doesnt seem to work.
I already used a similar statement higher up in my code. Only difference
is
I said
MyDate=Format(mydate,"Short Date")

Showing the text on the screen as a msgbox isn't a problem. When I try to
assign the string to a name or save to the sheet it still gives me the
serial
no 37986

I want it to say "29 February 2004" as text

I have tried these statements:
Sheets("MySheet").Range("C5").Value = strMyDate
ActiveWorkbook.Names.Add Name:="FinYear", RefersToR1C1:=strMyDate

Hope this is clear. It seems a little hard to explain!
Pavlos

"Niek Otten" wrote:

Hi Pavlos,

a$=Format(mydate,"dd mmm yyyy")

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Pavlos" wrote in message
...
In my code I have calculated the date using the DateSerial command.
This seems to work fine and gives the correct date when I use:

Debug.Print MyDate
29 Feb 2004

However I'm trying now to assign this date to a name as a string so
that I
can use it in concatenated text.
I also don't want to write the value directly to the spreadsheet, so
the
instruction I am using is
ActiveWorkbook.Names.Add Name:="FinYear", RefersTo:=MyDate

Ths is not saving the date a a string but instead is giving me a
serial no
value.

Can anybody tell me a way around this?

.- -. Bye Bye
/|6 6|\ - Pavlos
{/(_0_)\}
/ ^ \_
(/_/^\_\)