VBA to replace first two characters in dd/mm/yyyy with "01"

[alexcon]

Greetings,

In the attached file, I've got a bunch of dates set as dd/mm/yyyy. In this case, dd ranges from 01 - 31.

I'd like all dd to be replaced with 01 only, so that it's essentially 01/mm/yyyy, and still correctly reads as DMY.

I've manually done the first few in Column B as an example.

Looking for VBA that can do this.

Any suggestions appreciated.

[johnmpl]

Hi!

If you want, you can use just formulas to do it:
[B2] : =A2-DAY(A2)+1

Or, with VBA in same range:

Sub ChangeDate() 
    uf& = Range("A" & Rows.Count).End(xlUp).Row 
    Range("a2:a" & uf) = Evaluate(Replace("if(a2:a#,a2:a#-day(a2:a#)+1)", "#", uf)) 
End Sub  

Check file. Blessings!

[tim201110]

Please Login or Register  to view this content.

[63falcondude]

Here's a formula solution for anyone that may come across this in the future:

B2 =EOMONTH(A2,-1)+1

[alexcon]

Thanks everyone! Much appreciated