converting number to date format

[GirishDhruva]

Hi everyone,

can anyone suggest me that can we convert 8 digits or 7 digits numbers to date format

Like example:
12022018 to 12/02/2018
7022018 to 07/02/2018


can this be done using vba, if yes please suggest me how.
Thanks in advance

 

[Special-K99]

Formula solution is quicker

=TEXT(A1,"00-00-0000")+0

or VBA (though am no expert in this)

Sub NumToDate()
Dim var1 As Date
var1 = Format(Range("A1"), "##-##-####")
MsgBox var1
End Sub

 

[GirishDhruva]

Thanks for you effort...
I found the solution

Sub Convert_To_Date()
    Dim mydate, mymonth, myday, myyear As String
    Dim row As Long
    row = 2
    Do While Cells(row, 15).Value <> ""
        mydate = CStr(Cells(row, 15))
        If Len(mydate) = 7 Then
            myday = Left(mydate, 1)
            mymonth = Mid(mydate, 2, 2)
            myyear = Right(mydate, 4)
            Cells(row, 15).NumberFormat = "dd/mm/yyyy"
            Cells(row, 15).Value = CDate(myday & "-" & mymonth & "-" & myyear)
        ElseIf Len(mydate) = 8 Then
            myday = Left(mydate, 2)
            mymonth = Mid(mydate, 3, 2)
            myyear = Right(mydate, 4)
            Cells(row, 15).NumberFormat = "dd/mm/yyyy"
            Cells(row, 15).Value = CDate(myday & "-" & mymonth & "-" & myyear)
        End If
    row = row + 1
    Loop
End Sub

 

[tyija1995]

Formula solution is quicker

=TEXT(A1,"00-00-0000")+0

Hey, I tried this for myself and it returned the serial number, when I removed the +0 it gave me date format as dd-mm-yyyy which is fine.

The "/" format can be achieved using the escape character \ so essentially you get
=TEXT(A1, "00\/00\/0000")

It looks a bit funny but that's Excel for you!

 

[Special-K99]

+0 forces it into an Excel date otherwise it will still be 12022018 and you wont be able to perform any date arithmetic if needed.
You just need to format the cell to display as you want.

 

[FormR]

Hi, here is another VBA option you can try.

Sub Convert_To_Date()
With Range("O2:O" & Range("O" & Rows.Count).End(xlUp).Row)
    .Value = Evaluate("CHOOSE({1},0+TEXT(" & .Address & ",""00-00-0000""))")
    .NumberFormat = "dd/mm/yyyy"
End With
End Sub