Date Format Issue

[Mikeice]

HI All

I have the following code that enters the date when a cell is filled.

IT works perfectly thx to this forums help - thx all.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("D11100")) Is Nothing Then
With Target
..Offset(0, -1).Value = Format(Time, "hh:mm:ss")
..Offset(0, -2).Value = Format(Date, "dd/mm/yyyy")
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


My problem is since it changed to June yesterday the date is displaying
in US date format.

I have the cells formated as d/mm/yyyy on the worksheet but is still is
displaying todays date as 06/3/2005 how can I force it to display
British / Australian format
3/06/2005.

look forward to your reply

 

[Guest]

change .Offset(0, -2).Value = Format(Date, "dd/mm/yyyy")
to

 

[jindon]

Hi,
try

Code:
--------------------

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("D11100")) Is Nothing Then
With Target.Offset(0, -1)
.Value = Time
.NumberFormat = "hh:mm:ss"
End With
With Target.Offset(0, -2)
.Value = Date
.NumberFormat = "d/mm/yyyy"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub