Showing a calculated Date as a string
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_)\} / ^ \_ (/_/^\_\) |
Showing a calculated Date as a string
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_)\} / ^ \_ (/_/^\_\) |
Showing a calculated Date as a string
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_)\} / ^ \_ (/_/^\_\) |
Showing a calculated Date as a string
ActiveWorkbook.Names.Add Name:="FinYear", _
RefersTo:="=""" & Format(MyDate,"dd mmm yyyy") & """ -- Regards, Tom Ogilvy "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_)\} / ^ \_ (/_/^\_\) |
Showing a calculated Date as a string
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_)\} / ^ \_ (/_/^\_\) |
Showing a calculated Date as a string
Tom
Your answer was first class Thanks "Tom Ogilvy" wrote: ActiveWorkbook.Names.Add Name:="FinYear", _ RefersTo:="=""" & Format(MyDate,"dd mmm yyyy") & """ -- Regards, Tom Ogilvy "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_)\} / ^ \_ (/_/^\_\) |
All times are GMT +1. The time now is 09:14 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com