Changing date format in a macro

[sncb]

Hi All,

My macro currently pastes dates in column 'G' with format mm/dd/yy and I need to change it to dd/mm/yy.

Can anyone suggest the line of code required.

Here is the current code:

If

Cells(IntCounter, 6).Value = "No" Then
If IsError(Application.Match(Cells(IntCounter, 2), Sheets("Test").Columns("B"), 0)) Then
Rows(IntCounter).Cut Destination:=Sheets("Test").Range("A" & Rows.Count).End(xlUp).Offset(1)
Sheets("Test").Range("G" & Rows.Count).End(xlUp).Offset(1).Value = Date

End If


Thanks

 

[JamesW]

Hi,

Try:

= Format(Date,"dd/mm/yyyy")

 

[Andrew Poulsom]

The Format function returns a string. Try:

With Sheets("Test").Range("G" & Rows.Count).End(xlUp).Offset(1)
    .NumberFormat = "dd/mm/yyyy"
    .Value = Date
End With

 

[JamesW]

Didn't realise that, thanks Andrew.

 

[sncb]

Hi,

For some reason the first code worked and I was able to see the dates in dd/mm/yyyy format. The second one was giving me a syntax error.

Others Thanks as well.

 

[Andrew Poulsom]

With James' code I think you will find that the date is text, not a serial date. You can check with the ISNUMBER function - returns TRUE for a serial date.

Which line of my code caused a compile error? It compiles fine for me.

 

[sncb]

Hi Andrew,,

my mistake..I didnt start and end with the 'With' operator...it ran fine...Thanks a lot..