vba help - date format

[Mallesh23]

Hi Team,

I want date format like this. 9. Sep 2020 (Month No & "." & Sep & yyyy")

Date formula gives date :=> 12/09/2020 , how to achieve it. thanks for your help in advance.



Thanks
mg

 

[Peter_SSs]

You could just format the date once.

Format(Date, "yyyy\\m. mmm yyyy\\Re\co\n\\dd.mm.yyyy") & "\abc.xlsx"

 

[Mallesh23]

Hi Peter,

Thanks once again for your help, its working but I have two questions

Format(Date, "yyyy\\m. mmm yyyy\\Re\co\n\\dd.mm.yyyy") & "\abc.xlsx"


why double slashes
In above code Recon is a folder name, if I want to pass variable in its place say ------> " & str_Recon & "

what will be code.


Thanks
mg

 

[Peter_SSs]

why double slashes

I suppose the simple answer is that if you don't, then you don't get a slash generated. \ is a special character in the Format function. Details here

In above code Recon is a folder name, if I want to pass variable in its place say ------> " & str_Recon & "

Then the simplest way would be to split my statement and use two Format() functions

Format(Date, "yyyy\\m. mmm yyyy\\") & strRecon & Format(Date, "\\dd.mm.yyyy") & "\abc.xlsx"

 

[Rick Rothstein]

Maybe something like the below, not sure if you need "9.Sep 2020" or "9. Sep 2020" with a space after the Month number

Format(Date, "yyyy") & "\" & Format(Date, "m. mmm yyyy") & "\Recon\" & Format(Date, "dd.mm.yyyy") & "\abc.xlsx"

Probably a little more confusing to read, but you can shorten that code snippet to this...

Format(Date, "yyyy\\m. mmm yyyy""\Recon\""dd.mm.yyyy""\abc.xlsx""")

EDIT NOTE: Just noticed this is similar in concept to what Peter posted in Message #11.

 

[Mallesh23]

Hi Rick,

Thanks for giving one more option.?


Thanks
mg