Make Excel understand month names in english

Question

I have Excel spreadsheets with dates as strings (12/Nov/12 6:59 PM). If I apply Date / Date Time format to that cells - nothing happens. What I want to do is to make Excel interpret theese values as dates (so, for example YEAR function works, or I can compare this values in COUNTIFS (now I cannot, but I need it)). The last thing I've tried is custom format: [$-409]ДД/ММ/ГГ чч:мм AM/PM (yeah, I am russian user). It does not help too, I am stuck.

How can I make Excel parse it correctly?

Answer 1

You can build a lookup table with the English month names in the first column and the month number in the next column. Then you can use a Date() formula and use Vlookup to get the month number:

=DATE(MID(A1,FIND(" ",A1)-2,2)+2000,VLOOKUP(MID(A1,FIND("/",A1)+1,3),$E$1:$F$12,2,FALSE),LEFT(A1,FIND("/",A1)-1))

If you don't want to use a lookup table, you can do it in one step with the lookup inside the formula:

=DATE(MID(A1,FIND(" ",A1)-2,2)+2000,LOOKUP(MID(A1,FIND("/",A1)+1,3),{"Apr","Aug","Dec","Feb","Jan","Jul","Jun","Mar","May","Nov","Oct","Sep"},{4,8,12,2,1,7,6,3,5,11,10,9}),LEFT(A1,FIND("/",A1)-1))

Both formulas take into account that the day may be just one digit. Also, since the Date() function will interpret a 2-digit year like "12" as 1912, you need to add 2000 to the date. This will only work if all dates are greater than 1/1/2000, of course.