Как изменить формат даты в Google Sheets и преобразовать дату в число и текст

Даты — неотъемлемая часть Google Таблиц. И, как и многие другие концепции электронных таблиц, они требуют некоторого изучения.

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

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

Как Google Таблицы форматируют даты

Перво-наперво: перед любыми действиями, связанными с датами в электронных таблицах, крайне важно понять основные принципы работы дат.

В своей внутренней базе данных Google Sheets хранит все даты в виде целых чисел. Не последовательности дня, месяца и года, как мы привыкли видеть, а простые целые числа:

  • 1 на 31 декабря 1899 г.
  • 2 на 1 января 1900 г.
  • 102 от 11 апреля 1900 г. (100 дней после 1 января 1900 г.)
  • и так далее.

В отличие от Excel, который не может хранить даты как отрицательные числа, в Google для дат до 31 декабря 1899 года числа будут отрицательными:

  • -1 за 29 декабря 1899 г.
  • -2 за 28 декабря 1899 г.
  • -102 за 19 сентября 1899 г.
  • и т.п.

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

Кончик. То же самое относится и к единицам времени — они просто десятичные числа для вашей таблицы:

  • 0,00 на 00:00
  • 0,50 за 12:00
  • 0,125 за 3 часа ночи
  • 0,573 за 13:45
  • и т.п.

Дата в паре со временем сохраняется как целое число с десятичными разрядами:

  • 31 528,058 — 26 апреля 1986 г., 01:23.
  • 43 679,813 — 2 августа 2019 г., 19:30.

Изменить формат даты в Google Таблицах на другой язык

Еще одна важная вещь, о которой следует помнить, — это язык вашей электронной таблицы.

Языковой стандарт — это то, что настраивает формат даты в Google Таблицах в зависимости от вашего региона. Таким образом, если вы в настоящее время находитесь в США, 06 августа 2019 года будет указано в вашей таблице как 06.08.2019, а для Великобритании это будет 08.06.2019.

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

  1. Перейти к Файл > Настройки электронной таблицы в меню Google Таблиц.
  2. Находить Регион под Общий вкладку и выберите нужное место из выпадающего списка:
    Измените языковой стандарт электронной таблицы, чтобы обеспечить правильный формат даты Google Sheets.

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

Примечание. Язык не влияет на язык ваших Таблиц. Однако форматирование даты будет применено ко всей электронной таблице. Все, кто работает с ним, увидят изменения, независимо от их места на земном шаре.

Как изменить формат даты в Google Таблицах

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

Формат даты Google Sheets по умолчанию

  1. Выберите все ячейки, которые вы хотите отформатировать.
  2. Перейти к Формат > Число в меню электронной таблицы и выберите Свидание чтобы увидеть только дату или Дата время чтобы получить дату и время в ячейке:
    Как отформатировать дату в Google Таблицах.

Целые числа успешно преобразуются в формат, который вы узнаете с первого взгляда. Это форматы даты Google Таблиц по умолчанию:
Автоматический формат (целые числа) и формат даты.

Кончик. Вы можете найти те же форматы, если вы нажмете на 123 значок на панели инструментов электронной таблицы:
Еще один способ изменить формат даты в Google Таблицах.

Пользовательские форматы даты

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

Вы можете получить к ним доступ из того же меню Google Sheets: Формат > Число > Дополнительные форматы > Дополнительные форматы даты и времени:
Пользовательские форматы даты и времени Google Sheets.

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

Если вас все еще не устраивает внешний вид ваших дат, вы можете настроить свой собственный формат даты:

  1. Выберите ячейки, которые вы хотите отформатировать.
  2. Перейти к Формат > Число > Дополнительные форматы > Дополнительные форматы даты и времени.
  3. Поместите курсор в поле вверху, содержащее единицы даты, и удалите все с помощью клавиш Backspace или Delete:
    Удалите существующий пользовательский формат даты.
  4. Нажмите на стрелку справа от поля и выберите юнит, который вы хотите иметь в первую очередь. Не забудьте потом ввести разделитель.

    Повторяйте, пока не будут добавлены все необходимые единицы (не беспокойтесь, вы сможете добавить или удалить их позже):
    Добавьте столько единиц в свой собственный формат даты, сколько вам нужно.

  5. Обратите внимание, что справа от каждой единицы есть двойные стрелки. Нажмите на них, и вы сможете настроить точный способ отображения значения.

    Вот что я могу выбрать для День:
    Выберите, как день должен выглядеть в вашем формате даты.

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

  6. Когда будете готовы, нажмите Применить.

Вот какой формат я создал и как теперь выглядят мои даты:
Пользовательский формат даты Google Sheets заново.

Функция QUERY для Google Sheets для форматирования дат

Есть еще один способ изменить формат даты в Google Sheets — конечно, с помощью формулы. Поскольку QUERY я показываю вам не в первый раз, я начинаю думать о нем как о настоящей панацее для электронных таблиц. 🙂

У меня есть пример таблицы, в которой я отслеживаю отгрузку нескольких заказов:
Моя таблица с отгруженными заказами.

Я хочу изменить формат даты в столбце B. Вот моя формула ЗАПРОСА:

=ЗАПРОС(A1:C7,»выберите * формат B ‘д-ммм-гг (ддд)'»)

  • сначала я указываю диапазон всей моей таблицы — А1:С7
  • затем я прошу формулу вернуть все столбцы – Выбрать *
  • и в то же время переформатировать столбец B так, как я вставил в формулу — формат B ‘д-ммм-гг (ддд)’

Формула работает как шарм. Он возвращает всю мою таблицу и меняет формат даты в столбце B:
Измените формат даты в Google Таблицах с помощью функции ЗАПРОС.

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

Код Описание Пример d День без начального нуля для 1-9 7 dd День с начальным нулем для 1-9 07 ddd День как аббревиатура Wed dddd День как полное название Среда m
(если не предшествует или не следует за
часы или секунды) Месяц без ведущего нуля 8 мм
(если не предшествует или не следует за
часы или секунды) Месяц с начальным нулем 08 ммм Аббревиатура месяца авг мммм Полное название месяца август ммммм Первая буква месяца А у
или же
yy Год из двух цифр 19 yyy
или же
гггг Полный числовой год 2019

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

Используя эти коды, вы можете форматировать даты разными способами:

  • Получите только год, месяц или день:

    =ЗАПРОС(A1:C7,»выберите * формат B ‘гггг'»)

  • Возвращает день, месяц и день недели:

    =ЗАПРОС(A1:C7,»выберите * формат B ‘дд мммм, дддд'»)

Кстати, к какому формату даты вы привыкли? 🙂

Google Sheets: преобразовать дату в число

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

Преобразовать дату в число, изменив формат

  1. Выберите те ячейки с датами, которые вы хотите преобразовать в числа.
  2. Перейти к Формат > Число и на этот раз выбрать Число среди прочих вариантов.
  3. Вуаля — все выбранные даты превратились в цифры, которые их обозначают:
    Преобразование формата даты в числовой формат.

Функция DATEVALUE для Google Sheets

Другой способ для Google Sheets преобразовать дату в число — использовать функцию DATEVALUE:

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

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

Например, я хочу преобразовать 17 августа 2019 г. к номеру. Все приведенные ниже формулы вернут один и тот же результат: 43694.

=ДАТАЗНАЧ(«17 августа 2019 г.»)
=ДАТАЗНАЧ(«2019-8-17»)
=ДАТАЗНАЧ(«17.08.2019»)

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

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

=ДАТАЗНАЧ(A2)

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

Преобразование дат в текст в электронных таблицах — это задача для функции ТЕКСТ:

=ТЕКСТ(число,формат)

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

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

Формула реальных данных может выглядеть так:

=ТЕКСТ(«17.08.2019″,»ГГГГ-ММ-ДД»)

Вот как я преобразовал свою дату — 17.08.2019 — в текст и изменил формат одновременно:
Google Таблицы: преобразуйте даты в текст и измените формат.

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

Вас также могут заинтересовать

Похожие записи

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *