Avatar of Eirman
Eirman
Flag 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


G47 Format
Microsoft OfficeMicrosoft ExcelMicrosoft ApplicationsProgrammingVBA

Avatar of undefined
Last Comment
Fabrice Lambert

8/22/2022 - Mon
Karen Falandays

Try to Clear all on the cell and re-enter the date. Is it only on the one cell that this happens?
Fabrice Lambert

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

ASKER CERTIFIED SOLUTION
Rob Henson

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Rob Henson

In addition, using "Now" as suggested by Fabrice will include the current time with the date.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Gustav Brock

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.
Shums Faruk

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 Brock

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob Henson

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 Henson

@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"
Eirman

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

Did anyone notice that the "sample" in my image is above is wrong.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Gustav Brock

No, stopped with the error/issue in the code.
Eirman

ASKER
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 Lambert

That's what I suggested from the beginning ....
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.