Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)

Простая, но весьма частая задача у многих пользователей Microsoft Excel.

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

Поиск ближайшего рабочего дня в Excel

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

Первое, что обычно приходит в голову, это конструкция с вложенными проверками дней недели с помощью функций ЕСЛИ (IF) и ДЕНЬНЕД (WEEKDAY). Что-то типа:

round-to-workday2.png

Т.е. если попали на субботу (ДЕНЬНЕД выдал 6 для даты доставки), то прибавляем еще 2 дня, чтобы сдвинуть на следующий понедельник. А если попали на воскресенье, то добавляем еще день. Не самая сложная формула.

На самом деле можно управиться существенно короче и изящнее :)

С 2007 года в Excel появилась функция РАБДЕНЬ (WORKDAY), которая умеет сдвигать исходную дату на заданное количество рабочих дней, причем сдвиг может быть как положительным (в будущее), так и отрицательным (в прошлое). Фишка в том, что если взять предыдущий день от предполагаемой даты доставки и с помощью этой функции добавить к нему один рабочий день, то мы получим либо ту же самую дату (если были будни), либо ближайший понедельник  (если доставка выпала на субботу или воскресенье). Что и требуется:

round-to-workday3.png

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

round-to-workday4.png

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

Ссылки по теме




Добрый день! Это все понятно... А как все же сделать чтобы суббота 20 февраля 2016 г. являлась рабочим днем. Как это прописать формулой?
26.05.2016 01:52:59
Доброй ночи! Формула ниже:
=ЕСЛИ(СЧЁТЕСЛИ(Рабочие;A2)=0;ИНДЕКС(Рабочие;ПОИСКПОЗ(РАБДЕНЬ(A2;1;Выходные);Рабочие;0)+10;1);ИНДЕКС(Рабочие;ПОИСКПОЗ(A2;Рабочие;0)+10;1))

Для расчета нужно сделать два диапазона: один с рабочими днями, другой - с выходными и праздниками. Ну а далее прописать формулу в какую-либо ячейку со ссылкой на ячейку, в которую будете вводить дату (в данном случае А2). В данном случае формула отсчитывает 10 рабочих дней от указанной Вами даты (если Вы указали дату, выпадающую на выходной день, то от ближайшего к ней рабочего дня).

Беда в том, что диапазоны с выходными и рабочими днями нужно обновлять ближе к концу года, когда будут известны праздники на будущий год, ну и учитывать праздники субъектов федерации (если необходимо)
Спасибо огромное за подробное разъяснение. Учиться, учиться и еще раз учиться, как дедушка Ленин говаривал.
Попробую сейчас привязать к другим Вашим темам типа "Календарь этапов проекта", "Универсальный календарь формулой" и тд
19.09.2016 09:30:14
Добрый день. Пользуюсь данной формулой. Подскажите пожалуйста как отформатировать ячейки, чтобы к прибавленной дате в формате "ДД.ММ.ГГГГ ЧЧ:ММ" в конечной ячейке  также отображался формат ДД.ММ.ГГГГ ЧЧ:ММ
Например: 05/01/2017  21:30  после формулы  =РАБДЕНЬ(E2+45-1;1;$L$2:$L$27) хочу получить 20.02.2017 21:30, а получается 20.02.2017 0:00
И как можно скопировать конечную дату для внесения в другую ячейку?
Благодарю за возможную помощь.
06.12.2016 14:59:57
=РАБДЕНЬ(E2+45-1;1;$L$2:$L$27)+E2-ЦЕЛОЕ(E2)
06.12.2016 15:25:57
Благодарю
21.04.2017 17:34:01
Николай, спасибо за оригинальное и красивое решение!

Рядом с этой функцией лежит еще одна полезная функция ЧИСТРАБДНИ

1)     Можно рассчитать количество рабочих дней в месяце:
=ЧИСТРАБДНИ(КОНМЕСЯЦА($A$1;-1)+1;КОНМЕСЯЦА(КОНМЕСЯЦА($A$1;-1)+1;0))
2)     Можно рассчитать, сколько рабочих дней уже прошло в месяце:
=ЧИСТРАБДНИ(КОНМЕСЯЦА($A$1;-1)+1;$A$1)
Где $A$1 – это дата в формате Excel, например 20.04.2017


3)     Итого можно получить % прошедшего рабочего времени в месяце, это полезно при расчете темпов выполнения KPI сотрудников!

Как рассчитывать различные KPI рекомендую подробную статью в своем блоге: http://biweb.ru/kpi_mbo_bsc.html