Функции Google Таблиц для работы с датой и временем
В Google Таблицах есть несколько функций для работы с датой и временем – есть очень полезные, есть менее очевидные. Рассмотрим их.
На скриншоте во втором столбце результат действия формулы, а в третьем – текст самой формулы.
Далее я расскажу про каждую функцию отдельно. Отмечу, что в качестве даты можно использовать ссылку на ячейку с таковой или указывать дату напрямую в формуле в формате «01.02.2015».
Функция TODAY (СЕГОДНЯ)
возвращает сегодняшнюю дату. Аргументов у нее нет – сегодняшний день и есть сегодняшний. Результат вычисления функции обновляется вместе с остальными ячейками при любом изменении в документе.
Функция NOW (ТДАТА)
возвращает текущие время и дату. Аргументов тоже нет. Если ее не отформатировать – в ячейке будет отображаться и то, и другое:
А если в качестве формата использовать временной, в ячейке будет только текущее время:
Сверху неотформатированная ячейка, снизу – ячейка с временным форматом.
Функция NETWORKDAYS (ЧИСТРАБДНИ)
возвращает количество рабочих дней между двумя датами. Даты могут храниться в ячейках, как в этом примере:
Между первым октября 2015 года и 18 февраля 2016 года – 101 рабочий день.
Кроме того, даты можно задать и в самой формуле, хотя это и менее гибкий способ – ведь в ячейках их менять удобнее:
Можно задать в качестве второго аргумента функцию TODAY – и каждый день у вас будет отображаться актуальное количество рабочих дней, прошедших с определенной даты (аналогично можно задать TODAY и как первый аргумент – и отслеживать количество рабочих дней ДО определенной даты):
Последний аргумент этой функции – праздники, то есть даты, которые рабочими не считаются.
Удобнее всего их задать в диапазоне, а в функции сослаться на этот диапазон. Диапазон может выглядеть так:
А формула так:
Рабочих дней стало 95.
Функция NETWORKDAYS.INTL (ЧИСТРАБДНИ.INTL)
отличается от
предыдущей возможностью задать нестандартную рабочую неделю. Это третий аргумент, и задается он в виде «0000011», где нули – это рабочие дни, а единицы – выходные. Допустим, для шведской четырехдневной недели формула будет выглядеть так:
И рабочих дней стало 77.
Функция WORKDAY (РАБДЕНЬ)
возвращает дату, которая наступит по прошествии определенного количества дней (второй аргумент) с определенной даты (первый аргумент):
В данном примере мы ссылаемся на ячейку B7, в которой стоит дата 01.10.2015. По прошествии 155 рабочих дней с этой даты наступит пятое мая.
Функции DAY (ДЕНЬ), MONTH (МЕСЯЦ), YEAR (ГОД)
возвращают соответствующий параметр из даты, которая является единственным аргументом этой функции:
В примере аргумент – сегодняшняя дата, заданная функцией TODAY.
Функция WEEKNUM (НОМНЕДЕЛИ)
возвращает номер недели. Первый аргумент – дата, а второй – необязательный – тип. По умолчанию тип равен 1, и это означает, что первый день недели – воскресенье, если задать аргумент тип равным 2, то первым днем недели будет считаться понедельник.
Функция DATEDIF (РАЗНДАТ)
вычисляет количество дней, месяцев и лет между двумя датами.
Первые два аргумента – стартовая и конечная даты. А третий – параметр, у него есть следующие варианты:
D – полные дни;
M – полные месяцы;
Y – полные годы. В примере находим разницу между 1 октября 2015 и 18 февраля 2016:
Видимо, что полного года еще не прошло – но можно вычислить десятичную долю года, воспользовавшись функцией YEARFRAC (ДОЛЯГОДА)
.
У DATEDIF есть еще три варианта последнего аргумента:
MD – количество дней после вычитания полных месяцев ( в примере 17 дней между 01.01.2013 и 18.02.2016);
YM – количество месяцев после вычитания полных лет ( в примере 1 месяц между 01.01.2013 и 18.02.2016);
YD – количество дней
после вычитания полных лет ( в примере 48 дней между 01.01.2013 и 18.02.2016).
Функция EOMONTH (КОНМЕСЯЦА)
возвращает последнюю дату месяца, следующего от заданной даты через определенное количество месяцев. Например:
=EOMONTH(today();2)
Вернет 30.04.2016 в феврале (апрель = два месяца от февраля):
Если прибавить ко всей формуле единицу – то получим первый день следующего месяца, что тоже может пригодиться:
Функция WEEKDAY (НОМНЕДЕЛИ)
возвращает порядковый номер дня недели указанной даты (первый аргумент). Второй аргумент – тип, как у функции WEEKNUM. Для недели, начинающейся с понедельника, тип = 2:
Если же аргумент тип = 1, то четверг становится пятым днем недели, как видно на скриншоте:
Наконец, несколько функций для работы со временем. TIME (ВРЕМЯ)
преобразует заданные часы, минуты и секунды (это и есть ее аргументы) во время. Аргументы могут быть заданы, конечно, и внутри функции, и в виде ссылок на ячейки.
Ну а SECOND (СЕКУНДЫ), MINUTE (МИНУТЫ) и HOUR (ЧАС)
возвращают одно соответствующее значение из даты. На скриншоте выше видно, что заснял я его в 22 часа, 22 минуты и 27 секунд – ведь во всех трех функциях в качестве аргумента задано текущее время – NOW().
Другие статьи о функциях Google Таблиц
Текстовые функции Google Таблиц
Как выделить уникальные элементы из списка?
Вставляем в ячейки Google Таблиц изображения
Переводим текст прямо в Google Таблицах
Функция QUERY в Google Таблицах
А как бы определить сколько часов между датами?
(только именно решить функциями, а не форматом ячейки)
Алексей, вычесть одно из другого и умножить результат на 24.
Добрый день, нужно поместить в определенную ячейку дату заполнения другой ячейки, хотел это реализовать через функцию сегодня() , но она пересчитываемая, не подскажите как это можно реализовать другим способом? Или может отключить пересчитывает функций?
Добрый день. Это можно реализовать только через скрипт.
Здравствуйте.
А Вы можете написать такой скрипт?
Анастасия, мы писали о похожем скрипте здесь: https://t.me/google_sheets/171
Если будут затруднения, напишите мне на renat@shagabutdinov.ru
Премного благодарен! Давно искал нормальный вариант, без мусора и макросов.
Здравствуйте! Нужен совет!
Есть таблица, в которой одному из столбцов присвоен формат "ДД:ММ:ГГГГ ЧЧ:ММ». Таблицей пользуются несколько разных сотрудников организации с разными (к сожалению) понятиями о корректности заполнения ячеек. Кто-то проставляет дату и время как положено, а кто-то пишет некий бред вида «25.09.2018 *куча пробелов для полного счастья* 10-34» вместо «25.09.2018 10:34».
Задача:
1. Автоматически выделять красным цветом ячейку, в которую сотрудник внёс дату и время в кривом виде;
либо
2. Выдавать ошибку заполнения до тех пор, пока не будут внесены корректные данные.
Как?
а есть ли функция, с помощью которой можно сделать так: есть список дат на листе, каждая дата записана в отдельной ячейке столбца "А", сверху вниз. Далее в столбце "Б" идет какое либо уникальное значение, и в последующих столбцах например тоже. Хочу сделать, чтобы все данные из строки в которой написана именно сегодняшняя дата дублировались на другой лист. Сделать это нужно для того чтобы показатели одного дня от разных менеджеров в разных листах - собирались в отдельном листе пофамильно, чтобы контролировать и наглядно сравнивать.
Как поменять год с числом местами во всех датах?
Есть: 15.02.2019
Нужно: 2019.02.15
Добрый день. Подскажите, а как можно в Google Таблицах задать проставление текущей даты в одной ячейке, при выполнении условия в другой ячейке так, что бы эта дата не менялась?
Скажем в ячейке А1 проставляется значение "ОК", а в ячейку В2 проставляется дата текущая. Так, что бы на следующий день дата не изменилась
Вставляю в ячейку:=СЕГОДНЯ() Если файл открываю утром - дата стоит вчерашняя, после обеда - все правильно, сегодняшняя. Как быть? Нужна коррекция по часовому поясу? или что-то еще? Место нахождения - Сибирь, Томск.
Владислав, нужно сделать настройку таблицы. Файл/Настройка таблицы/Часовой пояс . В вашем случае (GST+07:00) Moskow+04- Krasnoyarsk
А как это сделать в таблицах google под андроидом?
Как подставить в формулу
=SUMIFS('Магазин 2'!O:O;'Магазин 2'!$C:$C;">=01.01.19";'Магазин 2'!$C:$C;"=01.01.19" на формулу первого дня месяца
"<=31.01.19" на формулу последнего дня месяца
которые ссылаются просто на номер месяца или на его название.
как определить номер недели в месяце? например 01.03.2020 1
02.03.20-08.03.20 2
Добрый день.
Как вставить в ячейку с текстом формулу =TODAY(), чтобы эта формула работала?
Например:
Сегодня 22.10.20
То есть, Сегодня - это текст, а дальше через пробел идет формула, показывающая текущую дату.
Максим, приветствую!
="Сегодня "&TODAY()
То есть текст в кавычках, а с функциями соединяется амперсандом &
Так не работает, дата отображается не датой, а просто числом
Так у меня не работает.
Работает так: ="Сегодня "&TO_TEXT(today())
Подскажите пожалуйста, как подсчитать количество отработанных часов при ненормированном рабочем дне. Есть данные сканера прихода/ухода работника: "На работу: 8:32; Домой: 17:35". И так на каждый день. Как к концу месяца посчитать рабочие часы в формате "Отработано: 167,45 часов". Спасибо.
а как отсортировать строки в таблице по дате добавления значений в строки?
Здравствуйте. При запуске формулы =DATEDIF(M6;N6;"D") по датам с 25.05.2015 по 23.06.2015 некорректно считаются календарные дни. При расчете согласно календаря получается 30 дней, а формула считает 29. В чем может быть проблема?
Есть ли формула или скрипт для записи данных ячейки в определенную дату в другую ячейку. Поясню: есть финансовые данные, которые постоянно меняются. Нужно знать сумму например на последний день месяца и чтобы эта сумма записалась в определенную ячейку. Через месяц тоже самое в другую. Это нужно для построения графика доходности например.
доброго времени суток. Как сделать, что бы прописывались только выходные дни? Используется для графика работ в выходные дни.