We help IT Professionals succeed at work.

What's Happening With My Date Formatting

156 Views
Last Modified: 2018-12-20
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

Karen FalandaysTraining Specialist
CERTIFIED EXPERT

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
CERTIFIED EXPERT
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
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
In addition, using "Now" as suggested by Fabrice will include the current time with the date.
CERTIFIED EXPERT
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
CERTIFIED EXPERT
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...
CERTIFIED EXPERT
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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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
CERTIFIED EXPERT

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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

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

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
CERTIFIED EXPERT
Distinguished Expert 2017

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions