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

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

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

В Microsoft Excel есть две основные функции, разработанные специально для расчета дней недели: РАБОЧИЕ ДНИ и СЕТЕВЫЕ РАБОЧИЕ ДНИ. Мы также выделим дополнительные WORKDAYS.INTL и CLEANER.DAYS.INTL, которые могут быть очень полезны.

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

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

Резюме

РАБОЧИЙ ДЕНЬ.
Как добавить или вычесть рабочие дни.
- Установка программы выходного дня.
Как рассчитать, сколько рабочих дней проходит между двумя датами?
- Что, если у нас будет своя специальная программа работы и отдыха?
Как определить следующий рабочий день?
Возможные ошибки в расчетах.

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

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

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

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

РАБОЧИЙ ДЕНЬ — встроенная функция в Excel 2007, 2010, 2013 и 2016. Вы можете ввести его вручную или с помощью мастера. В предыдущих выпусках вам нужно было специально включить набор инструментов анализа.

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

РАБОЧИЙ ДЕНЬ (начальная_дата; дни; [будние_дни])

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

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

Теперь, когда вы знаете основы, давайте посмотрим, как использовать WORKDAY в таблицах Excel.

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

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

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

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

Сделать это можно по следующим формулам:

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

= РАБОЧИЙ ДЕНЬ (10 австралийских долларов; 90 австралийских долларов; 2 австралийских доллара: 7 австралийских долларов)

Чтобы вычесть 90 рабочих дней без учета праздников:

= РАБОЧИЙ ДЕНЬ (10 австралийских долларов; -90, 2 австралийских доллара: 7 австралийских долларов)

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

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

= РАБОЧИЙ ДЕНЬ (СЕГОДНЯ (), 90; 2 доллара США: 7 долларов США)

Чтобы вернуться на тот же временной интервал с сегодняшнего дня:

= РАБОЧИЙ ДЕНЬ (СЕГОДНЯ (), — 90)

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

= РАБОЧИЙ ДЕНЬ (ДАТА (2020, 1, 7), 90; 2 австралийских доллара: 7 австралийских долларов)

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

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

= РАБОЧИЙ ДЕНЬ (10 австралийских долларов; С1; 2 австралийских доллара: 7 австралийских долларов)

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

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

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

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

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

Его синтаксис следующий:

РАБДЕНЬ.INTL (дата_пуска, дни, [выходные], [праздники])

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

Start_Date — это начальная точка во времени.

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

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

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

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

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

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

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

Рекомендую обратить внимание на дизайн

= РАБДЕНЬ.INTL (10 австралийских долларов; 90; «1000001»; 2 австралийских доллара: 7 австралийских долларов)

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

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

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

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

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

РАБОЧИЕ ДНИ (start_date; end_date; [public_days])

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

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

= СЕТЕВЫЕ РАБОЧИЕ ДНИ (A10, B10)

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

= СЕТЕВЫЕ РАБОЧИЕ ДНИ (A10, B10, A2: A7)

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

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

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

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

Формула РАБДЕНЬ.INTL была впервые представлена в Excel 2010 и поэтому недоступна в более ранних версиях программы.

Его синтаксис следующий:

WORKDAY.INTL (дата_пуска, дни, [выходные], [общественные_дни])

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

Start_Date — это отправная точка.

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

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

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

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

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

«00000011» — суббота и воскресенье — выходной.
«1000001» — понедельник, воскресенье — отдых.

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

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

А теперь несколько примеров.

= NETWORKDAYS.INTL (A10; B10; 1) — знакомый вариант, когда суббота и воскресенье считаются нерабочими.

Ma = ДНИ УБОРКИ (A10; B10; 11) — отдых только по воскресеньям,

= NETWORK.DAYS (A10; B10; 2) — воскресенье и понедельник. Этот расчет полезен, если график работы вашей организации отличается от графика работы всех остальных.

= СЕТЕВЫЕ ДНИ (A11, B11; «1100000»; A $ 2: $ A $ 7)

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

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

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

= «Остающийся» & ДНЕЙ ЧИСТКИ (СЕГОДНЯ (); ДАТА (2020; 12; 31); A2: A7) & «дней до конца года!»

Ого, осталось всего 203 дня! Не так сильно, как я думал 🙂
Примечание. Чтобы соответствовать вашему индивидуальному графику работы, используйте NETWORKDAYS.INTL().

Как определить ближайший рабочий день?

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

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

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

= РАБОЧИЙ ДЕНЬ (A2-1,1)

или

= ЕСЛИ (ДЕНЬ НЕДЕЛИ (A3; 2)> 5; A3 + (8 ДНЕЙ (A3; 2)); A3)

Ближайший рабочий день в прошлом (то есть в последнюю пятницу) определяется следующим образом:

= ЕСЛИ (ДЕНЬ НЕДЕЛИ (A4; 2)> 5; MIN (A4- (ДЕНЬ НЕДЕЛИ (A4; 2) -5); A4); A4)

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

= ЕСЛИ (ДЕНЬ НЕДЕЛИ (A6,2)> 6, A6 + (8-ДНЕЙ (A6,2)), MIN (A6- (ДЕНЬ НЕДЕЛИ (A6,2) -5), A6))

Как видите, это не так уж и сложно.

Возможные ошибки при расчетах.

Если ваша формула — РАБДЕНЬ, РАБДЕНЬ.INTL, ЧИСТРАБДНИ или ЧИСТРАБДНИ.INTL, ошибка возвращает # ЧИСЛО! или # ЗНАЧ! Причина может быть в одном из следующих:

аргументы начала и конца временного интервала передают неверные данные (скорее всего, не дату, написанную в виде текста)
аргумент выходных дней неверен (например, 22 или «000000011»).

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