What's Happening With My Date Formatting

Using Excel 2013 .....
I have a button that when double-clicked, enters today's date.
The problem is that it is entered on to the sheet in American format (mm/dd/yyyy).
For today, it incorrectly displays 12/11/2018 instead of the correct UK formatted 11/12/2018
(G46 Should read ... EE 0 Days Ago)

G47 is correctly formatted and Win10 Locale is set to UK
Why could this be happening?

(If I incorrectly set the format of the vba to American it works .... a most unsatisfactory workaround)

Private Sub CommandToday2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Range("G47").Value = Format(Now(), "dd/mm/yyyy")
End Sub

Open in new window


G47 Format
LVL 24
EirmanChief Operations ManagerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Karen FalandaysTraining SpecialistCommented:
Try to Clear all on the cell and re-enter the date. Is it only on the one cell that this happens?
Fabrice LambertConsultingCommented:
If the cell is supposed to hold a date, you should not use the format function because it return a string.
Excel do an implicit convertion from string to date, and attempt to guess the day and the month.
It has 2 possibilities here: dd/mm/yyyy or mm/dd/yyyy. and since the day is below 12, it has no way to know if it is a month or a day. Too bad, it choosed the wrong one.

Side notes:
The global Range collection implicitly refer to ActiveWorkbook.ActiveSheet.Range, because of that it is Dangerous to use it. "Active" objects offer no garantees about their content (Keep in mind that Excel is multi-documents).
Better explicitly refer to the workbook, the worksheet and the range.
Sample code:
Dim wb As Excel.Workbook
Set wb = ThisWorkbook

Dim ws As Excel.Worksheet
Set ws = wb.worksheets(1)

Dim rng As Excel.Range
Set rng = ws.Range("G47")

rng.Value  = Now

Open in new window

Rob HensonFinance AnalystCommented:
Try setting the date and format separately:

With Range("G47")
    .Value = Date
    .NumberFormat = "dd/mm/yyyy"
End With

Open in new window


Date in VBA is a pre-defined variable representing today's date.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rob HensonFinance AnalystCommented:
In addition, using "Now" as suggested by Fabrice will include the current time with the date.
Gustav BrockCIOCommented:
Your problem is, that you convert the date value to a string which VBA then has to convert back to a date value - and in this process it will read it as in US format. So, reduce it to insert the _value_:

Private Sub CommandToday2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Range("G47").Value = Date
End Sub

Open in new window

and apply the format to the cell as to your liking.
ShumsExcel & VBA ExpertCommented:
Just today I had similar issues and tried all above solutions, eventually it worked:

Try below:
Private Sub CommandToday2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Range("G47").Value = Format(CDate(Date), "dd/mm/yyyy")
End Sub

Open in new window


Above all you also need to change Regional Setting in Control Panel. Check if your date format is in UK or US format...
Gustav BrockCIOCommented:
That's pure luck.
Calling CDate(Date) makes no sense, and applying format potentially reintroduces the original issue - trying to cast a string value to a date value.
Rob HensonFinance AnalystCommented:
Also, are you aware of the keyboard shortcut that enters the current date?

"Ctrl + ;" (semi-colon) enters current date, leaves the cell in Edit mode so would also need to press Enter.

Likewise, "Shift + Ctrl + ;" will enter current time.
Rob HensonFinance AnalystCommented:
@Shums - I am in the UK and my regional settings are set accordingly. I recorded and amended the little snippet that I provided just to ensure I had the syntax correct. Bizarrely, when I recorded setting the cell format to standard Short Date formatting it recorded as:

NumberFormat = "m/d/yy"
EirmanChief Operations ManagerAuthor Commented:
Just back home now ..... I have not tried anything yet.

Did anyone notice that the "sample" in my image is above is wrong.
Gustav BrockCIOCommented:
No, stopped with the error/issue in the code.
EirmanChief Operations ManagerAuthor Commented:
The first thing I tried was Rob's code and it works fine - thank you.
Private Sub CommandToday2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
   With Range("G47")
    .Value = Date
    .NumberFormat = "dd/mm/yyyy"
End With

Open in new window

The "Sample" on Properties > Format
is now correct.
Fabrice LambertConsultingCommented:
That's what I suggested from the beginning ....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.