Текстовая дата в Excel

Вы когда-нибудь сталкивались с колонкой дат, с которой вы ничего не могли сделать? Ну то есть ни определить день недели, ни номер месяца, ни прибавить сколько-нибудь дней?

Не так давно мне задали вопрос – что делать с такими данными, как их исправить. Выглядели данные нормально, то есть «1 января 2010», «10 октября 2008», «17 ноября 2009» и т.д.. Но если в соседнем столбце попытаться определить день недели с помощью функции НОМНЕДЕЛИ (WEEKNUM), Excel выдаст ошибку #ЗНАЧ! (#VALUE!)

Скорее всего это происходит из-за того, что изначально дата была введена в текстовом формате (или импортировалась в текстовом формате), и Excel не «распознал» в ней дату, с которой он был бы готов работать.

Поэтому задача звучит так – как из текста получить дату в формате, «понятном» Excel.

Представим, что в файле Excel, начиная с ячейки A2 и ниже, в первом столбце расположены такие даты. Первым делом нам необходимо вычленить из этого текста номер дня и номер года в соседних столбцах. Для этого используем функции ЛЕВСИМВ(LEFT), ПРАВСИМВ(RIGHT), ПОИСК(SEARCH), ПСТР(MID)

Text_data_Img

Заполняем столбец B – номер дня

С помощью формулы для определения дня мы отрежем такое количество символов, сколько расположено до первого пробела. Чтобы не захватить первый пробел, используем -1. Итого формула для определения дня (для ячейки B2):

[code lang=sh]
=ЛЕВСИМВ(A2;ПОИСК(" ";A2)-1)
=LEFT(A2;SEARCH(" ";A2)-1)
[/code]

Заполняем столбец C – год

С годом проще – мы знаем, что это всегда 4 символа, поэтому можно просто указать

[code lang=sh]
=ПРАВСИМВ(A2;4)
=RIGHT(A2;4)
[/code]

Заполняем столбец D – три буквы месяца

С месяцем немного сложнее – его нужно получить в два этапа. Во-первых, получить текстовое определение месяца, затем найти номер месяца, чтобы потом его можно было бы использовать. Определить месяц можно по первым трем буквам. Их мы получим опять же через поиск символа и функцию ПСТР(MID):

[code lang=sh]
=ПСТР(A2;ПОИСК(" ";A2)+1;3)
=MID(A2;SEARCH(" ";A2)+1;3)
[/code]

Заполняем столбцы I и J вспомогательной таблицей

Затем, для того, чтобы преобразовать полученное текстовое значение в номер месяца, необходимо создать дополнительную табличку, в которой для каждой такой трехбуквенной аббревиатуры будет указан номер месяца. Для примера ее можно создать начиная с ячейки I1. В ячейке I2 – «Янв», J2 – 1, I3 – «Фев», J3 – 2, I4 – «Мар», J4 – 3 и т.д.. Обратите внимание, что дата, записанная текстом будет использовать другое окончание для месяца май – мая, поэтому нужно сделать соответствующую корректировку в случае автоматического заполнения списка.

Заполняем столбец E – номер месяца

После этого мы можем осуществить подстановку номера месяца по ее трехбуквенной аббревиатуре при помощи функции ВПР(VLOOKUP)

[code lang=sh]
=ВПР(D2;$I$2:$J$13;2;ЛОЖЬ)
=VLOOKUP(D2;$I$2:$J$13;2;FALSE)
[/code]

Заполняем столбец F – дата

Осталось только скрепить воедино дату при наличии дня, месяца и года. Для этого используем функцию ДАТА(DATE):

[code lang=sh]
=ДАТА(C2;E2;B2)
=DATE(C2;E2;B2)
[/code]

И в столбце F мы получаем дату, которую понимает Excel, с которой можно делать много чего интересного — начиная от простого формата чисел, и заканчивая функция ЧИСТРАБДНИ(NETWORKDAYS) и так далее.

Ссылка на скачивание примера: Text_data_sample

Оставьте комментарий

Этот сайт использует Akismet для борьбы со спамом. Узнайте, как обрабатываются ваши данные комментариев.