Поиск ближайшего рабочего дня функцией РАБДЕНЬ (WORKDAY)
Простая, но весьма частая задача у многих пользователей Microsoft Excel.
Предположим, что нам необходимо рассчитать срок доставки товара, зная дату отправки и длительность. Поскольку Excel на самом деле хранит даты как числа (количество дней с 1 января 1900 года), то простое сложение легко даст нам предполагаемую конечную дату доставки:
Однако, как видно из предыдущей картинки, нет никакой гарантии, что полученный срок не попадет на выходные, когда доставка не производится. Тогда нужно взять ближайший рабочий день, т.е. следующий понедельник, но какой формулой это лучше сделать?
Первое, что обычно приходит в голову, это конструкция с вложенными проверками дней недели с помощью функций ЕСЛИ (IF) и ДЕНЬНЕД (WEEKDAY). Что-то типа:
Т.е. если попали на субботу (ДЕНЬНЕД выдал 6 для даты доставки), то прибавляем еще 2 дня, чтобы сдвинуть на следующий понедельник. А если попали на воскресенье, то добавляем еще день. Не самая сложная формула.
На самом деле можно управиться существенно короче и изящнее :)
С 2007 года в Excel появилась функция РАБДЕНЬ (WORKDAY), которая умеет сдвигать исходную дату на заданное количество рабочих дней, причем сдвиг может быть как положительным (в будущее), так и отрицательным (в прошлое). Фишка в том, что если взять предыдущий день от предполагаемой даты доставки и с помощью этой функции добавить к нему один рабочий день, то мы получим либо ту же самую дату (если были будни), либо ближайший понедельник (если доставка выпала на субботу или воскресенье). Что и требуется:
Приятным бонусом идет возможность указать список праздничных выходных дней, которые функция РАБДЕНЬ тоже будет воспринимать как нерабочие помимо суббот и воскресений. Диапазон с праздниками можно задать третьим аргументом:
Легко сообразить, что подобный подход можно использовать и для поиска ближайшего предыдущего, а не следующего рабочего дня.
Ссылки по теме
- Как Excel на самом деле хранит и обрабатывает даты и время
- Как вычислить возраст или стаж в Microsoft Excel
- Как определить сколько заданных дней недели попадает в интервал дат
=ЕСЛИ(СЧЁТЕСЛИ(Рабочие;A2)=0;ИНДЕКС(Рабочие;ПОИСКПОЗ(РАБДЕНЬ(A2;1;Выходные);Рабочие;0)+10;1);ИНДЕКС(Рабочие;ПОИСКПОЗ(A2;Рабочие;0)+10;1))
Для расчета нужно сделать два диапазона: один с рабочими днями, другой - с выходными и праздниками. Ну а далее прописать формулу в какую-либо ячейку со ссылкой на ячейку, в которую будете вводить дату (в данном случае А2). В данном случае формула отсчитывает 10 рабочих дней от указанной Вами даты (если Вы указали дату, выпадающую на выходной день, то от ближайшего к ней рабочего дня).
Беда в том, что диапазоны с выходными и рабочими днями нужно обновлять ближе к концу года, когда будут известны праздники на будущий год, ну и учитывать праздники субъектов федерации (если необходимо)
Попробую сейчас привязать к другим Вашим темам типа "Календарь этапов проекта", "Универсальный календарь формулой" и тд
Например: 05/01/2017 21:30 после формулы =РАБДЕНЬ(E2+45-1;1;$L$2:$L$27) хочу получить 20.02.2017 21:30, а получается 20.02.2017 0:00
И как можно скопировать конечную дату для внесения в другую ячейку?
Благодарю за возможную помощь.
Рядом с этой функцией лежит еще одна полезная функция ЧИСТРАБДНИ
1) Можно рассчитать количество рабочих дней в месяце:
3) Итого можно получить % прошедшего рабочего времени в месяце, это полезно при расчете темпов выполнения KPI сотрудников!
Как рассчитывать различные KPI рекомендую подробную статью в своем блоге:
Например: Накладная от 05.01.2018, последний день оплаты 28.02.2018 (последний рабочий день следующего месяца - февраля)
upd Сообразил... Нужно наоборот, сначала прибавить день, а в количестве дней указать -1 и формула будет считать в обратную сторону с учетом праздников и выходных...
Пытался на основе этого метода формулу написать, мне кажется, решение где-то рядом, но никак не могу сообразить, какая формула будет.
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.
Спасибо Вам за ваш труд и полезную информацию.
Как реализовать функцию (WORKDAY) в составе ранее предложенного Вами макроса в теме "Автоматическая вставка текущей даты в ячейку при вводе данных".
Мучаюсь над этим 3й день.
Заранее спасибо
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 FunctionarrПраздники - у меня именованная область "выходные".
Я с 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
Переписал функцию, оптимизировано быстродействие. В том же посте, по ссылке выше, код и пример.
Подскажите, пожалуйста, как пользоваться этим календарём?
https//www.planetaexcel.ru/techniques/6/13289/
у меня умная таблица-Производственный_календарь, столбец-Дата
Нужно считать рабочие дни исполнения обязательств по условиям контракта.