If I have a cell with the following:
Tuesday, April 16th 2009
How do I convert that string into a date format recognized by Excel. I think I have to use MID() and FIND() functions.
Add a comment
|
2 Answers
Here is a function that would work from VBA and as a User Defined Function
Function GetDate(InString As Range) As Date
Dim newDate As Date
Dim tmpDate As String
'Sample Date
'Tuesday, April 16th 2009
'Remove the Day of the Week.
tmpDate = Trim(Mid(InString, InStr(InString, ",") + 1))
'Get rid of "th"
tmpDate = Replace(tmpDate, "th ", " ")
'Get rid of "rd"
tmpDate = Replace(tmpDate, "rd ", " ")
'Get rid of "nd"
tmpDate = Replace(tmpDate, "nd ", " ")
'Get rid of "st"
tmpDate = Replace(tmpDate, "st ", " ")
'Convert string to date
newDate = DateValue(tmpDate)
GetDate = newDate
End Function
1 Comment
capm
Thanks, the script worked well. I modified because in spanish the date is written different. For example: 'Tuesday, April 16th 2009' is 'Martes, 16 de Abril de 2009'. So as you can see there is no 'th','rd or any of those characters. So instead of removing them, I change the script to remove 'de'. Worked like a charm.
Assuming that text is in A1, we can break it out into separate parts, and use DATEVALUE to put it together.
B1: =MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+2)-FIND(" ",A1))
B2: =IFERROR(VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),3)),VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),2)))
B3: =RIGHT(A1,4)
B4: =DATEVALUE(B2&" "&B1&" "&B3)
Or, you can do it in one go:
=DATEVALUE(IFERROR(VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),3)),VALUE(MID(A1,FIND(" ",A1,FIND(" ",A1)+2),2)))&" "&MID(A1,FIND(" ",A1)+1,FIND(" ",A1,FIND(" ",A1)+2)-FIND(" ",A1))&" "&RIGHT(A1,4))
