Date type changing when moving cells from one sheet to another

[markpem]

Hello All,

I have some code below that looks for any changes and finds the match on the other sheet and updates any changes, however the dates when transferred over go from the UK formatiing of dd/mm/yyyy to the US style date of mm/dd/yyyy making all the dates in the sheet incorrect.

Could someone help and advise how I make it stay the same way? Thanks!"



Dim s1rw As Long, s2rw As Long, col As Long, endcol As Long

Sheets("Sheet1").Select
With Sheets("Sheet2")
s2rw = 2 ' Adjust to first data row #
endcol = .Cells(s2rw - 1, 1).End(xlToRight).Column
Do Until .Cells(s2rw, 1).Value = "" ' Loop through case #s
s1rw = 0
On Error Resume Next
s1rw = Cells.Find(What:=.Cells(s2rw, 1).Value, LookIn:=xlFormulas, LookAt:=xlWhole).Row
On Error GoTo 0
If s1rw > 0 Then ' Found case #
For col = 1 To endcol ' Loop through columns
If Cells(s1rw, col).Value <> "" Then
.Cells(s2rw, col).Value = Cells(s1rw, col).Value
End If
Next
End If
Rows(s2rw).Delete
s2rw = s2rw + 1
Loop
.Select
End With

[SamT]

Are you certain that the Date Format Locale is set to English (United Kingdom) on all machines?

[Aflatoon]

I suspect your dates are stored as text on the source sheet, in which case you need something like this:

For col = 1 To endcol ' Loop through columns
If Cells(s1rw, col).Value <> "" Then
If IsDate(Cells(s1rw, col).Value) Then
.Cells(s2rw, col).Value = CDate(Cells(s1rw, col).Value)
Else
.Cells(s2rw, col).Value = Cells(s1rw, col).Value
End If
End If
Next

[snb]

crossposted:

http://www.excelguru.ca/forums/showthread.php?3888-Date-type-changing-when-moving-cells-from-one-sheet-to-another