Поиск ближайшего рабочего дня функцией РАБДЕНЬ (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
28.02.2018 17:16:45
У меня задача  последний рабочий день месяца  следующего за датой. Можно использовать формулу:
 =РАБДЕНЬ(РАБДЕНЬ(КОНМЕСЯЦА(A1;1);1);-1)
Где A1 – это дата в формате Excel, например 05.01.2018, результат будет 28.02.2018

Например: Накладная от 05.01.2018, последний день оплаты 28.02.2018 (последний рабочий день следующего месяца - февраля)
14.03.2018 09:58:10
Легко сообразить, что подобный подход можно использовать и для поиска ближайшего предыдущего, а не следующего рабочего дня
Люди добрые, подскажите, пожалуйста. Ну никак не могу сообразить как написать формулу, чтобы она выдавала день перед праздниками или выходными.

upd Сообразил... Нужно наоборот, сначала прибавить день, а в количестве дней указать -1 и формула будет считать в обратную сторону с учетом праздников и выходных...
31.03.2018 17:15:41
Помогите пожалуйста составить формулу?У меня есть график дежурств где рабочие дни сутки через трое.Я просто заливаю ячейку в чёрный цвет в рабочий день.Так вот можно ли написать формулу и условно форматировать чтобы закрашеннная ячейка автоматически подсчитывалась и заливалась на каждые месяца когда меняю месяц
23.05.2018 21:18:52
Используйте функцию ОСТАТ (MOD), чтобы вычислить остаток от деления даты на 4 (каждый четвертый день - красим).
27.11.2018 10:22:53
есть задачка, найти предыдущий четверг от заданной даты.
Пытался на основе этого метода формулу написать, мне кажется, решение где-то рядом, но никак не могу сообразить, какая формула будет.
21.09.2019 16:58:37
=РАБДЕНЬ.МЕЖД(A1;-1;"1110111")
17.09.2019 19:34:12
Добрый день, возможно ли выполнить эту задачу в Excel ? Рассчитать продолжительность раб. Процесса, где есть:
1. Старт, к примеру 17.09.2019 22:30 длительность производственного процесса, 130:00:00 часов.
2. Неделя, три смены Начало 06:00 понедельник и заканчивается в субботу 06:00 утра.
3.  На недели есть праздник 19.09.2019 (четверг) в котором ночная смена до 06:00 что дает начало празднику.
4. И снова в пятницу начало 06:00
Как рассчитать продолжительность пр. процесса коротких или длинных с выходом точной даты и времени (к примеру, будет готово.: 23.09.2019 09:15) функция расчёта Рабочих дней с включенными выходными и праздниками не справляется и у меня недостаточно знаний в Excel.
21.09.2019 16:55:46
Андреас, с такими задачами лучше на форум в раздел Работа :)
13.01.2020 14:40:49
Добрый день, Николай
Спасибо Вам за ваш труд и полезную информацию.
Как  реализовать функцию (WORKDAY) в составе ранее предложенного Вами макроса в теме "Автоматическая вставка текущей даты в ячейку при вводе данных".
Мучаюсь над этим 3й день.
Заранее спасибо
15.01.2020 09:20:02
Как вариант:
Function fnСледующийРабочийДень(dДень As Date) As Date
    
    Dim dYYYY As String, arrПраздники()
    dYYYY = IIf(Day(dДень) = 31 And Month(dДень) = 12, Year(dДень + 1), Year(dДень))
        
    'Добавить праздничные (а также официальные нерабочие) дни при необходимости (после выхода официальных нормативных актов):
    arrПраздники = Array(CLng(CDate("01.01." & dYYYY)), CLng(CDate("02.01." & dYYYY)), CLng(CDate("03.01." & dYYYY)), _
    CLng(CDate("04.01." & dYYYY)), CLng(CDate("05.01." & dYYYY)), CLng(CDate("06.01." & dYYYY)), _
    CLng(CDate("07.01." & dYYYY)), CLng(CDate("08.01." & dYYYY)), CLng(CDate("23.02." & dYYYY)), _
    CLng(CDate("08.03." & dYYYY)), CLng(CDate("01.05." & dYYYY)), CLng(CDate("09.05." & dYYYY)), _
    CLng(CDate("12.06." & dYYYY)), CLng(CDate("04.11." & dYYYY)))
    
    fnСледующийРабочийДень = Format(Application.WorksheetFunction.WorkDay(DateValue(dДень), 1, arrПраздники), "dd.mm.yyyy")
    
End Function
15.01.2020 16:44:51
aequit , Большое спасибо за Ваш ответ.
arrПраздники - у меня именованная область "выходные".
Я с VBA только знакомлюсь и не очень понимаю куда все вами написанное подставить?

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

Private Sub Worksheet_Change(ByVal Target As Range)
   
   For Each cell In Target   'проходим по всем измененным ячейкам
If Not Intersect(cell, Range("A2:A100")) Is Nothing Then  'если изменененная ячейка попадает в диапазон A2:A100
With cell.Offset(0, 1)   'вводим в соседнюю справа ячейку дату
  .Value = Now
  .EntireColumn.AutoFit 'выполняем автоподбор ширины для столбца B, чтобы дата умещалась в ячейке
End With
End If
   Next cell
End Sub
17.01.2020 11:49:43
Доработал функцию. Вариант аналога РАБДЕНЬ (WORKDAY) на чистом VBA без формул и обращения к листам рабочей книги (перечень праздничных и выходных дней, а также рабочих суббот хранится в коде функции). Выложил на форуме ссылка на пост.
18.01.2020 14:19:37
Спасибо за Ваш ответ.  
19.01.2020 08:24:22
Не за что...
Переписал функцию, оптимизировано быстродействие. В том же посте, по ссылке выше, код и пример.
05.02.2020 08:12:54
Добрый день! Подскажите, пожалуйста, как мне от начальной даты и конечной даты высчитать количество рабочих дней?
Наверх