Adding Date to cell via Userform, Date swaps Day with Month...

[Chrisjd2]

Hi All,

Hope you can help.

As per the description, when I enter a date into my userform which then puts it into a cell, sometimes excel swaps the Day with the Month, which I dont want. This is frustrating, sometimes it does it, sometimes it doesn't, depending on the date. I have tried to force the cell but that doesn't work either. What I enter in the userform TB must be the date shown in the cell, I don't understand why it insists on changing it.

My PC settings on the clock is set to British Time / Britsh Date..

Here is some of my code I have used to enter the date into the cell and also to try and force the date correctly.

        If TrialDateTB.Text <> "" Then 
           DT = TrialDateTB.Text
            ws.Cells(crow, 7) = DT
            ws.Cells(crow, 7) = Format(DT, "dd/mm/yyyy")
            ws.Cells(crow, 7).HorizontalAlignment = xlRight
        End If
        
        If ClosingDateTB.Text <> "" Then
            DT = ClosingDateTB.Text
            ws.Cells(crow, 8) = DT
            ws.Cells(crow, 8) = Format(DT, "dd/mm/yyyy")
            ws.Cells(crow, 8).HorizontalAlignment = xlRight
        End If

 

[Norie]

Don't use Format, use DateValue then format the cell as required.

    If TrialDateTB.Text <> "" Then
    DT = TrialDateTB.Text
    With ws.Cells(crow, 7)
        .Value = DateValue(DT)
        .NumberFormat = "dd/mm/yyyy"
        .HorizontalAlignment = xlRight
    End With

End If

If ClosingDateTB.Text <> "" Then
    DT = ClosingDateTB.Text
    With ws.Cells(crow, 8)
        .Value = DateValue(DT)
        .NumberFormat = "dd/mm/yyyy"
        .HorizontalAlignment = xlRight
    End With
End If

 

[Chrisjd2]

Don't use Format, use DateValue then format the cell as required.

    If TrialDateTB.Text <> "" Then
    DT = TrialDateTB.Text
    With ws.Cells(crow, 7)
        .Value = DateValue(DT)
        .NumberFormat = "dd/mm/yyyy"
        .HorizontalAlignment = xlRight
    End With

End If

If ClosingDateTB.Text <> "" Then
    DT = ClosingDateTB.Text
    With ws.Cells(crow, 8)
        .Value = DateValue(DT)
        .NumberFormat = "dd/mm/yyyy"
        .HorizontalAlignment = xlRight
    End With
End If

THANKYOU!!!!