Как изменить формат даты в 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 Таблицах

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

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

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

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

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

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

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

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

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

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

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

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

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

   Вот что я могу выбрать для День:
   

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

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

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

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

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

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

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

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

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

Формула работает как шарм. Он возвращает всю мою таблицу и меняет формат даты в столбце B:

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

Код Описание Пример 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 Таблицах и преобразовать даты в числа или текст. Не стесняйтесь делиться другими интересными способами в разделе комментариев ниже. 😉

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