Automatic rearrangement issue of dates in excel

0
votes

If my date is "Sep-01-2015" it should return dd-mm-yyyy. Which can be done by cell formatting settings.

But what if my date I want to enter and print is the same "11-Jan-2021"? I want to type 11/01/2021 and format cell to 11-Jan-2021. I know I can use format cell option to convert date format or use custom format. But that is not my question.

When I'm typing the cell is re-arranging terms like 11/01/2021 as 01-Nov-21 where it should be 01-Jan-21.

Also, I want to keep regional setting in the code and not manual adjustments. If you find any VBA date format code to set the date in "en-US" or for any regional preferences please add your answer below.

Thanks!

enter image description here

enter image description here

2
excelvbadateexcel-formula
It depends on you computer local date format settings. - Harun24HR

2 Answers

0
votes

  1. What you input will always be parsed according to your locale ("Windows Regional Settings"): I have UK english locale so if I format a cell mm/dd/yyyy and enter 11/1 it interprets it with reference to my locale (so entering 11/1 is parsed as 11th Jan 2021) but displays it as 01/11/2021

  2. In the highlighted line, you have not explcitly declared a format in & Format(dtmDate) & and so it will format that according to the locale - so if it is only a display issue then explicitly put the date format here, e.g. & Format(dtmDate, "dd-mmm-yyyy") &

0
votes

Here is the final and easy answer to my question.

I tried the above solution also, it was useful for me but not for all the users. Some of my colleagues were still facing the issues with some regions.

Then I found that the VB macro was working right with this code: & Format(dtmDate, "dd-mmm-yyyy") &. But along with that we also need to change the cell formating to text.

Like this enter image description here

So, now whatever you enter it will be taken as it is for macros to process and produce the output irrespective of your system settings.

Like you can see in the image above, I have entered 2/01/2023 and converted to text from cell formatting. VB macros already is in & Format(dtmDate, "dd-mmm-yyyy") & this format.The 3 Ms means month in three letters like Jan.

Hence, my output will be enter image description here

Hope you get it. Thanks.