Showing a calculated Date as a string

[Guest]

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_)\}
/ ^ \_
(/_/^\_\)

 

[Niek Otten]

Hi Pavlos,

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

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

 

[Tom Ogilvy]

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

 

[Tom Ogilvy]

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.

 

[Guest]

Tom
Your answer was first class
Thanks

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