Как преобразовать текст в дату в Excel

Q: В чем разница между использованием функций ДАТАЗНАЧ и ЗНАЧЕН для преобразования текста в дату?

Функция ДАТАЗНАЧ специально предназначена для преобразования текстовых строк, представляющих даты, в серийные номера, которые Excel распознает как даты. Она игнорирует любую информацию о времени, если она присутствует. Функция ЗНАЧЕН более универсальна: она преобразует текст, представляющий числа, даты или время, в числовое значение. Таким образом, если текст включает время, ЗНАЧЕН сохранит его, в то время как ДАТАЗНАЧ проигнорирует.

Мар 20, 2025 | Дата и время | Нет комментариев

Даты

Home 9 Дата и время 9 Как преобразовать текст в дату в Excel

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

Часто при работе с данными, импортированными из других источников, таких как файлы .csv или программы вроде 1С, даты попадают в таблицу Excel в виде текста. Внешне они могут выглядеть как даты, но Excel не воспринимает их как таковые. Это создает проблемы при сортировке, использовании их в формулах или создании графиков.

Мы подробно разберем, как отличить текстовые даты от реальных, а также как их преобразовать в нормальные даты с помощью различных подходов.

Содержание

Отличие текстовых дат от реальных дат в Excel

Даты, импортированные как текст, могут визуально походить на обычные даты, но Excel определяет их как текстовые строки. Это означает, что вы не сможете использовать их в формулах, сводных таблицах или графиках, а также правильно отсортировать данные по этим датам.

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

Реальные даты выровнены по правому краю ячейки.
В поле «Числовой формат» на вкладке «Главная» для реальных дат указан формат даты.
Текстовые значения по умолчанию выровнены по левому краю.
В поле «Число» на вкладке «Главная» для текстовых значений отображается «Общий» формат.
Иногда перед текстом в ячейке может стоять апостроф, который виден в строке формул.

Что такое текстовые даты или даты записанные как текст

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

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

Подробнее о различных форматах дат вы можете прочитать здесь: Формат даты в Excel.

Использование функции ДАТАЗНАЧ для преобразования текста в дату

Функция ДАТАЗНАЧ в Excel позволяет преобразовать текст, представляющий дату, в серийный номер, который Excel интерпретирует как реальную дату. Это особенно полезно для данных, импортированными из внешних источников. Например, для бухгалтерских отчетов.

Синтаксис функции прост:

=ДАТАЗНАЧ(текстовая_дата)

Например, если в ячейке A1 находится текст «08.03.2020», формула =ДАТАЗНАЧ(A1) вернет серийный номер 43898. Он соответствует 8 марта 2020 года. Чтобы увидеть дату в привычном виде, необходимо отформатировать ячейку как дату. Для этого выделите ячейку, нажмите горячие клавиши Ctrl + 1. Затем перейдите на вкладку «Число», выберите категорию «Дата» и определите желаемый формат.

Преобразование текста в дату при помощи форматирования ячейк

Функция ДАТАЗНАЧ может работать с различными форматами дат, такими как ‘08.03.2020’ или ‘8/3/2020’, в зависимости от системных настроек вашего компьютера. Однако, если дата представлена в виде ‘8 марта 2020’, функция ДАТАЗНАЧ может не распознать её напрямую. В таких случаях можно использовать комбинацию функций для извлечения даты, как описано далее в разделе о преобразовании сложных текстовых строк в даты.

Примечание: Если текст не распознается как дата, функция вернет ошибку #ЗНАЧ! Убедитесь, что формат даты соответствует ожидаемому Excel. Например, если вместо точек использованы запятые (‘08,03,2020’), можно использовать функцию ПОДСТАВИТЬ для замены:

=ДАТАЗНАЧ(ПОДСТАВИТЬ(A1;",";"."))

Совет: Помните, что если дата содержит время, функция ДАТАЗНАЧ игнорирует время, возвращая только дату. Для включения времени используйте ЗНАЧЕН.

Функция ЗНАЧЕН: универсальный способ преобразовать текст в число или дату

Функция ЗНАЧЕН в Excel более гибкая, чем ДАТАЗНАЧ, поскольку она может преобразовать любую текстовую строку, содержащую числа или даты, в числовое значение. Это позволяет затем отформатировать полученный результат как дату или время.

Синтаксис функции:

=ЗНАЧЕН(текст)

Где ‘текст’ — это строка или ссылка на ячейку с текстом, который нужно преобразовать.

ЗНАЧЕН может обрабатывать как даты, так и время. Например, если в ячейке A1 находится ‘08.03.2020 14:30’, формула =ЗНАЧЕН(A1) вернет серийный номер, который включает и дату, и время. Для отображения только даты можно отформатировать ячейку как дату, а для отображения времени — как время.

Преобразование текста в дату в Excel при помощи функции ЗНАЧЕН

Совет: В отличие от ДАТАЗНАЧ, ЗНАЧЕН может работать с текстом, который включает как дату, так и время, делая его более универсальным инструментом.

Преобразовать текст в дату с помощью математических операций Excel

Если вы предпочитаете не использовать специальные функции, можно воспользоваться простыми математическими операциями. Они заставят Excel интерпретировать текст как число, а затем можно отформатировать его как дату.

Для ячейки с текстовой датой в A1 можно использовать:

Сложение: =A1 + 0
Умножение: =A1 * 1
Деление: =A1 / 1
Двойное отрицание: =—A1

Эти операции не изменяют значение, но принуждают Excel рассматривать текст как число, которое затем можно отформатировать как дату.

Можно преобразовать текст дату при помощи математических операций Excel

Такой метод работает как для дат, так и для времени, а также для чисел, отформатированных как текст. Иногда результат даже отображается в виде даты автоматически, и вам не нужно беспокоиться об изменении формата ячейки.

Преобразование дат с нестандартными разделителями

Если ваши текстовые даты используют разделители, отличные от ‘/’ или ‘-‘, такие как запятая или пробел, функции ДАТАЗНАЧ или ЗНАЧЕН могут не распознать их и вернуть ошибку #ЗНАЧ!.

Чтобы исправить это, можно использовать функцию ‘Найти и заменить’:

Выделите диапазон с текстовыми датами.
Нажмите Ctrl + H.
В поле ‘Найти’ введите текущий разделитель (например, ‘,’), в ‘Заменить’ введите ‘/’ (или другой стандартный разделитель, в зависимости от ваших настроек локали).
Нажмите ‘Заменить все’.

После этого функции ДАТАЗНАЧ или ЗНАЧЕН должны работать корректно.

Как заменить разделители в текстовой дате

Альтернативно, можно использовать функцию ПОДСТАВИТЬ в формуле, чтобы заменить нестандартный разделитель на стандартный:

=ДАТАЗНАЧ(ПОДСТАВИТЬ(A1;",";"/"))

Это заменит запятые на ‘/’, делая дату узнаваемой для функции ДАТАЗНАЧ.

Преобразование текста в дату при помощи формул Excel

Как преобразовать 8-значное число в дату в Excel

Это очень распространенная ситуация, когда дата записана как 8-значное число, например 10032021. Вам необходимо преобразовать ее в такой вид, который может распознать Excel (то есть, 20.03.2021). В этом случае простое изменение формата ячейки на «Дата» не пройдёт – в результате вы получите ##########.

Чтобы преобразовать это в дату, вам нужно будет использовать функцию ДАТА() в сочетании с функциями ПРАВСИМВ, ЛЕВСИМВ и ПСТР. К сожалению, невозможно составить универсальную формулу, которая будет работать всегда, потому что исходное значение может выглядеть по-разному. Например:

Число	Формат	Дата
20032021	ддммгггг	20-мар-2021
20210320	ггггммдд
20212003	ггггддмм

Нам понадобится функция ДАТА:

=ДАТА(год; месяц; день)

Нам нужно извлечь год, месяц и день из исходного числа и передать их в качестве соответствующих аргументов функции ДАТА.

Например, давайте посмотрим, как преобразовать число 20032021 (записанное в ячейке A2) в 20.03.2021.

Извлекаем год. Это последние 4 цифры, поэтому мы используем функцию ПРАВСИМВ(A2; 4).
Месяц. Это третья и четвёртая цифры, получаем их при помощи ПСТР(A2;3;2). Здесь цифра 3 (второй аргумент) – это начальная позиция, а 2 – это количество символов, которые нужно извлечь.
День. Это первые две цифры, возвращаем их: ЛЕВСИМВ(A2;2).

Наконец, вставьте эти выражения в функцию ДАТА, и вы получите формулу для преобразования числа в дату в Excel:

=ДАТА(ПРАВСИМВ(A2;4);ПСТР(A2;3;2);ЛЕВСИМВ(A2;2))

Как преобразовать число в дату при помощи формул Excel

Обратите внимание на строку 5 на скриншоте выше:

=ДАТА("20"&ЛЕВСИМВ(A5;2);ПРАВСИМВ(A5;2);ПСТР(A5;3;2))

Исходное число (212003) содержит только два символа, обозначающих год (21). Итак, чтобы получить 2021 год, добавляем ещё две цифры:

«20»&ЛЕВСИМВ(A5;2)

Если вы не сделаете этого, функция ДАТА возвратит 1921 год.

В том случае, если число представляет собой последовательность ДДММГГГ или ГГГММДД, то для конвертации в дату можно использовать функцию ТЕКСТ.

К примеру, число 20210320 можно преобразовать в дату при помощи формулы

=--ТЕКСТ(A3;"0000\/00\/00")

Осталось применить подходящий формат даты.

Примечание. Формулы, показанные в этом примере, работают правильно, если все числа, которые вы хотите преобразовать в даты, подчиняются одному и тому же определённому шаблону.

Преобразование сложных текстовых строк в даты с помощью текстовых функций

Для более сложных текстовых выражений, таких как ’08 марта 2025г.’, можно использовать функции работы с текстом. Извлекаем день, месяц и год, а затем объединяем их в формат, который понимает ДАТАЗНАЧ.

Предположим, что в ячейке A2 находится ’08 марта 2025г.’.

Мы можем использовать следующую формулу:

=ДАТАЗНАЧ(ЛЕВСИМВ(A2;2)&"-"&ПСТР(ЛЕВСИМВ(A2;6);4;3)&"-"&ПСТР(ПРАВСИМВ(A2;6);1;4))

Вот как это работает:

ЛЕВСИМВ(A2;2) — получает первые два символа (’08’)
ПСТР(ЛЕВСИМВ(A2;6);4;3) — извлекает три символа, начиная с четвертого символа из первых шести (‘мар’)
ПСТР(ПРАВСИМВ(A2;6);1;4) — извлекает четыре символа, начиная с первого из последних шести (‘2025’)

Затем мы объединяем эти части с дефисами: ’08-мар-2025′, что ДАТАЗНАЧ может преобразовать в дату.

Обратите внимание, что этот метод требует, чтобы текст был единообразным, то есть дни всегда двузначные, месяцы на русском и т.д.

Извлечение дат из текстовых предложений

В бухгалтерских документах даты часто встречаются в различных форматах внутри текстовых пояснений. Например, ‘Оплата по счету от 07.11.2024’ или ‘Договор заключен 7 ноября 2024 г.’. Для автоматизации анализа таких данных полезно извлекать даты из текста.

Для этого можно использовать комбинацию функций Excel, таких как ПСТР, ПОИСК, ЕЧИСЛО и другие.

Например, если дата всегда начинается с ‘??.??.’, где ‘?’ — цифры, можно использовать формулу:

=--ПСТР(A2;ПОИСК("??.??.";A2);8+2*ЕЧИСЛО(-ПСТР(A2;ПОИСК(".??.";A2)+6;1)))

Эта формула находит позицию даты в тексте и извлекает её, преобразуя в числовое значение.

Для форматирования извлеченной даты как строки можно использовать функцию ТЕКСТ:

=ТЕКСТ(ПСТР(A1;ПОИСК("??.??.";A1);8)&ТЕКСТ(ПСТР(A1;ПОИСК(".??.";A1)+6;2);"0;;;");"DD MMMM YYYY")

Это позволяет получить дату в виде ’07 ноября 2024′.

Для более гибкого подхода, когда дата может быть в разных форматах, можно использовать массивные формулы, которые охватывают несколько возможных вариантов написания даты.

Например:

{=ЕСЛИОШИБКА(--ЛЕВСИМВ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПСТР(A2;МИН(ЕСЛИОШИБКА(НАЙТИ(ТЕКСТ(СТРОКА($A$2:$A$363);{"dd.mm.";"d.mm.";"dd/mm/";"d/mm/"});A2);9^9));10);" ";" ");"г";" ");10);"")}

Эта формула массива пытается найти дату в различных форматах и извлечь её.

Для автоматического форматирования можно обернуть результат в функцию ТЕКСТ с подходящим форматом.

Хотя такие формулы сложны, они могут значительно упростить обработку больших объемов данных.

Таблица сравнения методов преобразования текста в дату

Метод	Преимущества	Ограничения
Функция ДАТАЗНАЧ	Простота использования, подходит для стандартных форматов	Игнорирует время, может не работать с нестандартными форматами
Функция ЗНАЧЕН	Универсальна, работает с датами и временем	Требует форматирования для отображения как даты
Математические операции	Не требует формул, простота	Может не работать с нестандартными форматами дат
Функции текста (ПСТР, ЛЕВСИМВ)	Подходит для сложных текстовых строк	Требует точного знания структуры текста

Эта таблица помогает пользователям выбрать подходящий метод чтобы в Excel преобразовать текст в дату в зависимости от имеющихся данных.

Часто задаваемые вопросы

Как преобразовать текст, который выглядит как дата (например, «08/03/2025»), в настоящую дату в Excel?

Если ваши даты хранятся как текст, их можно легко преобразовать в настоящие даты несколькими способами. Один из самых простых — использовать функцию ДАТАЗНАЧ. Например, если текстовая дата в ячейке A1, вставьте в другую ячейку формулу =ДАТАЗНАЧ(A1). Это преобразует текст в серийный номер, который Excel распознает как дату. Затем отформатируйте ячейку как дату (например, через «Формат ячеек» > «Дата»).
Если вы видите зеленый треугольник в углу ячейки с датой, это означает, что Excel распознал ее как текст. Наведите на ячейку, и появится восклицательный знак. Кликните на него, и вы увидите опцию «Преобразовать в дату», которую можно использовать для быстрого преобразования.
Другие методы включают использование функции ЗНАЧЕН или математических операций, таких как =A1 + 0, чтобы заставить Excel интерпретировать текст как число (поскольку даты — это числа в Excel), а затем отформатировать как дату.

Почему мои даты не сортируются правильно или не работают в формулах, хотя они выглядят как даты?

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

В чем разница между использованием функций ДАТАЗНАЧ и ЗНАЧЕН для преобразования текста в дату?

Функция ДАТАЗНАЧ специально предназначена для преобразования текстовых строк, представляющих даты, в серийные номера, которые Excel распознает как даты. Она игнорирует любую информацию о времени, если она присутствует.
Функция ЗНАЧЕН более универсальна: она преобразует текст, представляющий числа, даты или время, в числовое значение. Таким образом, если текст включает время, ЗНАЧЕН сохранит его, в то время как ДАТАЗНАЧ проигнорирует.

Как преобразовать текстовые даты, которые используют запятые или другие нестандартные разделители?

Если текстовые даты содержат нестандартные разделители, такие как запятые (например, «08,03,2025»), функции Excel могут не распознать их как даты. В этом случае можно использовать функцию ПОДСТАВИТЬ, чтобы заменить нестандартный разделитель на стандартный (например, на косую черту «/»). Например: =ДАТАЗНАЧ(ПОДСТАВИТЬ(A1;»,»;»/»)).
Также можно использовать инструмент «Найти и заменить» (Ctrl + H), чтобы заменить все запятые на косые черты в выбранном диапазоне, а затем применить ДАТАЗНАЧ.

Как извлечь дату из текстовой строки, содержащей дополнительную информацию, например «Счет от 08/03/2025»?

Для извлечения даты из сложного текста можно использовать функции работы с текстом, такие как ЛЕВСИМВ, ПРАВСИМВ, ПСТР и ПОИСК. Эти функции позволяют выделить нужные части строки. Например, если дата всегда находится в конце строки и имеет фиксированный формат, можно использовать ПСТР, чтобы извлечь определенное количество символов.
В более сложных случаях может потребоваться использование массивных формул или других продвинутых методов для точного извлеяения из текста нужных элементов.

Существуют ли способы преобразовать текст в даты без использования формул?

Да, есть несколько способов. Один из них — использовать математические операции, такие как =A1 + 0 или =A1 * 1. Это заставляет Excel интерпретировать текст как число (поскольку даты — это числа), а затем можно отформатировать ячейку как дату.
Другой способ — использовать функцию «Текст в столбцы» (Данные > Текст в столбцы > Разделенные символами). Иногда это автоматически преобразует текст в даты.
Также можно скопировать пустую ячейку, выделить диапазон с текстовыми датами и использовать «Специальная вставка» (Сложение), что эквивалентно добавлению нуля.

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

Совет: Всегда проверяйте формат ячеек после преобразования, чтобы убедиться, что даты отображаются корректно. Если возникают ошибки, проверьте локальные настройки и форматы дат.

Функция СЕГОДНЯ и текущая дата в Excel

Формат даты в Excel

Формат времени в Excel

Разность дат и количество дней между датами в Excel

Разница дат в часах и минутах в Excel

Как сложить, вычесть и посчитать время в Excel