VBA date in local format

[opislak]

Hello all,

As a European, my keyboard layout is set by default to show dates with '-' as in 11-06-2021 or jun-21
I would like to modify the '-' by a slash '/', to show 11/06/2021 or jun/21
The code I use doesn't work.

            Range("A:A").NumberFormat = "dd/mm/yyyy  hh:mm"
            Range("B:B").NumberFormat = "mmm/yy"

this code does not modify the date. It still shows jun-21
Any help is welcome, thank you !
Patrick.

 

[opislak]

Hi, is your locale setting expecting different letters to designate the month and year other than "m" or "y".

Some languages for example, use the letter "j" for the year.

Dear FormR,

That is correct, I am a dutch speaking Belgian and my Excel uses "j" instead of "y" for the year. I don't know why because all my settings are in English.

 

[Joe4]

Does column D not already show "Jun/21"?
Isn't that what you want?

That is correct, I am a dutch speaking Belgian and my Excel uses "j" instead of "y" for the year. I don't know why because all my settings are in English.

I don't have that setting, but I am guessing if you are using "j" instead of "y" for year, maybe you need:

Sub test()
    Range("A1:B1").NumberFormat = "mmm/jj"
End Sub

 

[opislak]

Does column D not already show "Jun/21"?
Isn't that what you want?


I don't have that setting, but I am guessing if you are using "j" instead of "y" for year, maybe you need:

Sub test()
    Range("A1:B1").NumberFormat = "mmm/jj"
End Sub

No, Joe4, I tried that too, and this gives a very funny result... it shows "jun-jj". So the year is not shown, and it remains a dash "-", not a slash"/". Funny but true...

 

[Joe4]

OK, I think VBA may use American standards, regardless of regional setting.

However, going back to what I said at the beginning of my last post, if I go to your link, and just look at the image without downloading it, it already appears to show column D in the format that you desire ("June/21"). Does it appear that way for you too?

I thought that is what you want, so I am a bit confused as to where the issue lies.

 

[Marc L]

Hi,​

​

in fact he does not want a local format but just a layout with a slash so this slash must be between double quotes in the cell format …​

 

[opislak]

OK, I think VBA may use American standards, regardless of regional setting.

However, going back to what I said at the beginning of my last post, if I go to your link, and just look at the image without downloading it, it already appears to show column D in the format that you desire ("June/21"). Does it appear that way for you too?

I thought that is what you want, so I am a bit confused as to where the issue lies.

Joe4,

No, it seems that we see different things. I tried to insert a screen shot of my Excel including the macro, as I see my window. But MrExcel says the image is too large. I will try to upload in multiple parts... Sorry for the inconvenience.
This is what I see: (upper part of the screen)

 

[opislak]

Joe4,

No, it seems that we see different things. I tried to insert a screen shot of my Excel including the macro, as I see my window. But MrExcel says the image is too large. I will try to upload in multiple parts... Sorry for the inconvenience.
This is what I see: (upper part of the screen)

Second part of my screen:

 

[opislak]

Hi,​

​

in fact he does not want a local format but just a layout with a slash so this slash must be between double quotes in the cell format …​

Dear MarcL

Do you mean like this?

Sub test()

    Range("D:D").NumberFormat = "mmm" & "/" & "yy"

End Sub

That doesn't work either. It still gives "jun-21"...

 

[Joe4]

No, it seems that we see different things. I tried to insert a screen shot of my Excel including the macro, as I see my window. But MrExcel says the image is too large. I will try to upload in multiple parts... Sorry for the inconvenience.
This is what I see: (upper part of the screen)

Insteresting, I see "Jun/21" on the DropBox site, even before I download the file.
So we are seeing different things. So it appears I cannot replicate your issue.

 

[Marc L]

​

Opislak, as so obviously the slash is not used in your local date settings so the VBA format must be

"mmm""/""yy"

​