Как рассчитать дни между двумя датами в Google Таблицах

Содержание:

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

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

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

Давайте начнем!

Расчет общего количества дней между двумя датами

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

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

Приведенная ниже формула даст вам общее количество дней между этими двумя датами.

= B2-B1

Приведенная выше формула говорит нам, что между датой начала и окончания проекта есть 274 дня.

Легко, правда!

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

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

= B2-B1 + 1

Расчет рабочих дней между двумя датами

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

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

В конце концов, если это проект, вы не можете ожидать, что ваша команда будет работать по выходным … верно!

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

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

Приведенная ниже формула даст вам количество рабочих дней между этими двумя датами:

= ЧИСТРАБДНИ (B1; B2)

Функция ЧИСТРАБДНИ (как следует из названия) дает вам сетевые дни между двумя датами (она принимает дату начала и дату окончания в качестве входных аргументов)

О функции ЧИСТРАБДНИ вам нужно знать две вещи:

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

Хотя это может сработать в большинстве случаев, есть два распространенных сценария, в которых вы можете захотеть настроить эту формулу:

Вам нужно исключить праздничные дни из рабочих дней
У тебя разные выходные. Например, вы можете выбрать только воскресенье, так как выходные, а суббота для вас — рабочий день. Или вы из стран, где выходные дни — пятница и суббота (а не суббота и воскресенье)

Хорошая новость в том, что Google Таблицы легко справятся со всем этим.

Расчет рабочих дней без учета государственных праздников

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

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

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

К счастью, функция ЧИСТРАБДНИ уже имеет эту встроенную функцию. Вы можете указать диапазон, в котором есть праздничные дни, и он будет автоматически игнорировать их при вычислении рабочих дней.

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

Приведенная ниже формула даст вам количество рабочих дней между датой начала и окончания, не считая праздников:

= ЧИСТРАБДНИ (B1; B2; E2: E12)

Эта формула принимает три аргумента:

Дата начала — B1 в этом примере
Дата окончания — B2 в этом примере
Диапазон с праздничными датами — E2: E12 в этом примере.

Обратите внимание, что функция ЧИСТРАБДНИ достаточно умна, чтобы самостоятельно настраивать праздничные и выходные дни. Например, в следующих праздничных датах с 1 апреля по 30 декабря восемь праздников.

Но функция ЧИСТРАБДНИ учитывает только шесть из них, а два в любом случае приходится на выходные дни (Пасха и День независимости). Поскольку формула ЧИСТРАБДНИ в любом случае не учитывает выходные дни при вычислении, она также будет следить за тем, чтобы дни не удалялись дважды (как в выходные, так и в праздничные дни).

Расчет рабочих дней, когда выходные не суббота и воскресенье

Хотя функция ЧИСТРАБДНИ автоматически предполагает, что выходные дни — это суббота и воскресенье, это может быть не так для всех. У некоторых из вас может быть только воскресенье, а некоторые могут работать в странах, где пятница и суббота нерабочие дни, а воскресенье — рабочее время.

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

NETWORKDAYS.INTL — где INTL означает международный.

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

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

Следующая формула даст вам результат:

= ЧИСТРАБДНИ.INTL (B1; B2,7; E2: E12)

Приведенная выше формула вернет 191, то есть количество дней между датами начала и окончания, с пятницей и субботой в качестве выходных.

Эта формула принимает четыре аргумента:

Дата начала — B1 в этом примере
Дата окончания — B2 в этом примере
Выходные — 7 в этом примере. Здесь вы указываете выходные дни, которые должна учитывать эта формула. Почему 7, посмотрите таблицу ниже.
Диапазон с праздничными датами — E2: E12 в этом примере.

Почему я использовал 7, чтобы сделать пятницу и субботу выходными?

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

Поэтому, когда я использую 7, он знает, что я хочу, чтобы выходные были пятницей и субботой.

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

Аргумент	Выходные дни
1	Суббота / воскресенье — выходные дни
2	Воскресенье / понедельник — выходные дни
3	Понедельник / вторник — выходные дни
4	Вторник / среда — выходные дни
5	Среда / четверг — выходные дни
6	Четверг / пятница — выходные дни
7	Пятница / суббота — выходные дни
11	Воскресенье — единственный выходной день
12	Понедельник — единственный выходной день
13	Вторник — единственный выходной день
14	Среда — единственный выходной день
15	Четверг — единственный выходной день
16	Пятница — единственный выходной день
17	Суббота — единственный выходной день

Основываясь на приведенной выше таблице, если вы хотите, чтобы выходными считалось только воскресенье (при шестидневной рабочей неделе), вам нужно использовать 11 в качестве третьего аргумента в функции NETWORKDAYS.INTL.

Расчет рабочих дней с непоследовательными выходными днями

Предположим, вы работаете на работе только в определенные дни недели (скажем, понедельник, вторник и четверг).

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

К счастью, когда формулировалась функция NETWORKDAYS.INTL, это также было учтено.

Когда вы используете функцию NETWORKDAYS.INTL для чего-то вроде этого, вам необходимо четко указать, какие дни рабочие, а какие нет.

И вы делаете это, используя серию из семи последовательных чисел (где каждое число равно 1 или 0). Эти числа представляют семь дней в неделе. Первое число в ряду представляет понедельник, второе — вторник и так далее.

«0» означает рабочий день, а «1» — нерабочий день. Таким образом, 0000011 будет означать, что первые пять дней (с понедельника по пятницу) рабочие, а суббота и воскресенье — выходные (или нерабочие дни).

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

Итак, в нашем примере, где понедельник, вторник и четверг — рабочие дни, код будет 0010111.

И вы можете использовать этот код в функции NETWORKDAYS.INTL, как показано ниже:

= ЧИСТРАБДНИ.INTL (B1; B2; «0010111»; E2: E12)

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

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

Рассчитать все понедельники между двумя датами

Иногда может потребоваться вычисление не рабочих дней, а числа одного конкретного дня недели.

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

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

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

Чтобы вычислить количество понедельников, я буду использовать функцию ЧИСТРАБДНИ.INTL со строкой из семи последовательных чисел, которые представляют дни недели.

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

Таким образом, строка чисел будет «0111111», где 0 представляет рабочий день понедельника.

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

= ЧИСТРАБДНИ.INTL (B1; B2; «0111111»; E2: E12)

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

Надеюсь, вы нашли этот урок полезным.

Вас могут заинтересовать следующие статьи: