Year to Date format - DD-MMM-YYYY

[Sdwd76]

I would like a button to convert a year into a date format.
The col is Y

For example if the cell contains 2018 then it is changed to 01-JAN-2018

Thanks in advance

 

[Peter_SSs]

If it important to have the month abbreviation in upper case then the result will need to be Text, otherwise, to keep a true date, try

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

 

[Sdwd76]

Great thanks this worked a treat.

If I want Col Z to have a text add to say "Date amended" is this possible? if the above works etc

 

[Peter_SSs]

If I want Col Z to have a text add to say "Date amended" is this possible?

Is this what you mean?

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