Eirman
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)
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
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:
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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:
Above all you also need to change Regional Setting in Control Panel. Check if your date format is in UK or US format...
Try below:
Private Sub CommandToday2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Range("G47").Value = Format(CDate(Date), "dd/mm/yyyy")
End Sub
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.
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.
"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"
NumberFormat = "m/d/yy"
ASKER
Just back home now ..... I have not tried anything yet.
Did anyone notice that the "sample" in my image is above is wrong.
Did anyone notice that the "sample" in my image is above is wrong.
No, stopped with the error/issue in the code.
ASKER
The first thing I tried was Rob's code and it works fine - thank you.
is now correct.
Private Sub CommandToday2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
With Range("G47")
.Value = Date
.NumberFormat = "dd/mm/yyyy"
End With
The "Sample" on Properties > Formatis now correct.
That's what I suggested from the beginning ....