Есть много способов преобразовать даты, отформатированные как текст, в фактические даты в Excel, и это руководство призвано изучить их все.
Когда вы импортируете данные в Excel, они могут принимать разные формы, которые Excel не распознает. Иногда, когда даты импортируются из внешнего источника, они приходят в формате текста.
Также важно знать, что Excel применяет форматы даты в зависимости от настроек региона вашей системы. Таким образом, когда вы импортируете в Excel данные из другой страны, Excel может не распознать их и сохранить как текстовые записи.
Если у вас возникла эта проблема, вы можете использовать множество способов преобразования текста в дату в Excel, и мы рассмотрим их все в этом руководстве.
Способы преобразования текста в дату
Если ваш рабочий лист содержит даты в текстовом формате, а не фактические даты, их невозможно использовать в каких-либо расчетах. Итак, вам нужно преобразовать их обратно в реальные даты.
Если вы видите, что даты выровнены по левому краю, это означает, что они отформатированы как текст, потому что тексты по умолчанию выровнены по левому краю. Число и даты всегда выравниваются по правому краю.
Существует несколько различных способов преобразования текста в дату в Excel:
- Возможность проверки ошибок
- Функция преобразования текста в столбцы в Excel
- Найти и заменить
- Вставить специальный инструмент
- Формула и функции Excel
Преобразование текста в дату с помощью опции проверки ошибок
В Excel есть встроенная функция проверки ошибок, которая обнаруживает некоторые очевидные ошибки в ваших данных. Если он обнаружит какую-либо ошибку, он покажет вам небольшой зеленый треугольник (индикатор ошибки) в верхнем левом углу ячейки, в которой есть ошибка. Если вы выберете эту ячейку, появится предупреждающий знак с желтым восклицательным знаком. При наведении курсора на этот знак Excel сообщит вам о возможной проблеме с этой ячейкой.
Например, когда вы вводите год в двузначном формате в дату, Excel принимает эту дату как текст и сохраняет ее как текст. И если вы выберете эту ячейку, появится восклицательный знак с предупреждением: «Эта ячейка содержит строку даты, представленную только двумя цифрами года».
Если в ваших ячейках отображается этот индикатор ошибки, щелкните восклицательный знак, и он отобразит несколько параметров для преобразования дат, отформатированных как текст, в фактические даты. Excel покажет вам варианты преобразования в 19XX или 20XX (19XX для 1915 г., 20XX для 2015 г.). Выберите подходящий вариант.
Затем текст будет преобразован в правильный формат даты.
Как включить опцию проверки ошибок в Excel
Обычно в Excel по умолчанию включена проверка ошибок. Если функция проверки ошибок не работает, вам необходимо включить проверку ошибок в Excel.
Для этого перейдите на вкладку «Файл» и выберите «Параметры» на левой панели.
В окне параметров Excel нажмите «Формулы» на левой панели, а на правой панели включите «Включить фоновую проверку ошибок» в разделе «Проверка ошибок». И отметьте «Ячейки, содержащие годы, представленные двумя цифрами» в разделе правил проверки ошибок.
Преобразование текста в дату с помощью функции текста Excel в столбец
Текст в столбец - отличная функция в Excel, позволяющая разбивать данные на несколько столбцов, а также мощный инструмент для преобразования текстовых значений в значения даты. Этот метод распознает несколько различных форматов данных и преобразует их в правильный формат даты.
Преобразование простых текстовых строк в даты
Допустим, ваши даты отформатированы в текстовых строках следующим образом:
Вы можете использовать мастер преобразования текста в столбцы, чтобы быстро преобразовать их в даты.
Сначала выберите диапазон текстовых записей, которые вы хотите преобразовать в даты. Затем перейдите на вкладку «Данные» на ленте и выберите параметр «Текст в столбцы» в группе «Инструменты для работы с данными».
Появится мастер преобразования текста в столбцы. На шаге 1 мастера преобразования текста в столбец выберите параметр «С разделителями» в разделе «Исходный тип данных» и нажмите «Далее».
На шаге 2 снимите все флажки «Разделители» и нажмите «Далее».
На последнем шаге мастера выберите «Дата» в разделе «Формат данных столбца», выберите формат даты в раскрывающемся списке рядом с «Даты» и нажмите кнопку «Готово». В нашем случае мы конвертируем текстовые даты, представленные как «01 02 1995» (день месяц год), поэтому мы выбираем «DMY» из раскрывающегося списка «Date:».
Теперь Excel преобразует ваши текстовые даты в фактические даты и отображает их в ячейках с выравниванием по правому краю.
Примечание: Прежде чем начать использовать функцию преобразования текста в столбец, убедитесь, что все ваши текстовые строки имеют одинаковый формат, иначе тексты не будут преобразованы. Например, если одни из ваших текстовых дат отформатированы как месяц / день / год (MDY), а другие - день / месяц / год (DMY), и когда вы выберете «DMY» на шаге 3, вы получите неверные результаты. Как показано на картинке ниже.
Преобразование сложной текстовой строки в дату
Функция «Текст в столбцы» пригодится, если вы хотите преобразовать сложные текстовые строки в даты. Это позволяет вам использовать разделители, чтобы определить, где ваши данные должны быть разделены и отображены в 2 или более столбцах. И объедините разделенные части дат в целую дату с помощью функции DATE.
Например, если ваши даты отображаются в виде текстовых строк, состоящих из нескольких частей, например:
Среда, 1 февраля 2020 г.
01 февраля 2020 г., 16.10
Вы можете использовать мастер преобразования текста в столбец, чтобы разделить информацию о дне, дате и времени, разделенную запятой, и отобразить их в нескольких столбцах.
Сначала выберите все текстовые строки, которые вы хотите преобразовать в даты. Нажмите кнопку «Текст в столбцы» на вкладке «Данные». На шаге 1 мастера преобразования текста в столбец выберите параметр «С разделителями» в разделе «Исходный тип данных» и нажмите «Далее».
На шаге 2 мастера выберите разделители, содержащиеся в ваших текстовых строках, и нажмите «Далее». В нашем примере текстовые строки, разделенные запятой и пробелом - «Понедельник, 1 февраля 2015 г., 13:00». Мы должны выбрать «запятую» и «пробелы» в качестве разделителей, чтобы разделить текстовые строки на несколько столбцов.
На последнем этапе выберите «Общий» формат для всех столбцов в разделе «Предварительный просмотр данных». Укажите, где должны быть вставлены столбцы в поле «Назначение». Если вы этого не сделаете, исходные данные будут перезаписаны. Если вы хотите проигнорировать какую-то часть исходных данных, нажмите на нее в разделе «Предварительный просмотр данных» и выберите опцию «Не импортировать столбец (пропустить)». Затем нажмите «Готово».
Теперь части дат (дни недели, месяц, год, время) разбиты на столбцы B, C, D, E, F и G.
Затем объедините части даты вместе с помощью формулы ДАТА, чтобы получить дату целиком.
Синтаксис функции Excel DATE:
= ДАТА (год, месяц, день)В нашем примере части месяца, дня и года находятся в столбцах C, D и E соответственно.
Функция ДАТА распознает только числа, но не текст. Поскольку все значения месяцев в столбце C представляют собой текстовые строки, нам необходимо преобразовать их в числа. Для этого вам нужно использовать функцию МЕСЯЦ, чтобы изменить название месяца на номер месяца.
Чтобы преобразовать название месяца в номер месяца, используйте эту функцию МЕСЯЦ внутри функции ДАТА:
= МЕСЯЦ (1 и C1)Функция МЕСЯЦ добавляет 1 к ячейке C2, которая содержит название месяца, чтобы преобразовать название месяца в соответствующий номер месяца.
Это функция DATE, которую нам нужно использовать для объединения частей даты из разных столбцов:
= ДАТА (E1; МЕСЯЦ (1 и C1); D1)
Теперь используйте маркер заполнения в нижнем углу ячейки с формулой и примените формулу к столбцу.
Преобразование текста в дату с помощью метода поиска и замены
Этот метод использует разделители для изменения формата текста на даты. Если день, месяц и год в датах разделены каким-либо разделителем, кроме тире (-) или косой черты (/), Excel не распознает их как даты и сохранит их как текст.
Чтобы решить эту проблему, используйте функцию «Найти и заменить». Заменив нестандартные разделители периодов (.) Косой чертой (/) или тире (-), Excel автоматически изменит значения на даты.
Сначала выберите все текстовые даты, которые вы хотите преобразовать в даты. На вкладке «Главная» нажмите кнопку «Найти и выбрать» в правом верхнем углу ленты и выберите «Заменить». Или нажмите Ctrl + H , чтобы открыть диалоговое окно «Найти и заменить».
В диалоговом окне «Найти и заменить» введите разделитель текста (в нашем случае точка (.) В поле «Найти» и косую черту (/) или тире (-) в поле «Заменить на»). Нажмите кнопку «Заменить все», чтобы заменить разделители, и нажмите «Закрыть», чтобы закрыть окно.
Теперь Excel распознает, что ваши текстовые строки теперь являются датами, и автоматически форматирует их как даты. Ваши даты будут выровнены по правому краю, как показано ниже.
Преобразование текста в дату с помощью специального инструмента «Вставить»
Еще один быстрый и простой способ преобразовать текстовые строки в даты - добавить к текстовой строке 0 с помощью специальной опции «Вставить». Добавление нуля к значению преобразует текст в порядковый номер даты, который можно отформатировать как дату.
Сначала выберите пустую ячейку и скопируйте ее (выделите и нажмите Ctrl + C копировать).
Затем выберите ячейки, содержащие текстовые даты, которые вы хотите преобразовать, щелкните правой кнопкой мыши и выберите параметр «Специальная вставка».
В диалоговом окне «Специальная вставка» выберите «Все» в разделе «Вставить», выберите «Добавить» в разделе «Операция» и нажмите «ОК».
Вы также можете выполнять другие арифметические операции: вычитание / умножение / деление значения в целевой ячейке на вставленное значение (например, умножение ячеек на 1, деление на 1 или вычитание нуля).
Когда вы выбираете «Добавить» в операции, он добавляет «ноль» ко всем выделенным текстовым датам, поскольку добавление «0» не меняет значений, вы получите серийный номер для каждой даты. Теперь все, что вам нужно сделать, это изменить формат ячеек.
Выберите серийные номера и на вкладке «Главная» щелкните раскрывающийся список «Формат номера» в группе номеров. В раскрывающемся списке выберите вариант «Краткая дата».
Как видите, теперь числа отформатированы как даты и выровнены по правому краю.
Преобразование текста в дату с помощью формул
Для преобразования текущего текста в основном используются две функции: DATEVALUE и VALUE.
Использование функции Excel DATEVALUE
Функция Excel DATEVALUE - один из самых простых способов преобразовать дату, представленную в виде текста, в порядковый номер даты.
Синтаксис функции ДАТАЗНАЧ:
= ДАТАЗНАЧ (текст_даты)Аргумент: date_text указывает текстовую строку, которую вы хотите скрыть, или ссылку на ячейку, содержащую текстовые даты.
Формула:
= ДАТАЗНАЧ (A1)На следующем рисунке показано, как функция ДАТАЗНАЧ обрабатывает несколько различных форматов даты, которые хранятся в виде текста.
У вас есть порядковые номера даты, теперь вам нужно применить к этим числам формат даты. Для этого выберите ячейки с серийными номерами, затем перейдите на вкладку «Главная» и выберите «Краткая дата» из раскрывающегося списка «Числовой формат».
Теперь у вас есть отформатированные даты в столбце C.
Даже если в текстовой дате (A8) нет части года, DATEVALUE будет использовать текущий год по часам вашего компьютера.
Функция ДАТАЗНАЧ преобразует только те текстовые значения, которые выглядят как дата. Он не может преобразовать текст, напоминающий число на текущий момент, и не может изменить числовое значение на дату, для этого вам понадобится функция Excel VALUE.
Использование функции Excel VALUE
Функция Excel VALUE может преобразовывать любую текстовую строку, напоминающую дату или число, в числовое значение, поэтому она очень полезна при преобразовании любых чисел, а не только дат.
Функция ЗНАЧЕНИЕ:
= ЗНАЧЕНИЕ (текст)текст- текстовая строка, которую мы хотим преобразовать, или ссылка на ячейку, содержащую текстовую строку.
Пример формулы для преобразования текстовой даты:
= ЗНАЧЕНИЕ (A1)Приведенная ниже формула VALUE может преобразовать любые текстовые строки, которые выглядят как дата, в число, как показано ниже.
Однако функция VALUE поддерживает не все типы значений даты. Например, если в датах используются десятичные знаки (A11), возвращается #VALUE! ошибка.
Когда у вас есть серийный номер для вашей даты, вам нужно будет отформатировать ячейку с серийным номером даты, чтобы она выглядела как дата, как мы сделали для функции DATEVALUE. Для этого выберите серийные номера и выберите опцию «Дата» в раскрывающемся меню «Числовой формат» на вкладке «Главная».
Вот и все, это 5 различных способов преобразования дат, отформатированных как текст, в даты в Excel.