Как поменять формат даты в гугл таблицах
Перейти к содержимому

Как поменять формат даты в гугл таблицах

  • автор:

Как сменить формат даты при запросе QUERY в Google Sheet?

Задача вывести столбец с названием месяца в гугл таблицах. Простое форматирование не подходит, потому что строки добавляются через гугл форму. При этом установленный формат слетает/перезаписывается. По этой же причине нельзя сразу формулы прописать в ячейках. Нашел решение вывода даты через запрос QUERY
=QUERY(A:J;»select B»;1) , где колонка B это дата формата «10.03.2021» и заголовок в первой строке
В итоге необходимо получить дату формата «MMM», т.е. «мар.»
Знаю что в запросе можно пользоваться функцией format, но по имеющимся примерам и описаниям так и не понял как ей пользоваться.

  • Вопрос задан более трёх лет назад
  • 425 просмотров

1 комментарий

Простой 1 комментарий

Форматы чисел и дат в Google Таблицах

В этой статье рассмотрим виды форматов чисел и текста в ячейках Google Таблиц.

Главное, что нужно помнить о форматах — это то, что формат не равен содержанию ячейки. Или, иначе говоря, внешнее отображение данных не обязательно отражает эти данные, “карта не есть территория”.

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

Вы можете не видеть ничего, а в ячейке что-то есть.

Истинное значение всегда можно увидеть в строке формул:

У числа в ячейке C4 есть дробная часть (знаки после запятой), но в самой ячейке ее не видно — потому что у ячейки формат с округлением, отображается только целая часть.

Числовые форматы

Автоматический

Формат хорош тем, что у чисел с дробной частью она отображается, а у чисел без нее — нет, то есть нет “пустых” нулей после запятой вида 1000,00.

Это формат по умолчанию.

Разделителей разрядов нет.

Число

В этом формате появляются разделители разрядов. И знаки после запятой. Их можно убрать (или добавить) вот этими кнопками на панели инструментов:

Процентный

Подходит для отображения долей и отклонений. 1 единица — это 100%. Не нужно в формулах умножать на 100% результат, лучше просто использовать процентный формат.

Знаки после запятой можно (и часто нужно) убирать. Точность до сотых процента редко бывает нужна.

Экспоненциальный

Экспоненциальный формат подходит для отображения очень больших и очень малых чисел. Как его читать?

E+25 означает 25 порядков, или степеней десятки, или “нулей”, хотя строго говоря это могут быть не нули, а любые числа.

Например, число 12700000000000000000 в экспоненциальном формате — это 1,27E+19.

Миллион — это 1,00E+06 (единица и шесть нулей)

-1000000 (минус миллион) — это -1,00E+06

А 0,0000001 — это 1,00E-07

Знак минус после E означает разряды после запятой.

Финансы

Формат как числовой, но добавляется символ валюты с выравниванием влево. Вместо нуля — прочерк.

Финансы (второй формат)

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

Валюта

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

Валюта с округлением

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

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

Формат -> Числа -> Другие форматы -> Другие форматы чисел (валюты)

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

В случае с валютой можно будет выбрать любую:

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

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

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

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

00 — это обязательные знаки, #,## — это разделители разрядов, [Red] — цвет отображения данных. Например, если [Red] указан после точки с запятой, то отрицательные числа будут красными.

Форматы даты и времени

Давайте рассмотрим форматы даты и времени, примененные к одному и тому же значению — функции ТДАТА, у которой нет аргументов и которая выдает текущие время и дату.

Итак, во всех ячейках стоит одна и та же функция — ТДАТА. Возвращаются и дата, и время.

Автоматически при вводе этой функции используется формат “Дата и время”, его же можно применить принудительно.

Также есть возможность отображать только дату или только время. Хотя в ячейке фактически есть и то и другое.

Обратите внимание на числовой формат. 43 159, 78 — это 28 февраля 2018 года, 18:40. Дело в том, что для Таблиц (как и Excel, кстати) дата является целым числом. Именно это позволяет их складывать и вычитать, сравнивать. Если вы поменяете у любого числа формат на “Дату”, в ячейке будет дата, соответствующая этому числу. Одна единица — один день. 0 — это 30.12.1899, а, например, 100 — это день на 100 дней позже 30.12.1899, то есть 09.04.1900.

Есть в Таблицах и пользовательские форматы даты и времени. Для их создания нужно зайти в Формат -> Числа -> Другие форматы -> Другие форматы даты и времени

Там у вас будет возможность выбрать один из множества готовых форматов в списке:

Или же создать свой. Чтобы изменить формат представления одного из элементов (дня, месяца, года, часов, минут, секунд), нажмите на стрелки около этого элемента и выберите представление:

Чтобы добавить новый элемент, нажмите на небольшую кнопку в правом конце поля:

Между элементами можно добавлять слова и фразы, символы — все ограничено лишь вашей фантазией:

4 быстрых способа изменить формат даты в Google Sheets

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

Оглавление

Шаг 3: Нажмите на раскрывающееся меню для Locale.

Шаг 4: Выберите регион, который вы хотите использовать в формате.

Шаг 5: Наконец, нажмите «Сохранить и перезагрузить».

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

Но если вам нужны дополнительные элементы управления для изменения формата даты, взгляните на следующий раздел.

2. Вручную измените формат даты в Google Sheets

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

Шаг 1: Откройте соответствующий лист и щелкните параметр «Формат» на панели инструментов.

Шаг 2: Выберите «Число» > «Пользовательская дата и время».

Вы увидите текущий формат даты.

Шаг 3: Выберите любой формат даты из вариантов.

Шаг 4: Нажмите «Применить», чтобы сохранить изменения.

Однако, если вы хотите изменить отдельные элементы даты, вот как это сделать.

Связанный : Как пройти проверку в Twitter: подробное пошаговое руководство

Как изменить отдельные элементы даты

Шаг 1: Откройте соответствующий лист и щелкните параметр «Формат» на панели инструментов.

Шаг 2: Выберите «Число» > «Пользовательская дата и время».

Шаг 3: Теперь нажмите на элемент, который вы хотите изменить.

Шаг 4: Выберите формат элемента. Это изменит формат только для этого элемента.

Хотя мы показали, как вы можете изменить месяцы, вы также можете индивидуально изменить дни и год в дате, повторив описанные выше шаги.

Шаг 5: Нажмите «Применить», чтобы сохранить изменения.

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

Как изменить порядок дат вручную

Шаг 1: Откройте соответствующий лист и щелкните параметр «Формат» на панели инструментов.

Шаг 2: Выберите «Число» > «Пользовательская дата и время».

Шаг 3: Нажмите на каждый из элементов даты.

Шаг 4: Нажмите Удалить.

Повторите процесс для всех трех элементов, убедитесь, что поле даты полностью пусто, и удалите все символы, такие как «-» и «*», используя клавишу возврата.

Шаг 5: Теперь откройте раскрывающееся меню и выберите все элементы, которые вы хотите в этой пользовательской дате, и в том порядке, в котором вы хотите.

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

Шаг 6: Нажмите «Применить», чтобы сохранить изменения.

Однако, если вы хотите изменить формат даты только для нескольких ячеек электронной таблицы, ознакомьтесь со следующим разделом.

3. Автоматическое изменение формата даты в Google Sheets

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

Шаг 1: Откройте соответствующий лист в Google Таблицах.

Шаг 2: Введите диапазон дат в столбце Google Таблиц.

Теперь мы хотим преобразовать даты в столбце A в формат, в котором месяц отображается в виде текста, а остальная часть остается прежней. Итак, мы можем использовать формат «ДД-ммм-ГГГГ».

Как мы предположили, что «ммм» означает месяц в текстовом формате? Существует руководство по токену даты, написанное Google. Вы можете проверить это, чтобы понять, как ввести формат даты.

Нам нужны эти преобразованные даты в столбце B. Следовательно, нам нужно ввести следующую формулу в ячейку столбца B, которая соответствует дате в столбце A.

Шаг 2: Введите формулу и нажмите клавишу ввода. Наши даты начинаются в ячейке A2 в столбце A, и мы хотим ввести эту формулу в соответствующую ячейку — B2 в столбце B.

=arrayformula(if((A2:A)<>"",text(A2:A,"DD mmmm YYYY"),""))

Примечание: Измените формулу соответствующим образом, то есть замените «A2» и «A» на «клетка’ в котором начинаются даты и ‘столбец‘ исходных дат соответственно.

Шаг 3: Теперь вы можете видеть, что все даты преобразованы в другой формат.

Когда вы вводите новую дату в новую ячейку в столбце A, она автоматически преобразуется в формат «ДД-ммм-ГГГГ» в столбце B.

Далее давайте посмотрим, как преобразовать даты, представленные в текстовом формате, в распознаваемый серийный номер с помощью функции ДАТАЗНАЧ.

Связанный : Как включить и использовать рукописный текст в Gmail, Google Docs

4. Используйте DATEVALUE для преобразования дат в соответствующее числовое значение.

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

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

=DATEVALUE(date_string)

В нашем случае дата в тексте присутствует в ячейке A1, и мы хотим преобразовать значение даты в ячейку B1. Нам нужно ввести формулу «=DATEVALUE(A1)» в ячейку B1. Вы также можете напрямую ввести строку даты в формулу.

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

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

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

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

Как видите, мы только что ввели «11-1», что может быть чем угодно. Это может быть счет игры или указание периода с 11:00 до 13:00 в ежедневном расписании. И, как вы можете видеть, когда я выбираю ячейку «11-1», Google Sheets показывает ее в формате даты.

Шаг 1: Выберите и выделите все ячейки, которые ошибочно считаются датой.

Шаг 2: Нажмите «Формат» > «Число» и выберите «Обычный текст».

Любые данные во всех выбранных ячейках не будут автоматически преобразованы в даты.

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

Часто задаваемые вопросы об изменении формата даты в Google Sheets

1. Если я изменю формат даты для одной таблицы в Google Таблицах, изменится ли формат даты для всех будущих таблиц?

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

2. Как добавить сегодняшнюю дату в Google Sheets?

Вы можете ввести функцию СЕГОДНЯ() в ячейку, и она вернет текущую дату. Вы также можете ознакомиться с этим руководством, чтобы узнать, как автоматически добавлять даты в Google Sheets.

Конвертируйте даты с легкостью

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

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

Текст вместо даты в Гугл Таблицах или смещение по годам

Текст вместо даты в Гугл Таблицах или смещение по годам

Наконец-то разобрал проблему, когда отображается формат текст вместо даты в Гугл Таблицах. Или та же ошибка, когда протягиваешь ячейку с датой, а вместо прибавления дня добавляется единичка к году в Google Spreadsheets. Ранее не доходили руки и вот проблема стала актуальной настолько, что пришлось гуглить.

На скрине ниже привёл пример, когда у нас есть столбец с форматом «Дата» и мы протягиваем ячейку вниз, получаем смещение не на один день → 06.06.2021, а на один год → 05.06.2022, чего собственно быть не должно.

ошибка при протягивании даты в Гугл Таблицах, когда добавляется год вместо числа

Решение проблемы с форматом даты Гугл Таблиц

Проблема до банальности проста. Дело в том, что Гугл просто не воспринимает введённую дату, как формат «Даты», потому что по умолчанию в «региональные настройки» стоит США.

Соответственно, чтобы Гугл Таблицы стали воспринимать ячейку и данные в ней как даты, нужно сначала выбрать Россию в «региональных настройках», т.к. в США формат даты начинается с года, а не дня.

Потом встать на столбец и присвоить ему формат «Даты».

Как вы видите, формат столбца поменялся и выравнивание стало по правой стороне — так Гугл Таблицы форматируют Даты.

Протянем дату вниз и проверим, что прибавляется один день, а не год.

Смещение даты в Гугл Таблицах на один день

Всё получилось и теперь при протягивании ячейки с датой в Гугл Таблице смещение будет идти по дням, а не годам.

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

  1. Поиск значения по столбцу и вывод даты первого совпадения
  2. Выделение строки цветом по значению в ячейке в гугл таблицах и excel
  3. Как вывести двойные кавычки в Гугл Таблицах
  4. Функция Query и сумма SUM по значению в Гугл таблицах

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

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