Link to home
Start Free TrialLog in
Avatar of Eirman
EirmanFlag for Ireland

asked on

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

User generated image
Avatar of Karen Falandays
Karen Falandays
Flag of United States of America image

Try to Clear all on the cell and re-enter the date. Is it only on the one cell that this happens?
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

Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
In addition, using "Now" as suggested by Fabrice will include the current time with the date.
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.
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...
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.
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.
@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"
Avatar of Eirman


Just back home now ..... I have not tried anything yet.

Did anyone notice that the "sample" in my image is above is wrong.
No, stopped with the error/issue in the code.
Avatar of Eirman


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.
That's what I suggested from the beginning ....