View original (French)

DdMMyyyy

Solved
areskip -  
Hello everyone,

I'm really stuck with dates in the format ddmmyyyy without any separator.
How can I convert them into valid dates in VBA?
I've tried breaking them into smaller pieces to insert /, but maybe there's a simpler method?

Thanks in advance if you have any ideas!

3 answers

  1.  pilas31 Contributor
    Hello,

    here's a possibility: 

    date_valide = CDate(Mid(jjmmaaaa, 3, 2) & "/" & Mid(jjmmaaaa, 1, 2) & "/" & Mid(jjmmaaaa, 5, 4))


    The variable jjmmaaaa contains the famous date. Keep in mind that in VBA it's an English format, so the days and months are reversed.
    Perhaps with the Format() instruction there are other possibilities

    --
    Best regards,
    1. areskip Author
      Thank you pilas31!

      I had to adapt because the zero for the days doesn't display (1 instead of 01).

      It looks like this:

      size = Len(Cells(i, j))
      If size = 8 Then date_valide = CDate(Left(Cells(i, j), 2) & "/" & Mid(Cells(i, j), 3, 2) & "/" & Right(Cells(i, j), 4))
      If size = 7 Then date_valide = CDate(Left(Cells(i, j), 1) & "/" & Mid(Cells(i, j), 2, 2) & "/" & Right(Cells(i, j), 4))

      But maybe there’s a simpler way by displaying the leading zero...
      1. michel_m Contributor
        Hello
        with the Pilas31 formula that I greet
        maybe 
        jjmmaaaa = "04062014"
        With Range("A1")
         .Value = CDate(Mid(jjmmaaaa, 3, 2) & "/" & Mid(jjmmaaaa, 1, 2) & "/" & Mid(jjmmaaaa, 5, 4))
         .NumberFormat = "dd/mm/yyyy"
        End With

        --
        Michel