CODEWITHSUNDEEP
vbaexcelformattinguserform
MBrann
MBrann

Reputation: 265

How to format date to UK date in excel vba

I have created a userform which looks at several cells inputting dates. Strange that it is very temperamental when it formats the date as UK format. When I initially written the code it would format the date in UK format, and then again the next, today however the 11th September 2017 it has diverted to a US Format and instead of reading 11/09/2017 it is reading 09/11/2017.

The dates are put in via a textbox and coded as below. Can someone issue any advice to prevent this happening in the future? Also the language and keyboard setups are both English U.K.

ws.Cells(mRow, 2).Value = Format(Date, "DD/MM/YYYY")
ws.Cells(mRow, 12).Value = Format(Me.TxtRcD.Value, "DD/MM/YYYY")
ws.Cells(mRow, 18).Value = Format(Me.TxtDD.Value, "DD/MM/YYYY")

In all of these examples the date has been input in the textbox as 11/09/2017

Upvotes: 0

Views: 4663

Answers (1)

Salek
Salek

Reputation: 303

Make sure you are reading the cell as correct data format - that might cause problems when formatting. If it is String, convert it to Number or Date. If you are reading it as number, NumberFormat might be useful:

selection.NumberFormat = "dd/mm/yyyy"

Also, pay attention to uppercase and lowercase: DD is not the same as dd. Don't have it on top of my head, but for example if you need month as 3 first letters (like FEB, JUN, etc.) you need 'MMM' not 'mmm'

Upvotes: 1

Related Questions