I have an issue:
I have a column with date in dd.mm.yyyy format, I replace . with - and use loop with:
Cells(i, 2).Value = Format(Cells(i, 2).Value, "yyyy-mm-dd") Because I need to make it yyyy-mm-dd.
It works mostly fine, but formats of a few dates not properly like it makes 5th April into 4th May.
Sounds like your current cell values are stored as strings that happen to only look like dates.
Your regional settings will be defaulting to date format mm-dd-yyyy. So where dates are ambiguous (as 5th April is) Excel will default to that interpretation.
You should really store the dates as real dates, and use formatting to display them as you wish. Somethig like
If Cells(i, 2).Value Like "##-##-####" Then s = Cells(i, 2).Value Cells(i, 2).Value = DateSerial(Mid$(s, 7, 4), Mid$(s, 4, 2), Left(s, 2)) Cells(i, 2).NumberFormat = "yyyy-mm-dd" End If If you insist on storing them as strings, use
If Cells(i, 2).Value Like "##-##-####" Then s = Cells(i, 2).Value Cells(i, 2).NumberFormat = Format$(DateSerial(Mid$(s, 7, 4), Mid$(s, 4, 2), Left(s, 2)), "yyyy-mm-dd") End If 