VBA Converting Date From UK to US

[Lewzerrrr]

Hi,

I've got a data entry form that loads up the current date "06/07/2022" (which can manually overwritten by a user) however when it inputs into the worksheet it converts it to US system "07/06/2022".

How can I ensure it stays as UK system? It's not a problem once it goes past the 12th of the month as it then automatically converts to General type rather than Date type.

Thanks,

Private Sub CurrentDate()
   
    Me.txtDate.Value = Format(Now(), "dd/mm/yyyy")
   
End Sub

Private Sub WriteDataToSheet()

    Dim newRow As Long, DateSubmitted As String
   
    DateSubmitted = Me.txtDate.Value
   
    With shData
   
        newRow = .Cells(.Rows.Count, 1).End(xlUp).Row + 1
       
        .Cells(newRow, 1).Value = Me.txtID.Value
        .Cells(newRow, 2).Value = Me.cboCategory.Value
        .Cells(newRow, 3).Value = Me.cboSubcategory.Value
        .Cells(newRow, 4).Value = Me.txtCost.Value
        .Cells(newRow, 5).Value = DateSubmitted
        .Cells(newRow, 6).Value = Me.cboMonth.Value
           
    End With

End Sub

 

[Anthony47]

Try using

.Cells(newRow, 5).Value = CDate(DateSubmitted)

 

[Anthony47]

Thank you for the feedback