trying to move dates to week ending 1

[thelos]

hi there guys

im having a funny problem inserting a piece of code.

With Range(Cells(2, "A"), Cells(2, "A").End(xlDown))
lRowStart = .Row
lRowEnd = lRowStart + .Rows.Count - 1
End With
With Range(Cells(lRowStart, "B"), Cells(lRowEnd, "B"))
.Offset(-1).Value = "WE"
.Formula = "=IF(WEEKDAY(A2)=7,A2+6,IF(WEEKDAY(A2)=1,A2+5,IF(WEEKDAY(A2)=2,A2+4,IF(WEEKDAY(A2)=3,A2+3,IF(WEEKDAY(A2)=4,A2+2,IF(WEEKDAY(A2)=5,A2+1,IF(WEEKDAY(A2)=6,A2)))))))"
End With

im trying to move dates to week ending, this does work except for a few exceptions. I format the cells to show "dd/mm/yy" but no matter how many times i format the cells they stay "dd/mm/yyyy"

is the way im inputing the formula into the cells wrong???

 

[lupins46]

Well there's a simpler formula, but I don't see how the formula will have any bearing on your formatting problem.
"=if(Weekday(A2)=7, A2+6, 6-Weekday(A2))"

Make sure you have used Edit Clear Format on all relevant cells before you apply the format you want.