What's Happening With My Date Formatting

Eirman
Eirman used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Karen FalandaysTraining Specialist

Commented:
Try to Clear all on the cell and re-enter the date. Is it only on the one cell that this happens?
Fabrice LambertConsulting
Distinguished Expert 2017

Commented:
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

Finance Analyst
Commented:
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.
Ensure you’re charging the right price for your IT

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

Rob HensonFinance Analyst

Commented:
In addition, using "Now" as suggested by Fabrice will include the current time with the date.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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.
ShumsManaging Director/Excel VBA Developer
Distinguished Expert 2018

Commented:
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...
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
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 Analyst

Commented:
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 Analyst

Commented:
@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 Manager

Author

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

Did anyone notice that the "sample" in my image is above is wrong.
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
No, stopped with the error/issue in the code.
EirmanChief Operations Manager

Author

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 LambertConsulting
Distinguished Expert 2017

Commented:
That's what I suggested from the beginning ....

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial