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

 

[GirishDhruva]

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

Hi sir thank you for your reply again and sorry for delay but then also i am getting same error (#Value) ,below is my sample table data
their is no space character in the end

[TABLE="width: 500"]
<tbody>[TR]
[TD]Column M[/TD]
[TD]Column N[/TD]
[TD]Column O[/TD]
[/TR]
[TR]
[TD]30042018[/TD]
[TD]30042018[/TD]
[TD]7052018[/TD]
[/TR]
[TR]
[TD]30042018[/TD]
[TD]30042018[/TD]
[TD]7052018[/TD]
[/TR]
[TR]
[TD]30042018[/TD]
[TD]30042018[/TD]
[TD]7052018[/TD]
[/TR]
[TR]
[TD]30042018[/TD]
[TD]30042018[/TD]
[TD]7052018[/TD]
[/TR]
[TR]
[TD]30042018[/TD]
[TD]30042018[/TD]
[TD]7052018[/TD]
[/TR]
[TR]
[TD]30042018[/TD]
[TD]30042018[/TD]
[TD]7052018[/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Hi sir thank you for your reply again and sorry for delay but then also i am getting same error (#Value) ,below is my sample table data
their is no space character in the end

Is there any chance you can post a copy of your original data to DropBox so that we can download it and examine why there is a problem with those two columns?

 

[GirishDhruva]

Ya sure but i am sharing only dates

https://www.dropbox.com/s/xyg6cp7152826sy/Dropbox_Dates.xlsx?dl=0

 

[GirishDhruva]

Is there any chance you can post a copy of your original data to DropBox so that we can download it and examine why there is a problem with those two columns?

Hi All,
Any updates about the error which i am facing