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

 

[Momentman]

Could this be what you need

Format(TheDate,"dd. mmm yyyy"

 

[JoeMo]

You want this for today's date? If yes:

Sub dateFormat()
MsgBox Month(Date) & "." & MonthName(Month(Date), True) & " " & Year(Date)
End Sub

 

[Rick Rothstein]

Format(TheDate,"dd. mmm yyyy"

That is the answer I would have given except I think the OP would only want to use one "d" (based on his posted example) and I am not sure about the space character (his example shows it but his description in parentheses doesn't show it.

 

[Peter_SSs]

Based on description & example, I think the OP doesn't want 'd' at all but more what JoeMo has offered.

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.

So perhaps this?

Format(TheDate, "m. mmm yyyy")

 

[Momentman]

That is the answer I would have given except I think the OP would only want to use one "d" (based on his posted example) and I am not sure about the space character (his example shows it but his description in parentheses doesn't show it.

Spot on @Rick Rothstein , it should be a "d", I could have sworn that I saw "09 Sep"

 

[Momentman]

Based on description & example, I think the OP doesn't want 'd' at all but more what JoeMo has offered.

So perhaps this?

Format(TheDate, "m. mmm yyyy")

Very possible. if he didn't mean to type 12. Sep 2020. I guess only the OP can tell us what he really needs

 

[Mallesh23]

Hi Team,

Thank you all for your help, really helpful. My mistake I didn't put actual requirement clear.

Actual folder structure is :==> 2020\9. Sep 2020\Recon\06.09.2020\abc.xlsx

Through calender user select date , system date dd/mm/yyyy
From above folder structure I have to pick abc file.

a)Year
b)month no, Month Name , Year
c)Recon-----> Constant
d) 06.09.2020 ----> each day folder
e) abc.xlsx actual file.


Thanks
mg

 

[Momentman]

Hi Team,

Thank you all for your help, really helpful. My mistake I didn't put actual requirement clear.

Actual folder structure is :==> 2020\9. Sep 2020\Recon\06.09.2020\abc.xlsx

Through calender user select date , system date dd/mm/yyyy
From above folder structure I have to pick abc file.

a)Year
b)month no, Month Name , Year
c)Recon-----> Constant
d) 06.09.2020 ----> each day folder
e) abc.xlsx actual file.


Thanks
mg

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"

 

[Mallesh23]

Hi Momentman ,Peter_sss, Rick and .JoeMo

Perfect ! Thank you all for your help it worked, learned something new. ?




Thanks
mg