ДАТА И ВРЕМЯ
На скриншоте ниже представлены основные функции для работы с датой и временем. Во втором столбце указан результат вычисления формулы, а в третьем — текст самой формулы:
Расскажу про каждую функцию отдельно. Отмечу, что в качестве даты можно использовать ссылку на ячейку или указывать дату напрямую в формуле в формате дд.мм.гггг.
Функция TODAY (СЕГОДНЯ) возвращает сегодняшнюю дату. Аргументов у нее нет — сегодняшний день и есть сегодняшний. Результат вычисления функции обновляется вместе с остальными ячейками при любом изменении в документе.
Следующая функция вернет количество дней, прошедших с определенной даты:
=TODAY()-дата
=СЕГОДНЯ()-дата
Функция NOW (ТДАТА) возвращает текущие время и дату. Аргументов тоже нет. Если ее не отформатировать — в ячейке будет отображаться и то, и другое:
А если в качестве формата использовать временной, в ячейке будет только текущее время:
Сверху неотформатированная ячейка, снизу — ячейка с временным форматом.
Функция NETWORKDAYS (ЧИСТРАБДНИ) возвращает количество рабочих дней между двумя датами. Даты могут храниться в ячейках, как в этом примере:
Между 1 октября 2015 года и 18 февраля 2016 года — 101 рабочий день.
Кроме того, даты можно задать и в самой формуле, хотя это и менее гибкий способ — ведь в ячейках их менять удобнее:
Можно задать в качестве второго аргумента функцию TODAY — каждый день у вас будет отображаться актуальное количество рабочих дней, прошедших с определенной даты (аналогично можно задать TODAY как первый аргумент и отслеживать количество рабочих дней ДО определенной даты):
Последний аргумент этой функции — праздники, то есть дни, которые не считаются рабочими.
Удобнее всего задать их в диапазоне, а в функции — сослаться на него. Диапазон может выглядеть так:
А формула так:
Рабочих дней стало 95.
Функция NETWORKDAYS.INTL (ЧИСТРАБДНИ.INTL) отличается от предыдущей возможностью задать нестандартную рабочую неделю, указав третий аргумент, который задается в виде 0000011, где нули — это рабочие дни, а единицы — выходные. Например, для шведской четырехдневной недели формула будет выглядеть так:
И рабочих дней стало 77.
Функция WORKDAY (РАБДЕНЬ) возвращает дату, которая наступит по прошествии определенного количества дней (второй аргумент) с определенной даты (первый аргумент):
В данном примере мы ссылаемся на ячейку, в которой стоит дата 01.10.2015. По прошествии 155 рабочих дней с этой даты наступит пятое мая.
Функции DAY (ДЕНЬ), MONTH (МЕСЯЦ), YEAR (ГОД) возвращают соответствующие параметры из даты, которая является единственным аргументом этой функции:
В примере аргумент — сегодняшняя дата, заданная функцией TODAY.
Функция WEEKNUM (НОМНЕДЕЛИ) возвращает номер недели. Первый аргумент — дата, а второй — необязательный — тип. По умолчанию тип равен 1, где первый день недели — воскресенье. Если задать тип 2, то первым днем недели будет считаться понедельник.
Функция DATEDIF (РАЗНДАТ) вычисляет количество дней, месяцев и лет между двумя датами.
В примере мы находим разницу между 1 октября 2015 года и 18 февраля 2016 года:
Первые два аргумента — стартовая и конечная даты, а третий — параметр вычислений: D — полные дни; M — полные месяцы; Y — полные годы.
Кроме того, мы можем вычислять десятичную долю года, воспользовавшись функцией 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)
=КОНМЕСЯЦА(СЕГОДНЯ();2)
Вернет 30.04.2016 в феврале:
Прибавив ко всей формуле единицу, мы получим первый день следующего месяца, что тоже может пригодиться:
Функция WEEKDAY (НОМНЕДЕЛИ) возвращает порядковый номер дня недели указанной даты (первый аргумент). Второй аргумент — тип, как у функции WEEKNUM. Для недели, начинающейся с понедельника, тип равен 2:
Если же аргумент тип 1, то четверг становится пятым днем недели, как видно на скриншоте:
Наконец, несколько функций для работы со временем. TIME (ВРЕМЯ) преобразует заданные часы, минуты и секунды (это и есть ее аргументы) во время. Аргументы могут быть заданы и внутри функции, и в виде ссылок на ячейки.
Ну а SECOND (СЕКУНДЫ), MINUTE (МИНУТЫ) и HOUR (ЧАС) возвращают одно соответствующее значение из времени. На скриншоте выше видно, что я заснял его в 22 часа, 22 минуты и 27 секунд, ведь во всех трех функциях в качестве аргумента задано текущее время — NOW().
ВЫЧИСЛЯЕМ НОМЕР КВАРТАЛА
В Google Таблицах нет встроенной функции для вычисления номера квартала, но ее несложно сделать самостоятельно. Алгоритм следующий:
1) прибавляем к номеру месяца 2 (для июня получится число 8);
2) делим результат на 3 (получится 2,66);
3) округляем результат в меньшую сторону (получится 2).
Теперь эти же шаги опишем с помощью функций (в примере предполагается, что дата в ячейке A1):
1.-Сначала нужно вычислить месяц по дате — функция MONTH:
=MONTH(A1)
=МЕСЯЦ(A1)
2.-Добавляем к месяцу 2 и делим полученную сумму на 3:
=(MONTH(A1)+2)/3
=(МЕСЯЦ(A1)+2)/3
3.-Все это округляем с помощью функции INT:
=INT((MONTH(A1)+2)/3)
=ЦЕЛОЕ((МЕСЯЦ(A1)+2)/3)
Если месяц уже рассчитывается в отдельных ячейках, то вместо (MONTH(A1)) можно поставить ссылки на них:
КЕЙС «ПЛАН НА МЕСЯЦ: СКОЛЬКО ОСТАЛОСЬ ВЫПОЛНИТЬ (NETWORKDAYS)»
У менеджера по продажам есть план на период (допустим, квартал). И в каждый конкретный день ему нужно видеть, сколько он должен был заработать к этому дню (если исходить из предположения, что мы распределяем сумму равномерно между днями).
Для этого понадобится функция NETWORKDAYS (ЧИСТРАБДНИ), возвращающая количество рабочих дней между двумя указанными датами:
Как вы знаете, у нее есть третий необязательный аргумент — праздничные дни.
Если у вас в компании есть какие-то нестандартные выходные (например, 14 февраля), можно указать эти даты в отдельных ячейках и сослаться на них в третьем аргументе NETWORKDAYS:
Теперь вернемся к задаче: рассчитать, сколько на сегодняшний день нужно было продать (выполнить), исходя из плана. Алгоритм следующий:
- Определить, сколько рабочих дней прошло с начала периода (рассчитать разницу с помощью NETWORKDAYS и функции TODAY, возвращающей сегодняшнюю дату (в примере — начиная с 1 января 2017 года)):
NETWORKDAYS(DATE(2017;1;1);TODAY())
ЧИСТРАБДНИ(ДАТА(2017;1;1);СЕГОДНЯ())
- Определить, сколько в квартале (рабочем периоде) рабочих дней, с помощью NETWORKDAYS:
NETWORKDAYS(DATE(2017;1;1);DATE(2017;3;31))
ЧИСТРАБДНИ(ДАТА(2017;1;1);ДАТА(2017;3;31))
- Разделить план (допустим, он в ячейке A2) на количество дней в периоде — так мы рассчитаем, сколько нужно выполнять за каждый рабочий день:
=A2/NETWORKDAYS(DATE(2017;A1;1);DATE(2017;31;1))
=A2/ЧИСТРАБДНИ(ДАТА(2017;A1;1);ДАТА(2017;31;1))
- Все это умножить на количество прошедших с начала периода дней ():
=A2/NETWORKDAYS(DATE(2017;A1;1);DATE(2017;31;1))*NETWORKDAYS(DATE(2017;A1;1);TODAY())
=A2/ЧИСТРАБДНИ(ДАТА(2017;A1;1);ДАТА(2017;31;1))*ЧИСТРАБДНИ(ДАТА(2017;A1;1);СЕГОДНЯ())
Итак, формула возвращает только ту часть плана, которую мы должны были выполнить на сегодняшний день, исходя из предпосылки, что план распределяется равномерно и только на рабочие дни.
КЕЙС С РАСЧЕТОМ ЗАРПЛАТЫ ЗА НЕПОЛНЫЙ МЕСЯЦ
В моей трудовой практике был такой случай. Я устроился работать в новую компанию в январе, спустя примерно неделю после новогодних каникул. Отработав месяц до конца, я получил зарплату на свою карточку. К сожалению, сумма была меньше той, на которую я рассчитывал.
Оказалось, что при расчете использовали отношение календарных дней, прошедших от фактической даты трудоустройства, к общему количеству дней в месяце. А не отношение отработанных дней к количеству рабочих дней в месяце.
На примере моего случая создадим модель и рассмотрим оба варианта расчета. Будем использовать формулу ЧИСТРАБДНИ (NETWORKDAYS) и формулы для работы с датой.
Ниже наши вводные:
Интересен тут, пожалуй, расчет последней даты месяца с использованием формулы КОНМЕСЯЦ (EOMONTH). В формуле два аргумента, первый — любая дата, второй — последнюю дату какого месяца нужно вернуть. Если использовать 0, то формула вернет тот же месяц, что и в первом аргументе, если -1 — предыдущий месяц, +1 — следующий и т. д.:
Формулу ЧИСТРАБДНИ разбирать не будем — она разобрана в предыдущем кейсе; напомню только, что она считает количество чистых рабочих дней, без выходных и праздников. И ее аргументы — две даты, между которыми считаем рабочие дни и праздники, которые следует исключить.
Вернемся к моей зарплате. Мне казалось, что мы должны взять количество дней, которые я отработал (ячейка С7 в вводных данных), и разделить его на все рабочие дни месяца (ячейка С5 в вводных данных). Получается 15 / 17 = 82%, то есть на этот процент зарплаты я мог рассчитывать.
Однако бухгалтерия применила другой метод расчета: она взяла отношение количества дней, которое я был фактически оформлен, к общему количеству календарных дней в месяце. Получилось (31.01.13-14.01.13 + + 1) / (31.01.13-01.01.13 + 1) = 18 / 31 = 58%.
Итак, мы разобрали два варианта расчета. Не столь важно, какой применит ваше начальство, — важнее, чтобы вы всегда могли (и умели) его проверить.
