Vba to change a list of text data to dates?

[tonywatsonhelp]

Hi Everyone,

I have a sheet with data that's supposed to be dates but is recorded wrong I need a macro to turn it into dates.

So Column F hold what should be dates but they are written as just year month like this "2010-01"

2010-01

2010-05

2010-07

2010-11

2011-01

2011-02

<tbody>
</tbody>

So I want them all to be first of the month and an actual date in the order dd/mm/yyyy
so the first one would be "01/01/2010" etc

how can I do this?

Thanks

Tony

 

[louisH]

Hi,

If you can confirm that column F has no blank row & starts in F1 you can use this :

Assuming you want first day of the month :

Sub verificaCNP()
    
On Error Resume Next

    nbr = Range("F1").End(xlDown).Row
    
    For Each cel In Range("F1:F" & nbr)
        
        y = Left(cel.Value, 4)
        m = Right(cel.Value, 2)
        cel.Value = DateSerial(y, m, 1)
    
    Next cel
    
 
End Sub

 

[tonywatsonhelp]

Thank you LouisH, had to play around with it but just what i needed