VBA date in local format

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61
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.
VBA Code:
            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

Board Regular
Joined
Feb 28, 2017
Messages
61
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.
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
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:
Rich (BB code):
Sub test()
    Range("A1:B1").NumberFormat = "mmm/jj"
End Sub
 

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61
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:
Rich (BB code):
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...
1623677083066.png
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,238
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Feb 28, 2017
Messages
61
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)
1623678018879.png
 

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61

ADVERTISEMENT

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:
1623678147621.png
 

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61
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?
VBA Code:
Sub test()

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

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

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
58,950
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,238
Office Version
  1. 2010
Platform
  1. Windows
Opislak, as so obviously the slash is not used in your local date settings so the VBA format must be
Rich (BB code):
"mmm""/""yy"
 
Solution

Forum statistics

Threads
1,141,062
Messages
5,704,057
Members
421,325
Latest member
tapete86

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top