View original (French)

DdMMyyyy

Solved
areskip Posted messages 2 Status Member -  
michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   -
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 Posted messages 1878 Status Contributor 648
     
    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,
    0
  2. areskip Posted messages 2 Status Member
     
    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...
    0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    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
    0