Как преобразовать текст в дату в Google Таблицах

Содержание:

Работа с датами в Google Таблицах иногда может быть довольно неприятной. Это потому, что существует множество способов представления дат.

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

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

Зачем преобразовывать текст в даты?

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

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

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

В этом руководстве мы покажем вам, как узнать, отображается ли значение как TEXT (ТЕКСТ) или DATE(ДАТА), и как преобразовать текстовые значения на дату в Google Таблицах.

Как узнать, действительно ли дата является текстовым значением?

Простой способ понять, является ли значение в ячейке датой или нет, — это использовать функцию ISDATE.

Если значение является датой, принятой в Google Таблицах, функция возвращает ИСТИНА, в противном случае она возвращает ЛОЖЬ.

Допустим, у нас есть даты, как показано на следующем снимке экрана:

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

= ISDATE (A2)

Поскольку дата в ячейке A2 нашего примера не принимается в Google Таблицах в качестве значения даты (для нашего местоположения), функция возвращает FALSE.

Однако та же формула, примененная к ячейке A3, вернет ИСТИНА, поскольку это действительная дата.

Есть много других способов узнать, действительно ли дата является ТЕКСТОВЫМ значением:

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

Как преобразовать текст в дату в Google Таблицах

Как только вы определили, что дата действительно интерпретируется Google Таблицами как текстовое значение, вы можете преобразовать ее в дату.

Метод преобразования текущего текста зависит от вашей ситуации. Вот некоторые ситуации, когда дату можно интерпретировать как текст в Google Таблицах:

Предыдущий пользователь мог работать с листом и сохранять значения даты в виде текста, используя апостроф (‘) перед значением даты или преобразовывая дату в формат обычного текста.
Ваши даты могут быть введены в формате, отличном от формата даты, принятого в вашей версии Google Таблиц. Форматы даты, понятные в Google Таблицах, зависят от региона и языковых настроек. Например, у вас может быть дата в формате дд-мм-гггг, тогда как Google Таблицы принимает даты только в формате мм-дд-гггг в вашем регионе или языковых настройках.
Возможно, вы импортировали PDF-файл в Google Таблицы и обнаружили, что даты указаны в странной текстовой форме, что-то вроде того, что показано ниже:

Давайте посмотрим на все три из вышеперечисленных сценариев на трех отдельных примерах.

Использование функций DATEVALUE и TO_DATE для преобразования текста в дату в Google Таблицах

Мы можем использовать комбинацию функций DATEVALUE и TO_DATE для преобразования даты, которая изначально находится в формате TEXT, в дату, которая находится в формате DATE Google Таблиц.

Функция DATEVALUE принимает значение даты в любом допустимом формате и возвращает ту же дату, что и серийный номер.

Примечание . По умолчанию все даты в Google Таблицах хранятся в виде целых чисел или серийных номеров, что упрощает их использование в расчетах. Серийные номера начинаются с 1 января 1900 года и увеличиваются на 1 каждый день после этого. Итак, 1 января 1900 года — это порядковый номер «1», следующий день — это порядковый номер «2» и так далее.

Синтаксис функции DATEVALUE следующий:

DATEVALUE(date_string)

Здесь date_string — это дата в формате ТЕКСТ. Строки, принимаемые этой функцией, включают любую дату, которая обычно автоматически преобразуется при вводе без кавычек в ячейку. Дата также должна быть в формате, понятном Google Таблицам в вашем регионе и в языковых настройках.

Функция TO_DATE преобразует заданный серийный номер в соответствующую дату (в формате Google Таблицы DATE). Синтаксис функции TO_DATE следующий:

TO_DATE(value)

Здесь значение — это число, которое нужно преобразовать в дату. Это может быть числовое значение или ссылка на ячейку, содержащую числовое значение.

Возьмем пример. Допустим, у вас есть следующая дата:

Обратите внимание на начальный апостроф, означающий, что это ТЕКСТОВОЕ значение. Чтобы преобразовать эту дату в формате TEXT в правильное значение DATE, вам необходимо использовать функцию TO_DATE вместе с функцией DATEVALUE, как показано ниже:

=TO_DATE(DATEVALUE(A2))

Функция DATEVALUE преобразует текстовую дату в числовое значение, а функция TO_DATE примет это число и преобразует его обратно в фактическую дату, как показано на изображении ниже:

Изменение местоположения или языковых настроек электронной таблицы для преобразования текста в дату

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

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

Вы можете изменить принятый формат даты следующим образом:

Перейдите в Файл-> Настройки электронной таблицы.
Выберите вкладку «Общие».
В разделе «Язык и часовой пояс» выберите требуемый параметр.
Щелкните Сохранить настройки.

Устанавливая языковой стандарт и часовой пояс для вашей электронной таблицы, вы меняете валюту, дату и числовые форматы по умолчанию.

Преобразование текста в дату, когда дата указана в 8-значном числовом формате

Если ваша таблица Google импортирована из PDF-файла, нередко можно найти даты, отформатированные в странной текстовой форме, например, в виде 8-значного числа, как показано ниже:

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

Вот шаги, чтобы преобразовать данную 8-значную текстовую дату в фактическое значение DATE:

Извлеките год. Он состоит из последних 4 цифр текстовой даты (справа от значения). Для этого мы используем функцию RIGHT следующим образом: RIGHT (A2,4)
Извлеките месяц. Он состоит из 3-й и 4-й цифр текстовой даты (которые находятся в середине значения). Для этого мы используем функцию MID следующим образом: MID (A2,3,2). Здесь мы извлекаем 2 цифры, начиная с 3-й цифры.
Извлеките день. Он состоит из первых двух цифр текстовой даты (слева от значения). Для этого мы используем функцию LEFT следующим образом: LEFT (A2,2)
Наконец, объедините их вместе в функцию DATE, чтобы создать значение ДАТА с извлеченными значениями дня, месяца и года.

Итак, окончательная формула будет такой:

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

Это должно дать дату в правильном формате, как показано ниже:

Важно отметить, что не все текстовые даты импортируются именно в этом формате. Порядок дня, месяца и года может варьироваться от случая к случаю.

Уловка состоит в том, чтобы эффективно использовать функции RIGHT, LEFT и MID для извлечения правильных чисел, представляющих день, месяц и год.

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

Обратите внимание, что в последней формуле на приведенном выше снимке экрана значение года в исходной текстовой дате содержит только 2 цифры (вместо 4).

Итак, чтобы получить 2020 год, мы объединили 20 со значением года, используя формулу: 20 & RIGHT (A4,2). Если мы этого не сделаем, таблицы Google вернут значение 1920 года вместо 2020 года.

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

В этом руководстве мы показали вам три случая, когда вы можете найти даты, представленные в виде ТЕКСТА в таблицах Google.

Мы также показали вам, как преобразовать эти значения TEXT в фактические значения DATE. Мы надеемся, что вы нашли этот урок полезным.

Вас могут заинтересовать следующие статьи: