MrExcel.com - Your One Stop for Excel Tips & Solutions

Excel doesn't recognize date format

Posted by Kierrica Firson on July 06, 2001 1:17 PM

I recieved a bunch of data that corresponds with a date. However, the date format is the first three letters of the month then the date then the four-digit year. Example: Sep 18 2001 Apparently, Excel does not recognize this as a date form, so when I try to format the cell to any other date format, say to 9/18/01, the cell remains unchanged. How would I go about changing large amounts of dates into a format that Excel recognizes?

Posted by David Rainey on July 06, 2001 1:26 PM


Dim MyDate
MyDate = DateValue("Sep 18 2001") ' Return a date.

Posted by Scott S on July 06, 2001 2:12 PM

This is kind of long, but it works:

Sub Change_Date()

Range("E9:E22").Select

Selection.Replace What:="jan", Replacement:="1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="feb", Replacement:="2", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="mar", Replacement:="3", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="apr", Replacement:="4", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="may", Replacement:="5", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="jun", Replacement:="6", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="jul", Replacement:="7", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="aug", Replacement:="8", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="sep", Replacement:="9", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="oct", Replacement:="10", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="nov", Replacement:="11", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:="dec", Replacement:="12", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.Replace What:=" ", Replacement:="/", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False
Selection.NumberFormat = "mm/dd/yy"

End Sub

Posted by Scott S on July 06, 2001 2:14 PM

Forgot to add: You would have to changet the range to equal your date range. Selection.Replace What:="jan", Replacement:="1", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="feb", Replacement:="2", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="mar", Replacement:="3", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="apr", Replacement:="4", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="may", Replacement:="5", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="jun", Replacement:="6", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="jul", Replacement:="7", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="aug", Replacement:="8", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="sep", Replacement:="9", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="oct", Replacement:="10", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="nov", Replacement:="11", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:="dec", Replacement:="12", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.Replace What:=" ", Replacement:="/", LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=False Selection.NumberFormat = "mm/dd/yy"

Posted by BILLY on July 06, 2001 2:37 PM

Also, give text to column a try under the data menu.
After highlighting, goto data, text to columns.
Check delimited and hit next. Hit next again. Click date and MDY. Finally finish.

Posted by Mark W. on July 09, 2001 3:06 PM

Using Edit | Replace change all occurances of
" 2001" with ", 2001".