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.
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Joe4

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

opislak

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

Joe4

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

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61

ADVERTISEMENT

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.
 

Joe4

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

opislak

Board Regular
Joined
Feb 28, 2017
Messages
61

ADVERTISEMENT

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.
 

Joe4

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

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,557
Office Version
  1. 365
Platform
  1. Windows
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.
 

opislak

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

Forum statistics

Threads
1,141,284
Messages
5,705,508
Members
421,399
Latest member
hjweiss00

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