Формула Excel: преобразование текста в дату -

Содержание

Общая формула

=DATE(LEFT(text,4),MID(text,5,2),RIGHT(text,2))

Резюме

Чтобы преобразовать текст в нераспознанном формате даты в правильную дату Excel, вы можете проанализировать текст и собрать правильную дату с помощью формулы, основанной на нескольких функциях: DATE, LEFT, MID и RIGHT. В показанном примере формула C6:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

Эта формула извлекает значения года, месяца и дня отдельно и использует функцию ДАТА для объединения их в дату 24 октября 2000 г.

Задний план

Когда вы работаете с данными из другой системы, вы можете столкнуться с ситуацией, когда даты не распознаются должным образом в Excel, который вместо этого обрабатывает даты как текст. Например, у вас могут быть такие текстовые значения:

Текст	Дата представления
20001024	24 октября 2000 г.
20050701	1 июля 2011 г.5
19980424	24 апреля 1998 г.
28.02.2014	28 февраля 2014 г.

Когда Excel оценивает значение даты как текст, можно использовать формулу для разбора текста на его компоненты (год, месяц, день) и использовать их для создания даты с функцией ДАТА. Как отмечалось выше, я рекомендую вам сначала попробовать решения ниже (добавление нуля и использование текста в столбцах), прежде чем использовать формулу. Оба обходных пути быстрее и требуют меньше усилий.

Объяснение

Функция ДАТА создает допустимую дату, используя три аргумента: год, месяц и день:

=DATE(year,month,day)

В ячейке C6 мы используем функции LEFT, MID и RIGHT для извлечения каждого из этих компонентов из текстовой строки и передачи результатов в функцию DATE:

=DATE(LEFT(B6,4),MID(B6,5,2),RIGHT(B6,2))

Функция LEFT извлекает 4 крайних левых символа для года, функция MID извлекает символы в позициях 5-6 для месяца, а функция RIGHT извлекает 2 крайних правых символа как день. Каждый результат возвращается непосредственно в функцию DATE. Конечным результатом является правильная дата в Excel, которую можно отформатировать как угодно.

Этот подход можно настроить по мере необходимости. Например, нераспознанный формат даты в строке 8 - дд.мм.гггг, а формула в C8:

=DATE(RIGHT(B8,4),MID(B8,4,2),LEFT(B8,2))

Текст в длинной форме

Иногда у вас могут быть даты в более длинной форме, например «11 апреля 2020 г. 08:43:13», которые Excel не распознает должным образом. В этом случае вы можете настроить строку таким образом, чтобы Excel мог правильно распознавать дату с помощью функции ЗАМЕНА. В приведенной ниже формуле второй экземпляр пробела ("") заменяется запятой и пробелом (","):

=SUBSTITUTE(A2," ",", ",2)+0 // add comma after month

После того, как мы добавим запятую после названия месяца, Excel поймет дату, но ему все равно нужно немного «пнуть». Вот почему мы добавляем ноль в конце. Математическая операция заставляет Excel пытаться преобразовать строку в число. В случае успеха это приведет к действительной дате в Excel. Обратите внимание, что для правильного отображения даты может потребоваться применить форматирование числа даты.

Без формул

Прежде чем использовать формулу для ручного анализа и построения даты из текста, попробуйте одно из следующих исправлений. Первый вариант использует математическую операцию, чтобы немного «подтолкнуть» Excel и заставить его попытаться вычислить текст как число. Поскольку даты в Excel на самом деле являются числами, это часто помогает. Вам может потребоваться применить формат даты, если операции завершатся успешно.

Добавьте ноль, чтобы исправить даты

Иногда вы можете встретить даты в текстовом формате, который должен распознавать Excel. В этом случае вы можете заставить Excel преобразовать текстовые значения в даты, добавив к значению ноль. Когда вы добавляете ноль, Excel попытается преобразовать текстовые значения в числа. Поскольку даты - это просто числа, этот трюк - отличный способ преобразовать даты в текстовый формат, который Excel действительно должен понимать.

Чтобы преобразовать даты на месте, добавив ноль, попробуйте Специальная вставка: