Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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_)\} / ^ \_ (/_/^\_\) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date showing incorrect. 30:00 hrs showing 06:00 | Excel Discussion (Misc queries) | |||
Date showing as numeric value in a text string formula | Excel Discussion (Misc queries) | |||
Calculated Textbox not showing proper results. | Excel Discussion (Misc queries) | |||
How can a calculated value be displayed wiyhin a text string | Excel Discussion (Misc queries) | |||
Calculated Fields showing error results | Excel Discussion (Misc queries) |