Date issues

[davewatson86]

Hello all

i have a report that is giving me dates in the format of DD.MM.YYYY i am trying to convert this to a date format that excel will work with but i am getting some strange tings happening.

i have used find replace to replace . with / but some dates are showing in a format of MM/DD/YYYY and some are showing as DD/MM/YYYY

this is the code i have used

WS_Data.Range("I:I").Replace What:=".", Replacement:="/", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

i have tried to use this code to change the format.

WS_Data.Range("I:I").NumberFormat = "dd/mm/yyyy:@"

however it faults stating "Unable to set the number format property of the Range class" i am thinking that its due to it being in a table but im not sure.

i did try this code to only effect the used cells rather than the whole column but had the same result

WS_Data.Range("I22", WS_Data.Range("I2").End(xlDown)).NumberFormat = "dd/mm/yyyy:@"

any help is greatly appreciated

Dave

 

[davewatson86]

will try this now cheer Fluff