Как посчитать количество рабочих дней между датами в excel

Как посчитать количество рабочих дней между датами в excel

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

Синтаксис

Аргументы функции ЧИСТРАБДНИ.МЕЖД описаны ниже.

Нач_дата и кон_дата Обязательный. Даты, разницу между которыми требуется вычислить. Начальная дата может предшествовать конечной дате, совпадать с ней или быть позже нее.

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

Номера обозначают следующие выходные дни:

Номер выходного дня

Выходные дни

Строковые значения дней недели включают семь знаков, каждый из которых обозначает день недели (начиная с понедельника). Значение 1 представляет нерабочие дни, а 0 — рабочие дни. В строке допустимо использовать только знаки 1 и 0. При значении 1111111 всегда возвращается 0.

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

Праздники Необязательный. Набор из одной или нескольких дат, которые необходимо исключить из календаря рабочих дней. Значение «праздники» должно быть диапазоном ячеек, содержащих даты, или константой массива, включающей порядковые значения, которые представляют даты. Порядок дат или значений может быть любым.

Замечания

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

Если start_date вне диапазона для текущего базового значения даты, NETWORKDAYS. InTL возвращает #NUM! значение ошибки #ЗНАЧ!.

Если end_date вне диапазона для текущего базового значения даты, NETWORKDAYS. InTL возвращает #NUM! значение ошибки #ЗНАЧ!.

Если строка выходных дней имеет недействительные длину или содержит недопустимые символы, NETWORKDAYS. InTL возвращает #VALUE! значение ошибки #ЗНАЧ!.

Пример

Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.

Получаем 22 предстоящих рабочих дня. Вычитает 9 нерабочитающих выходных дней (5 суббот и 4 воскресенья) из общего числа дней между двумя датами. По умолчанию суббота и воскресенье считаются нерабочитами.

Получаем -21, т. е. 21 прошедший рабочий день.

Из 32 дней между 1 января 2006 и 1 февраля 2006 года вычитаем 10 нерабочих дней (4 пятницы, 4 субботы и 2 праздника) и получаем 22 предстоящих рабочих дня. Используем аргумент 7 в качестве выходных дней, которыми являются субботы и воскресенья. Также на этой период времени приходится два праздника.

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

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

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

Пример расчета количества рабочих дней без выходных

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

Применяемая формула весьма проста. Функция ЧИСТРАБДНИ имеет два обязательных для заполнения аргумента:

1. Начальная дата.
2. Конечная дата.

Если начальная дата записана в ячейке A2, а конечная в B2, тогда функция возвращает количество только рабочих дней (без суббот и воскресенья) с учетом високосных годов, в которых всего 366 дня.

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

Для вычислений данного типа Excel предлагает функцию ЧИСТРАБДНИ.МЕЖД. Данная функция обладает дополнительным важным аргументом – «Выходные». Здесь следует указать один из кодов, который поддерживает данная функция (от 1 и до 15). Данный код определяет которые дни недели должны быть восприняты как выходные и не учитываться при вычислении формулы.

В процессе введения функции вручную в ячейку или в строку формул в момент заполнения третьего аргумента появится подсказка значения кодов 1-15. Как показано ниже на рисунке:

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

Вычисление разности дат в Microsoft Excel

Для выполнения определенных задач в Excel нужно определять, сколько дней прошло между некоторыми датами. К счастью, у программы имеются инструменты, которые способны решить данный вопрос. Давайте выясним, какими способами можно посчитать разность дат в Экселе.

Расчет количества дней

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

1. Выделяем пространство листа, на котором вы планируете производить вычисления. Кликаем правой кнопкой мыши по выделению. Активируется контекстное меню. В нём выбираем пункт «Формат ячейки…». Как вариант, можно набрать на клавиатуре сочетание клавиш Ctrl+1.
2. Открывается окно форматирования. Если открытие произошло не во вкладке «Число», то следует в неё перейти. В блоке параметров «Числовые форматы» выставляем переключатель в позицию «Дата». В правой части окна выбираем тот тип данных, с которым собираемся работать. После этого, чтобы закрепить изменения, жмем на кнопку «OK».

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

Способ 1: простое вычисление

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

1. Записываем в отдельные ячейки отформатированного диапазона даты, разность между которыми нужно вычислить.
2. Выделяем ячейку, в которой будет выводиться результат. В ней должен быть установлен общий формат. Последнее условие очень важно, так как, если в этой ячейке стоит формат даты, то в таком случае и результат будет иметь вид «дд.мм.гг» или другой, соответствующий данному формату, что является некорректным итогом расчетов. Текущий формат ячейки или диапазона можно просмотреть, выделив его во вкладке «Главная». В блоке инструментов «Число» находится поле, в котором отображается данный показатель.

Способ 2: функция РАЗНДАТ

Для вычисления разности в датах можно также применять специальную функцию РАЗНДАТ. Проблема в том, что в списке Мастера функций её нет, поэтому придется вводить формулу вручную. Её синтаксис выглядит следующим образом:

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

y	Полные года
m	Полные месяцы
d	Дни
YM	Разница в месяцах
MD	Разница в днях (месяцы и годы не учитываются)
YD	Разница в днях (годы не учитываются)

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

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

1. Записываем формулу в выбранную ячейку, согласно её синтаксису, описанному выше, и первичным данным в виде начальной и конечной даты.
2. Для того, чтобы произвести расчет, жмем кнопку Enter. После этого результат, в виде числа обозначающего количество дней между датами, будет выведен в указанную ячейку.

Способ 3: вычисление количеств рабочих дней

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

В этой функции основные аргументы, такие же, как и у оператора РАЗНДАТ – начальная и конечная дата. Кроме того, имеется необязательный аргумент «Праздники».

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

1. Выделяем ячейку, в которой будет находиться итог вычисления. Кликаем по кнопке «Вставить функцию».
2. Открывается Мастер функций. В категории «Полный алфавитный перечень» или «Дата и время» ищем элемент «ЧИСТРАБДНИ». Выделяем его и жмем на кнопку «OK».
3. Открывается окно аргументов функции. Вводим в соответствующие поля дату начала и конца периода, а также даты праздничных дней, если таковые имеются. Жмем на кнопку «OK».

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

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

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

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

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

Microsoft Excel имеет две основных функции, специально предназначенных для расчета будних дней – РАБДЕНЬ (WORKDAY) и ЧИСТРАБДНИ (NETWORKDAYS). Мы также остановимся на дополнительных РАБДНИ.МЕЖД и ЧИСТРАБДНИ.МЕЖД, которые могут быть очень полезными.

Функция РАБДЕНЬ определяет момент времени, который наступит через какое-то количество рабочих дней в будущем или в прошлом. Вы можете использовать ее для добавления или вычитания рабочих дней к определенной точке времени.

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

А теперь давайте подробнее рассмотрим каждую их них отдельно и посмотрим, как можно использовать для расчета будних дней в ваших таблицах Excel.

Функция РАБДЕНЬ.

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

РАБДЕНЬ(дата_начала; дни; [праздничные_дни])

Первые два обязательны, а последний – по желанию:

• Дата_начала – с нее начинается подсчет дней.
• Дни — рабочие дни, которые нужно добавить или вычесть из начальной точки отсчета. Положительное число возвращает будущий момент времени, отрицательное — возвращает в прошлое.
• Праздничные_дни — необязательный список дат, которые следует считать нерабочими днями. Это может быть либо диапазон ячеек, содержащих даты, которые вы хотите исключить из расчетов, либо константа массива.

Теперь, когда вы знаете основы, давайте посмотрим, как вы можете использовать РАБДЕНЬ в ваших таблицах Excel.

Как добавить или вычесть рабочие дни.

Чтобы рассчитать рабочие дни в Эксель, следуйте этим простым правилам:

• Чтобы добавить будние дни, введите положительное число в качестве аргумента дни в формуле РАБДЕНЬ.
• Чтобы вычесть будние дни, используйте там же отрицательное число.

Предположим, у вас в ячейке A10 указан начальный момент времени, а список праздников — в ячейках А2: А7. Вы хотите узнать, какое число будет через 90 рабочих дней в будущем и прошлом.

Вы можете сделать это, используя следующие формулы:

Чтобы добавить столько же к начальной точке, но учитывая праздничные дни в А2: А7:

=РАБДЕНЬ($A$10;-90;$A$2:$A$7)

Чтобы рассчитать сроки на основе текущей даты, используйте функцию СЕГОДНЯ() в качестве точки начала:

Чтобы добавить 90 рабочих дней:

Чтобы переместиться на такое же время назад по отношению к сегодня:

Чтобы указать время начала непосредственно в формуле, используйте формулу ДАТА():

На следующем скриншоте показаны результаты всех этих и еще нескольких формул РАБДЕНЬ:

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

Где A10 — начальная датировка, а C1 — число рабочих дней до (отрицательные числа) или после (положительные числа) нее, без учета праздников.

Настраиваем свой график выходных дней.

В случае, если у вас применяется какой-то особенный график нерабочих дней (не суббота и воскресенье), то вам в помощь – формула РАБДЕНЬ.МЕЖД (WORKDAY.INTL в английской версии).

Она появилась в Excel 2010, в более старых версиях программы ее нет.

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

Синтаксис ее выглядит следующим образом:

РАБДЕНЬ.МЕЖД (дата_начала, дни, [выходные], [праздничные_дни])

Первые два аргумента являются обязательными и знакомыми нам:

Дата_начала – начальный момент времени.

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

Последние два аргумента являются необязательными:

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

Число

Выходные дни

1 или отсутствует

Только в пятницу

Текст для указания выходного дня — серия цифр из семи нулей и единиц, представляющих семь дней недели, начиная с понедельника. 1 означает нерабочий день, и 0 — соответственно наоборот. Например:

• «0000010» — суббота — отдыхаем.
• «1000001» — понедельник и воскресенье — отдыхаем.

И вот как это может выглядеть на примере:

Я бы рекомендовал обратить внимание на конструкцию

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

Все остальное, думаю, достаточно хорошо понятно из скриншота.

Замечание. Функции Excel РАБДЕНЬ и РАБДЕНЬ.МЕЖД возвращают порядковые номера (числа), представляющие даты. Чтобы они отображались как нужно, выделите нужные ячейки и нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Число» выберите «Дата» и определите нужный формат.

Как рассчитать, сколько рабочих дней между двумя датами?

В ситуациях, когда вам нужно получить число рабочих дней в каком-то временном промежутке, используйте формулу ЧИСТРАБДНИ() (в английском варианте – NETWORKDAYS) :

ЧИСТРАБДНИ (дата_начала; дата_окончания; [праздничные_дни])

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

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

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

В результате учитываются только будние дни.

А если у нас свой особенный график труда и отдыха?

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

ЧИСТРАБДНИ.МЕЖД (WORKDAY.INTL в английском варианте) — более мощная модификация функции ЧИСТРАБДНИ, которая работает с пользовательскими параметрами выходных . Как и его предшественница, она возвращает число рабочих дней в будущем или в прошлом, но позволяет вам определить, какие именно дни недели в вашем графике нужно считать нерабочими.

Формула ЧИСТРАБДНИ.МЕЖД появилась впервые в Excel 2010, и поэтому недоступна в более ранних версиях программы.

Синтаксис ее выглядит следующим образом:

ЧИСТРАБДНИ.МЕЖД (дата_начала, дни, [выходные], [праздничные_дни])

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

Дата_начала — начальная точка.

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

Последние два аргумента являются необязательными:

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