Поиск по сайту:

Анализ бизнес-данных часто требует работы со значениями дат в Excel, чтобы ответить на такие вопросы, как «сколько денег мы заработали сегодня» или «как это соотносится с тем же днем на прошлой неделе?» И это может быть сложно, если Excel не распознает значения как даты.

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

В этой статье мы опишем четыре различных сценария и решения для преобразования текста в значения даты.

Даты, содержащие точку/точку

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

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

Выберите столбцы, для которых вы хотите выполнить поиск и замену.

Нажмите «Главная» > «Найти и выделить» > «Заменить» или нажмите Ctrl+H.

В окне «Найти и заменить» введите точку (.) в поле «Найти» и косую черту (/) в поле «Заменить на». Затем нажмите «Заменить все».

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

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

=VALUE(SUBSTITUTE(A2,".","/"))

Функция ПОДСТАВИТЬ является текстовой функцией, поэтому сама по себе не может преобразовать ее в дату. Функция ЗНАЧ преобразует текстовое значение в числовое значение.

Результаты показаны ниже. Значение должно быть отформатировано как дата.

Сделать это можно с помощью списка «Числовой формат» на вкладке «Главная».

Пример разделителя точки здесь типичен. Но вы можете использовать ту же технику для замены любого символа-разделителя.

Преобразование формата ггггммдд

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

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

Для быстрого ручного решения вы можете использовать Text to Columns.

Выберите диапазон значений, которые необходимо преобразовать, а затем щелкните Данные > Текст в столбцы.

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

Если вам нужно решение формулы, вы можете использовать функцию Date для построения даты.

Это будет использоваться вместе с текстовыми функциями Left, Mid и Right для извлечения трех частей даты (день, месяц, год) из содержимого ячейки.

В приведенной ниже формуле показана эта формула с использованием наших выборочных данных.

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

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

Функции DATEVALUE и VALUE

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

Ниже приведен список дат в различных структурах, но все они узнаваемы для нас как дата. К сожалению, они были сохранены в виде текста и нуждаются в преобразовании.

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

В этой статье я хотел упомянуть две функции для обработки этих сценариев. Это DATEVALUE и VALUE.

Функция ДАТАЗНАЧ преобразует текст в значение даты (вероятно, предвидела это), в то время как функция ЗНАЧ преобразует текст в общее числовое значение. Различия между ними минимальны.

На изображении выше одно из значений также содержит информацию о времени. И это будет демонстрация мелких отличий функций.

Приведенная ниже формула DATEVALUE преобразует каждое из них в значение даты.

=DATEVALUE(A2)

Обратите внимание, что время было удалено из результата в строке 4. Эта формула строго возвращает только значение даты. Результат все равно нужно будет отформатировать как дату.

В следующей формуле используется функция ЗНАЧ.

=VALUE(A2)

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

Затем результаты можно отформатировать как дату и время или как дату, чтобы скрыть значение времени (но не удалить).