Forum Discussion
Date formatting form with VBA to excel worksheet
Hi,
I have built a VBA form with a date input field. When I save the form the date formatting changes from the date that I entered 01-06-2018 to 06-01-2018 the date that appears in the excel cell.
I have checked the region settings and the date & time formats setting on my computer and they are correct dd/MM/yyyy. My VBA code is
'Format cells as a date
sh.Range("C6", "C2000").NumberFormat = "dd-MM-yyyy"
but it keeps thinking I am inputting the date in US format and changes it to European format.
Can anyone help please.
1 Reply
Hi Ken,
1. Please match your Region settings exactly as dd-MM-yyyy and not as dd/MM/yyyy.
2. You don't need this statement sh.Range("C6", "C2000").NumberFormat = "dd-MM-yyyy"
3. While saving your date, you can use Cells(Row, Column).Value = DateValue("01-06-2018") or if you use Range then you can use Range(CellAddress).Value instead of Cells(Row,Column).Value.
Hope this helps. This works on my Comp. Windows 10, and Office 365.
Regards,
San.
