Convert text date to date format

[MrManBoy]

Hi Team,

How does one change a text date to a excel date format?

I tried simply by formatting the cell to date format, yet still not working for me.

https://drive.google.com/open?id=0B-TPXFo_aWFqazc0Q3QySUl1RWM

 

[Macropod]

You could try putting a 1 in any unused cell, cutting that to the clipboard, then selecting your dates and using Paste>Paste Special>Values>Multiply.

 

[MrManBoy]

Thanks Paul - however it did not work for me perhaps I may be doing something incorrectly? I have attached an example workbook here with the same formatting as the original raw data. Hope this helps with the troubleshooting.

https://drive.google.com/open?id=0B-TPXFo_aWFqR01DTl93Y2dkOTg

 

[admiral100]

Hi,

In your example file , enter in D2 =DATEVALUE(REPLACE(B2,1,FIND(",",B2),""))
then go to cells format and change to date

=DATEVALUE(REPLACE(B2,1,FIND(",",B2),"")) +1 ​for next day...

 

[Macropod]

Alternatively, you could use a macro like:

Sub Demo()
Dim i As Long
With Selection
  For i = 1 To .Cells.Count
    .Cells(i).Value = CDate(Split(.Cells(i).Text, ", ")(1))
  Next
End With
End Sub

 

[MrManBoy]

Hi,

In your example file , enter in D2 =DATEVALUE(REPLACE(B2,1,FIND(",",B2),""))
then go to cells format and change to date

=DATEVALUE(REPLACE(B2,1,FIND(",",B2),"")) +1 ​for next day...

Alternatively, you could use a macro like:

Sub Demo()
Dim i As Long
With Selection
  For i = 1 To .Cells.Count
    .Cells(i).Value = CDate(Split(.Cells(i).Text, ", ")(1))
  Next
End With
End Sub

Thank you both so much, both solutions work perfectly. Now i have 2 ways of solving this problem, appreciate the help!