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

tomexcel1

New Member
Joined
Feb 22, 2018
Messages
45
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

VBA Code:
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
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Use:

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,463
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Glad to help. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,127,473
Messages
5,624,933
Members
416,064
Latest member
PaulBr2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top