Sub YearToDate()
With Range("Y2", Range("Y" & Rows.Count).End(xlUp))
.NumberFormat = "dd-mmm-yyyy"
.Value = Evaluate(Replace("If(#="""","""",Date(#,1,1))", "#", .Address))
End With
End Sub
Is this what you mean?If I want Col Z to have a text add to say "Date amended" is this possible?
Sub YearToDate_v2()
With Range("Y2", Range("Y" & Rows.Count).End(xlUp))
.NumberFormat = "dd-mmm-yyyy"
.Value = Evaluate(Replace("If(#="""","""",Date(#,1,1))", "#", .Address))
.Offset(, 1).Value = Evaluate(Replace("if(#="""","""",""Date amended"")", "#", .Address))
End With
End Sub