VBA Date stamp showing in American Format (mm/dd/yyyy)

[tomexcel1]

Hi All

I've been using the below code for ages and it seams to have stopped working this week, The date keeps showing as mm/dd/yyyy instead of dd/mm/yyyy like it used too. Can anyone think why it keep reverting to american date format?

The code basically date stamps a cell when someone selects their name from a drop down box.

Thanks in Advance
Tom

Private Sub Worksheet_Change(ByVal Target As Range)

Me.Unprotect "passwordhere"

Dim WorkRngA As Range
Dim Rng As Range
Dim xOffsetColumn As Integer
Set WorkRngA = Intersect(Application.ActiveSheet.Range("L6:L130"), Target)

'Code for 1st range
xOffsetColumn = 1
Application.EnableEvents = False
If Not WorkRngA Is Nothing Then
    For Each Rng In WorkRngA
        If Not VBA.IsEmpty(Rng.Value) Then
            Rng.Offset(0, xOffsetColumn).Value = Date & " " & Time
            Rng.Offset(0, xOffsetColumn).NumberFormat = "dd/mm/yyyy" & " hh:mm:ss"
        Else
            Rng.Offset(0, xOffsetColumn).ClearContents
        End If
    Next
End If

Application.EnableEvents = True

Me.Protect "passwordhere"

End Sub

 

[RoryA]

Use:

Rng.Offset(0, xOffsetColumn).Value = Now

​

 

[tomexcel1]

Use:

Rng.Offset(0, xOffsetColumn).Value = Now

Thanks Rory!

Works perfectly, fixed the issue

 

[RoryA]

Glad to help.