Odtnhj

Excel keeps changing my date to US format. I have a user form where the user inputs a date. As an example, I enter 01/11/2018 (1st November 2018). I then put this value into a cell however it changes automatically to 11/01/2018 or 11th January 2018.

I've so far tried using the following code to force it to use UK format;

SHT_data_TASKS.Cells(1 + tableRows, 8).NumberFormat = "dd/mm/yyyy;@"
SHT_data_TASKS.Cells(1 + tableRows, 8) = form_addTask_Date.Value

But it does not help. I've also tried using CDate function but still no luck. I've checked my computer regional settings and they are all correct for the UK.

If I right click on the cell where the date is placed and chose format it shows as already being formatted to UK date! I'm not sure what is causing it to change.

This is the date code I use on control containing UK dates:

Private Sub UserForm_Initialize()
 Me.txtDate = Format(Date, "dd-mmm-yyyy")
End Sub 

On the AfterUpdate event for the date control:

Private Sub txtDate_AfterUpdate()
 With Me
 FormatDate .txtDate
 End With
End Sub 

In a normal module:

Public Sub FormatDate(ctrl As Control)

 Dim dDate As Date
 Dim IsDate As Boolean

 If Replace(ctrl.Value, " ", "") <> "" Then
 On Error Resume Next
 dDate = CDate(ctrl.Value)
 IsDate = (Err.Number = 0)
 Err.Clear
 On Error GoTo 0

 If IsDate Then
 ctrl.Value = Format(ctrl.Value, "dd-mmm-yyyy")
 ctrl.BackColor = RGB(255, 255, 255)
 Else
 ctrl.BackColor = RGB(255, 0, 0)
 End If
 End If

End Sub 

Then I transfer it to the worksheet (on a Save button) using the below code:

shtRawData.Cells(rLastCell.Row, CLng(lCol)) = CDate(ctrl.Value)

When you're about to write your date to the sheet:

1. Verify that it's being stored as a date (or date equivalent) with isdate()
   


2. Assign it via Range.FormulaLocal instead of value or value2.

Untested, but i.e.

Range("A1").FormulaLocal = #10/31/2018#