Number to Date for multiple columns

[GirishDhruva]

Hi Everyone,
Here i am trying to convert multiple columns from numbers to date(Columns are M,N,O)
I tried for converting only one column, but couldn't do the same for multiple columns in one Loop.
Can anyone suggest me how i can do this.......

    row = 2
    Do While Cells(row, 11).Value <> ""
        mydate = CStr(Cells(row, 13))
        If Len(mydate) = 7 Then
            myday = Left(mydate, 1)
            mymonth = Mid(mydate, 2, 2)
            myyear = Right(mydate, 4)
            Cells(row, 13).NumberFormat = "dd/mm/yyyy"
            Cells(row, 13).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, 13).NumberFormat = "dd/mm/yyyy"
            Cells(row, 13).Value = CDate(myday & "-" & mymonth & "-" & myyear)
        End If
    row = row + 1
    Loop

Thanks in advance

 

[Special-K99]

Doesn't solve your problem but if Len(mydate) is only going to be 7 or 8 you can remove that IF condition and shorten the code to simply

row = 2
    Do While Cells(row, 11).Value <> ""
        mydate = CStr(Cells(row, 13))

            myday = Left(mydate, 1+(Len(mydate)=8))
            mymonth = Mid(mydate, 2+(Len(mydate)=8), 2)

            myyear = Right(mydate, 4)
            Cells(row, 13).NumberFormat = "dd/mm/yyyy"
            Cells(row, 13).Value = CDate(myday & "-" & mymonth & "-" & myyear)
    row = row + 1
    Loop

 

[Special-K99]

This may solve your problem (am no VBA expert)
Loop where there is 13 is referenced

For i = 13 to 15
row = 2
    Do While Cells(row, 11).Value <> ""
        mydate = CStr(Cells(row, i))

            myday = Left(mydate, 1+(Len(mydate)=8))
            mymonth = Mid(mydate, 2+(Len(mydate)=8), 2)

            myyear = Right(mydate, 4)
            Cells(row, i).NumberFormat = "dd/mm/yyyy"
            Cells(row, i).Value = CDate(myday & "-" & mymonth & "-" & myyear)
    row = row + 1
    Loop
next i

 

[Rick Rothstein]

Does this macro do what you want...

Sub NumToDate()
  With Columns("M:O").SpecialCells(xlConstants, xlNumbers)
    .Value = Evaluate("IF({1},0+TEXT(" & .Address & ",""00\/00\/0000""))")
    .NumberFormat = "dd/mm/yyyy"
  End With
End Sub

 

[GirishDhruva]

Does this macro do what you want...

Sub NumToDate()
  With Columns("M:O").SpecialCells(xlConstants, xlNumbers)
    .Value = Evaluate("IF({1},0+TEXT(" & .Address & ",""00\/00\/0000""))")
    .NumberFormat = "dd/mm/yyyy"
  End With
End Sub

Thank you for your reply , but for first two columns(M,N) it's displaying as "#Value" errors and only for last column(O) it's displaying correctly

 

[GirishDhruva]

This may solve your problem (am no VBA expert)
Loop where there is 13 is referenced

For i = 13 to 15
row = 2
    Do While Cells(row, i).Value <> ""
        mydate = CStr(Cells(row, i))

            myday = Left(mydate, 1+(Len(mydate)=8))
            mymonth = Mid(mydate, 2+(Len(mydate)=8), 2)

            myyear = Right(mydate, 4)
            Cells(row, i).NumberFormat = "dd/mm/yyyy"
            Cells(row, i).Value = CDate(myday & "-" & mymonth & "-" & myyear)
    row = row + 1
    Loop
next i

Thank you for your reply , but it's throwing me an error in highlighted row as "Type Mismatch"

 

[Special-K99]

Well if it was working OK on column 13 (M) only then there's a problem with the data in columns 14 or 15 (N, O)

 

[GirishDhruva]

This may solve your problem (am no VBA expert)
Loop where there is 13 is referenced

For i = 13 to 15
row = 2
    Do While Cells(row, 11).Value <> ""
        mydate = CStr(Cells(row, i))

            myday = Left(mydate, 1+(Len(mydate)=8))
            mymonth = Mid(mydate, 2+(Len(mydate)=8), 2)

            myyear = Right(mydate, 4)
            Cells(row, i).NumberFormat = "dd/mm/yyyy"
            Cells(row, i).Value = CDate(myday & "-" & mymonth & "-" & myyear)
    row = row + 1
    Loop
next i

But not even Column 13 is getting printed

 

[Special-K99]

"if Len(mydate) is only going to be 7 or 8"

Maybe Len(mydate) is not 7 or 8 in whcih case this may not work.

 

[Rick Rothstein]

Thank you for your reply , but for first two columns(M,N) it's displaying as "#Value" errors and only for last column(O) it's displaying correctly

That would mean the values in Columns M and N are not numbers. Perhaps they have a space character at the end or, if you got the values from the web, perhaps they have a non-breaking space at the end. See if this version works...

Sub NumToDate()
  With Columns("M:O")
    .Replace " ", "", xlPart, , , , False, False
    .Replace Chr(160), "", xlPart, , , , False, False
    With .SpecialCells(xlConstants, xlNumbers)
      .Value = Evaluate("IF({1},0+TEXT(" & .Address & ",""00\/00\/0000""))")
      .NumberFormat = "dd/mm/yyyy"
    End With
  End With
End Sub