Поиск  Пользователи  Правила  Войти
Форумы "Планета Excel" » Вопросы по Microsoft Excel
Страницы: 1
RSS
Расчет следующего рабочего дня после отпуска, Необходимо рассчитать дату выхода на работу после отпуска с учетом выходных, а также праздников и переносов.
 
#1
07.05.2025 22:34:18
Доброго времени суток. Нужна помощь профессионалов, т.к. задача со звездочкой.
Помогите составить формулу (Office 2019) для расчёта даты выхода на работу сотрудников после отпуска с учетом выходных, а также праздников и переносов по следующим условиям:
1. Если в диапазон отпуска попал перенесенный выходной день, то перенесенный рабочий день  должен учитываться как выходной;
2. Если окончание отпуска попадает на пятницу или субботу, или предпраздничный день, то выход на работу должен быть в первый рабочий день с учетом выходных, а также праздников и переносов.
3. Таблица с праздничными днями и переносами размещена на отдельном листе.
Заранее спасибо!
Изменено: Pisnes - 07.05.2025 22:34:43
 
 
 
#2
08.05.2025 08:20:20
=IF(H4="";"";H4+MATCH(0;COUNTIF(Праздники!G:I;H4+ROW(1:9))+(WEEKDAY(H4+ROW(1:9);2)>5+(COUNTIF(Праздники!K:K;H4+ROW(1:9))*2));))
Первую тоже мне кажется избавить от лишнего.
Изменено: БМВ - 08.05.2025 08:23:32
По вопросам из тем форума, личку не читаю.
 
 
 
#3
08.05.2025 09:45:01
Цитата
написал:
=IF(H4="";"";H4+MATCH(0;COUNTIF(Праздники!G:I;H4+ROW(1:9))+(WEEKDAY(H4+ROW(1:9);2)>5+(COUNTIF(Праздники!K:K;H4+ROW(1:9))*2)))Первую тоже мне кажется избавить от лишнего.
Спасибо, формула замечательная, но не полностью учитывает условие №1.
Пример :
Сотрудник ушел в отпуск на 10 дней с 01.07.2025 окончание отпуска рассчитывается верно 11.07.2025 (с учетом праздничного дня 03.07.2025), но первый рабочий день для него формула выдает 12.07.2025, без учета переносов рабочих и выходных дней. (рабочий день 04.07.2025 объявлен выходным и перенесен на 12.07.2025, а, следовательно, для сотрудника, который 04.07.25 находился в отпуске, 12.07.2025 будет выходным)  
P.S. Формула расчета окончания отпуска действительно стала проще.
 
 
 
#4
08.05.2025 10:11:24
соглашусь что не понял в данных что соответствуют переносы построчно, но тут надо серьезно подумать ибо получается надо учитывать не только что день рабочий, но и входил ли в период отпуска день на который был перенос. Чуть сложнее будет. Но реализуемо.

=IF(H4="";"";H4+MATCH(0;COUNTIF(Праздники!G:I;H4+ROW(1:9))+(WEEKDAY(H4+ROW(1:9);2)>5+(COUNTIF(Праздники!K:K;H4+ROW(1:9))*2*IFERROR(ABS((G4+(H4-G4)/2-INDEX(Праздники!I:I;MATCH(H4+ROW(1:9);Праздники!K:K;))))>(H4-G4)/2;1)));))

Но проверьте, времени не хватает погонять.
По вопросам из тем форума, личку не читаю.
 
 
 
#5
08.05.2025 10:14:51
в H4 формула не совсем корректна. Логика у формулы посчитать праздники и прибавить к отпуску. Но вопрос: что если количество праздников накопится таким образом, что это добавленное время "накроет" еще какой-нибудь праздник?
лучше искать в интервале с запасом и брать наименьшее:
=ЕСЛИ(G4=0;"";АГРЕГАТ(15;6;(СТРОКА(A1:ИНДЕКС(A:A;30))+G4-1)/(СЧЁТЕСЛИ(Праздники!G3:G82;СТРОКА(A1:ИНДЕКС(A:A;30))+G4-1)=0);F4))
но для "Office 2019" это проще
=ЕСЛИОШИБКА(РАБДЕНЬ.МЕЖД(G4-1;F4;"0000000";Праздники!G3:G82);"")
Цитата
условие №1
=РАБДЕНЬ(H4;1;Праздники!G3:H82)
...чтоб не усложнять - табличку с праздниками изменил самую малость
Изменено: Павел \Ʌ/ - 08.05.2025 10:15:30
 
 
 
#6
08.05.2025 10:31:12
Павел \Ʌ/, встроенные не учитывают переносы. если честно, то я тоже не считаю определение последнего дня отпуска корректным, но вопрос был про вторую :-)
По вопросам из тем форума, личку не читаю.
 
 
 
#7
08.05.2025 10:53:08
Цитата
БМВ:  встроенные не учитывают переносы
ну как раз рабочие выходные и не надо учитывать, как я понял
Только вот про выходные рабочие сказано не было )
Исходил из того что не логично выходить в день когда на работе никого нет ))
 
 
 
#8
08.05.2025 11:26:43
Задача решена! Павел \Ʌ/ и БМВ огромное спасибо!!!!
1. Для определения последнего дня отпуска использовал формулу =ЕСЛИОШИБКА(РАБДЕНЬ.МЕЖД(G4-1;F4;"0000000";Праздники!$G$3:$G$82);""), т.к. формула через функцию "Агрегат" выдает ошибку при количестве дней отпуска 30 и более!
2. Для определения следующего рабочего дня после отпуска использовал формулу =ЕСЛИОШИБКА(РАБДЕНЬ(H4;1;Праздники!$G$3:$H$82);"").
Вроде все правильно считает, багов не нашел.
 
 
 
#9
08.05.2025 11:39:22
Цитата
Pisnes:   т.к. формула через функцию "Агрегат" выдает ошибку при количестве дней отпуска 30 и более!
)  да для вашей версии экселя она и не нужна
...но пытливый читатель этой темы, не обладающий соответствующей функцией, надеюс догадается увеличить ентот предел (без фанатизма конечно)
)
 
 
 
#10
08.05.2025 13:47:43
Цитата
Pisnes написал:
Вроде все правильно считает, багов не нашел.
допустим отпуск с 05.07.2025 и 7 дней Каков должен быть результат? Повторю, функция встроенная не учитывает переносы, то есть черные субботы.

Цитата
Pisnes написал:
Только вот про выходные рабочие сказано не было )
Цитата
Pisnes написал:
с учетом выходных, а также праздников и переносов.
перенос делает из рабочего дня выходной и из выходного рабочий.
По вопросам из тем форума, личку не читаю.
 
 
 
#11
08.05.2025 14:16:36
Цитата
написал:
Цитата
допустим отпуск с 05.07.2025 и 7 дней Каков должен быть результат?
Действительно, имеется ошибочка! при отпуске с 05.07.25 продолжительность 7 дней выход на работу должен быть 12.07.25, а формула выдает 14.07.25.
есть мысли как устранить этот недочет?
 
 
 
#12
08.05.2025 14:30:06
Цитата
Pisnes написал:
есть мысли как устранить этот недочет?
ну мой вариант последний это учитывает. Другое дело что он не такой краткий.
По вопросам из тем форума, личку не читаю.
 
 
 
#13
08.05.2025 15:11:22
Цитата
написал:
ну мой вариант последний это учитывает. Другое дело что он не такой краткий
Спасибо! Теперь все работает как нужно!
 
 
 
#14
09.05.2025 08:00:35
Цитата
БМВ:   допустим отпуск с 05.07.2025 и 7 дней Каков должен быть результат? Повторю, функция встроенная не учитывает переносы, то есть черные субботы.
честно говоря не проникся полностью переносами )
если вопрос только с "черными субботами" такой вариант:
=ЕСЛИОШИБКА((H4+1)/(ПРОСМОТР(ИНДЕКС(Праздники!H3:H82;ПОИСКПОЗ(H4+1;Праздники!I3:I82;));ВЫБОР({1;2;3};;G4;H4+1))<>G4);РАБДЕНЬ(H4;1;Праздники!G3:H82))
для "теоретической экзотики":
=МИН(ЕСЛИОШИБКА((H4+{1:2:3:4:5:6:7})/(ПРОСМОТР(ИНДЕКС(Праздники!H3:H82;ПОИСКПОЗ(H4+{1:2:3:4:5:6:7};Праздники!I3:I82;));ВЫБОР({1;2;3};;G4;H4+1))<>G4);РАБДЕНЬ(H4+{1:2:3:4:5:6:7};1;Праздники!G3:H82)))
 
 
 
#15
09.05.2025 08:51:46
Павел \Ʌ/,  даже я уже запутался в вариантах :-)

Цитата
Pisnes написал:
Сотрудник ушел в отпуск на 10 дней с 01.07.2025 окончание отпуска рассчитывается верно 11.07.2025 (с учетом праздничного дня 03.07.2025), но первый рабочий день для него формула выдает 12.07.2025, без учета переносов рабочих и выходных дней. (рабочий день 04.07.2025 объявлен выходным и перенесен на 12.07.2025, а, следовательно, для сотрудника, который 04.07.25 находился в отпуске, 12.07.2025 будет выходным)  

Формула срабатывает, но только при конкретных данных, допустим перенос сделан не с 12.07, а с 13го, то есть 13го общий рабочий день. а 12го общи выходной.
По вопросам из тем форума, личку не читаю.
 
 
 
#16
09.05.2025 09:42:41
Цитата
БМВ:  13го общий рабочий день
написал же:
Цитата
для "теоретической экзотики": =МИН(ЕСЛИОШИБКА((H4+{1:2:3:4:5:6:7}...
массивка
можно усложнить: к примеру отпуск закончился сразу перед праздниками, а после них первый рабочий - чёрная воскресенья, и с переносами поиграться (из прошлого, из будущего...)    )
...горизонт изощрений можно расширить )
Моя память говорит что суббот должно быть достаточно, но чем черт правительство не шутит...? )
 
 
 
#17
09.05.2025 10:12:20
так экзотика тоже  не работает. для 01.07 и 10 дней  . тут прикол в том попадал ли перенесенный день в период отпуска.
Изменено: БМВ - 09.05.2025 10:14:51
По вопросам из тем форума, личку не читаю.
 
 
 
#18
09.05.2025 10:22:50
 Может я, конечно, что-то не понял но давайте разбираться
Цитата
БМВ:   не с 12.07, а с 13го, то есть 13го общий рабочий день. а 12го общи выходной.
Pisnes:    Сотрудник ушел в отпуск на 10 дней с 01.07.2025 окончание отпуска рассчитывается верно 11.07.2025 (с учетом праздничного дня 03.07.2025), но первый рабочий день для него формула выдает 12.07.2025, без учета переносов рабочих и выходных дней. (рабочий день 04.07.2025 объявлен выходным и перенесен на 12.07.2025   13.07.2025, а, следовательно, для сотрудника, который 04.07.25 находился в отпуске, 12.07.2025   13.07.2025 будет выходным)  
формула возвращает 14.07.2025
а для
Цитата
допустим отпуск с 05.07.2025 и 7 дней
13.07.2025
Что не так?
...
но одну некорректность всё ж у себя увидел (огрехи копипаста)
=МИН(ЕСЛИОШИБКА((H4+{1:2:3:4:5:6:7})/(ПРОСМОТР(ИНДЕКС(Праздники!H3:H82;ПОИСКПОЗ(H4+{1:2:3:4:5:6:7};Праздники!I3:I82;));ВЫБОР({1;2;3};;G4;H4+1))<>G4);РАБДЕНЬ(H4;{1:2:3:4:5:6:7};Праздники!G3:H82)))
Изменено: Павел \Ʌ/ - 09.05.2025 11:02:19
 
 
 
#19
09.05.2025 11:34:29
Или пардон, или не помню комбинацию при которой было расхождение.
По вопросам из тем форума, личку не читаю.
 
 
 
Страницы: 1
Читают тему
Наверх