VBA date in local format

opislak

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

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
If changing the date format does not change the data, then it means that your data is probably entered as text, and not as a valid date.
Changing the formatting only works on numeric or date entries, not text ones. You would need to convert the text to valid dates.
You can do that with "Text to Columns", to "re-enter" a whole column at a time.
 
Upvote 0
If changing the date format does not change the data, then it means that your data is probably entered as text, and not as a valid date.
Changing the formatting only works on numeric or date entries, not text ones. You would need to convert the text to valid dates.
You can do that with "Text to Columns", to "re-enter" a whole column at a time.
Thank you for your reply Joe4, but it is not true.

I just tried again in a brand new workbook. These are the steps I made:

- In cell A1 I typed 11/06/2021 --> result 11-06-2021
- In cell B1 I typed =now() --> result: 11-06-2021 14:42:30

I wrote this macro:
VBA Code:
Sub test()
    Range("A1:B1").NumberFormat = "mmm/yy"
End Sub
Result in both cells is jun-21
 
Upvote 0
Then it sounds like you have placed the VBA code in the wrong place.
What is the name of the module you have placed this VBA code in?
It should be placed in a Standard/General module.
 
Upvote 0
Then it sounds like you have placed the VBA code in the wrong place.
What is the name of the module you have placed this VBA code in?
It should be placed in a Standard/General module.
Hi Joe4,
I put the macro in Module1
Patrick.
 
Upvote 0
I cannot recreate your behavior.
Are you able to upload a sample worksheet that is not working for you for us to download and look at?
You cannot upload files to this site, but you can upload them to a file sharing site and provide the link here.
 
Upvote 0
I cannot recreate your behavior.
Are you able to upload a sample worksheet that is not working for you for us to download and look at?
You cannot upload files to this site, but you can upload them to a file sharing site and provide the link here.
Joe4,
I have never done this before, so I'l give it a try :)
VBA_DateInLocalFormat.xlsm
This is the link to DropBox. I hope you can open it?

In the Excel file, I have put some screen shots of what I see, because I'm afraid that you see something else in your version of Excel, as you tell me that you cannot recreate the behavior of my Excel.
 
Upvote 0
I am confused as to what you are showing me and what the differences are between columns D and E. The titles both say results after running your macro.
So what is the difference?
Column D looks correct to me.
 
Upvote 0
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.
 
Upvote 0
I am confused as to what you are showing me and what the differences are between columns D and E. The titles both say results after running your macro.
So what is the difference?
Column D looks correct to me.
Dear Joe4,

The columns B, C and E are screen shots of what I see. I added them because I don't know your local settings (I'm a dutch speaking Belgian) so maybe your Excel shows you something different than mine. A screen shot doens't change so there you see what I see.
In my Excel, columns D and E are indeed the same. However, I would like to see the result as shown in column F, that is "jun/21" with a slash "/"
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top