#Value error when macro is run by colleague in Europe

[gseft]

I wrote the following to remove the time from the date in a report we are provided by a vendor. When I run this macro in the US the field updates properly, but when my colleague in Europe runs the macro it returns #Value error for most of the dates. I initially thought it was just a problem with the date order and I made the change to the formula to reorder the date to fit Euro formatting but my colleague is still receiving #Value errors for some of the dates. Looking for help in trying to correct this issue.

This is pulling the month, day, and year from the previous column to remove the time information sent in the report. I did edit my colleagues formula to arrange the date as Day, Month, Year to match Euro date format but the error still occurs.

Columns("R:R").Select
Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("R2").Formula = _
"=MONTH(RC[-1]) & ""/"" & DAY(RC[-1]) & ""/"" & YEAR(RC[-1])"
Range("R2").AutoFill Destination:=Range("R2:R" & lastRow)
Range("R2:R" & lastRow, Selection.End(xlDown)).Select
Selection.Copy

 

[Joe4]

Welcome to the Board!

What exactly do your entries look like? Are they coming across as Text, or valid Date/Time entries?
If they are already valid Date/Time entries, just use the INT function to remove the time portion.
(Excel stores dates as numbers, specifically the number of days since 1/0/1900, so the time piece is just the decimal portion of the number, so you can use INT to remove it).

 

[gseft]

Welcome to the Board!

What exactly do your entries look like? Are they coming across as Text, or valid Date/Time entries?
If they are already valid Date/Time entries, just use the INT function to remove the time portion.
(Excel stores dates as numbers, specifically the number of days since 1/0/1900, so the time piece is just the decimal portion of the number, so you can use INT to remove it).

That works great. A much simpler fix as well.

Thanks for your help.

 

[Joe4]

You are welcome!
Glad I was able to help!