Заставьте Excel понимать названия месяцев на английском языке

Question

У меня есть таблицы Excel с датами в виде строк (12/Nov/12 6:59 PM). Если я применяю формат даты / даты и времени к этим ячейкам - ничего не происходит. Я хочу, чтобы Excel интерпретировал эти значения как даты (например, работает функция YEAR , или я могу сравнить эти значения в COUNTIFS (сейчас я не могу, но мне это нужно)). Последнее, что я попробовал, это пользовательский формат: [$-409]ДД/ММ/ГГ чч:мм AM/PM (да, я русский пользователь). Это тоже не помогает, я застрял.

Как я могу заставить Excel разбирать его правильно?

Answer 1

Вы можете создать справочную таблицу с английскими названиями месяцев в первом столбце и номером месяца в следующем столбце. Затем вы можете использовать формулу Date() и использовать Vlookup, чтобы получить номер месяца:

=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))

Если вы не хотите использовать таблицу поиска, вы можете сделать это за один шаг с помощью поиска внутри формулы:

=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))

Обе формулы учитывают, что день может быть только одной цифрой. Кроме того, поскольку функция Date() будет интерпретировать двухзначный год, например, "12", как 1912, необходимо добавить 2000 к дате. Это будет работать, только если все даты больше 1/1/2000, конечно.