Convert data string to mm/dd/yyyy

  • Hi i have an excel with mixture of dates in long string format (Shown below) as well as some rows in mm/dd/yyyy. How do i change it all to mm/dd/yyyy using VBA ? I tried using right click format date but nothing changes.


    Wednesday, August 22, 2018 5:06:00 PM
    Friday, August 31, 2018 9:57:27 AM
    Friday, August 31, 2018 9:57:27 AM
    Friday, August 31, 2018 9:57:27 AM
    Friday, August 31, 2018 9:57:27 AM
  • Are all the dates in one column? If so which column?

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hello KjBox ;)


    @ yankiessg


    You seem to have stored Text (and not Values) in your cells ...


    If that is the case, you could test following

    Code
    Sub TestSplitDate()
    Dim arr, mymonth, myyear, myday
        arr = Split(ActiveCell, ", ")
        mymonth = Split(arr(1), " ")
        myday = Split(mymonth(1), " ")
        myyear = Split(arr(2), " ")
    ActiveCell.Offset(0, 2) = Format(Month(DateValue("01/" & mymonth(0) & "/2020")), "00") & "/" & myday(0) & "/" & myyear(0)
    End Sub


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • I noticed that too, Carim.


    Assuming your data starts in Row 2 where Row 1 is a header row, if the dates are in Column A then this will convert them all to "mm/dd/yyyy"

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

    Edited 2 times, last by KjBox ().

  • slight change needed

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!