Как изменить формат даты в 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.
Для обеспечения правильных вычислений очень важно правильно установить языковой стандарт, особенно если файл был создан в другой стране:
- Перейти к Файл > Настройки электронной таблицы в меню Google Таблиц.
- Находить Регион под Общий вкладку и выберите нужное место из выпадающего списка:
Кончик. В качестве бонуса вы также можете указать здесь свой часовой пояс, чтобы записать в него историю файлов.
Примечание. Язык не влияет на язык ваших Таблиц. Однако форматирование даты будет применено ко всей электронной таблице. Все, кто работает с ним, увидят изменения, независимо от их места на земном шаре.
Как изменить формат даты в Google Таблицах
Если даты в ваших таблицах отформатированы непоследовательно или вместо них вы видите только странные наборы чисел, не паникуйте. Вам просто нужно изменить формат даты в ваших таблицах Google с помощью встроенных инструментов.
Формат даты Google Sheets по умолчанию
- Выберите все ячейки, которые вы хотите отформатировать.
- Перейти к Формат > Число в меню электронной таблицы и выберите Свидание чтобы увидеть только дату или Дата время чтобы получить дату и время в ячейке:
Целые числа успешно преобразуются в формат, который вы узнаете с первого взгляда. Это форматы даты Google Таблиц по умолчанию:
Кончик. Вы можете найти те же форматы, если вы нажмете на 123 значок на панели инструментов электронной таблицы:
Пользовательские форматы даты
Если вам не нравится, как Google Таблицы форматируют даты по умолчанию, я не буду вас винить. К счастью, есть много возможностей для импровизации благодаря настраиваемым форматам даты.
Вы можете получить к ним доступ из того же меню Google Sheets: Формат > Число > Дополнительные форматы > Дополнительные форматы даты и времени:
Вы увидите окно с множеством доступных пользовательских форматов даты. Какой бы из них вы ни выбрали и ни применили, ваши даты будут выглядеть одинаково:
Если вас все еще не устраивает внешний вид ваших дат, вы можете настроить свой собственный формат даты:
- Выберите ячейки, которые вы хотите отформатировать.
- Перейти к Формат > Число > Дополнительные форматы > Дополнительные форматы даты и времени.
- Поместите курсор в поле вверху, содержащее единицы даты, и удалите все с помощью клавиш Backspace или Delete:
- Нажмите на стрелку справа от поля и выберите юнит, который вы хотите иметь в первую очередь. Не забудьте потом ввести разделитель.
Повторяйте, пока не будут добавлены все необходимые единицы (не беспокойтесь, вы сможете добавить или удалить их позже):
- Обратите внимание, что справа от каждой единицы есть двойные стрелки. Нажмите на них, и вы сможете настроить точный способ отображения значения.
Вот что я могу выбрать для День:
Таким образом, вы можете редактировать все значения, вставлять дополнительные и удалять устаревшие. Вы можете разделять единицы с помощью различных символов, включая запятые, косые черты и тире.
- Когда будете готовы, нажмите Применить.
Вот какой формат я создал и как теперь выглядят мои даты:
Функция 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: преобразовать дату в число
В случае, если вам нужно видеть цифры вместо дат, вам пригодится один из способов ниже.
Преобразовать дату в число, изменив формат
- Выберите те ячейки с датами, которые вы хотите преобразовать в числа.
- Перейти к Формат > Число и на этот раз выбрать Число среди прочих вариантов.
- Вуаля — все выбранные даты превратились в цифры, которые их обозначают:
Функция 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 Таблицах и преобразовать даты в числа или текст. Не стесняйтесь делиться другими интересными способами в разделе комментариев ниже. 😉
