И всё бы ничего, но как обычно это бывает, появилась еще одна необходимость, а именно - как прибавить к дате количество рабочих часов, среди которых не учитываются выходные, праздники, время обеда и нерабочие часы?
Прикладываю файл, в котором рассчитывается разница в часах между двумя датами без учета выходных, праздников, времени обеда и нерабочих часов. Нужно в ячейке C10 к дате из ячейки A10 прибавить, например, 40 часов (только рабочих - без учета выходных, праздников, времени обеда и нерабочих часов) из ячейки B10.
Насколько я понимаю, простой переменой мест уменьшаемого/вычитаемого/разности тут не обойтись.
Но почему при смене в диапазоне N:N формулы на учет нестандартных выходных на:
Код
ЧИСТРАБДНИ.МЕЖД(M2;M2;11;Праздники!A:A)
ничего не меняется?
Я думал, смогу подправить формулы под свои условия, как уже делал ранее (для учета нестандартных рабочих графиков, отличных от 8х5), но вот не получается.
skais675, см. лист "ДрГрафик". В нем изменена формула в диапазоне N:N, а также рабочий график: Начало раб дня - 9 Окончание раб дня - 20 Обед - 13 Конец обеда - 13
Тем самым, рабочий график: 11х6 с одним выходным (воскресенье).
С этими изменениями результат расчета в C10 не изменился и остался аналогичен расчету на листе "Разница".
первой формуле из прошлой темы все ж надо подправить диапазон с праздниками массивная =A10+MATCH(B10;MMULT(--(ROW(A1:INDEX(A:A;B10+14*24))>=TRANSPOSE(ROW(A1:INDEX(A:A;B10+14*24)))); ISNA(MATCH(INT(A10+(ROW(A1:INDEX(A:A;B10+14*24))-1)/24);Праздники!A1:A99;))* (WEEKDAY(A10+(ROW(A1:INDEX(A:A;B10+14*24))-1)/24;2)<6)* SIGN((MOD(A10+(ROW(A1:INDEX(A:A;B10+14*24))-1)/24;1)>E2)*(MOD(A10+(ROW(A1:INDEX(A:A;B10+14*24))-1)/24;1)<=G2)+ (MOD(A10+(ROW(A1:INDEX(A:A;B10+14*24))-1)/24;1)>H2)*(MOD(A10+(ROW(A1:INDEX(A:A;B10+14*24))-1)/24;1)<=F2)));)/24
skais675, БМВ, благодарю. Но в итоге ошибки Проверяю на двух периодах:
1. 40 рабочих часов по графику 8х5 (8 часов, понедельник-пятница) с 31.10.2019 12:00. Должно получиться 08.11.2019 12:00:
2. 40 рабочих часов по графику 11х6 (11 часов, понедельник-суббота) с 31.10.2019 12:00. Должно получиться 05.11.2019 19:00:
skais675, проверяю, и вот что получается. • На листе "Разница" (это лист для 8х5) в A10 вбиваю дату 31.10.2019 12:00, в результате (желтая C10) получается #Н/Д. Обращаю внимание на зеленую M2 и понимаю, что туда надо вбить начало дня из A10 (в данном случае это 31.10.2019 0:00) и обновить сводную. Результат такой же. Если вбить 31.10.2019 12:00 и 08.11.2019 12:00 в A2 и B2, то разница получается не 40 часов, а 41. • На листе "ДрГрафик" (это лист для 11х6) все то же самое, но все равно распишу: в A10 вбиваю дату 31.10.2019 12:00, в результате (желтая C10) получается #Н/Д. Обращаю внимание на зеленую M2 и понимаю, что туда надо вбить начало дня из A10 (в данном случае это 31.10.2019 0:00) и обновить сводную. Результат такой же. Если вбить 31.10.2019 12:00 и 08.11.2019 12:00 в A2 и B2, то разница получается не 40 часов, а 41.
БМВ, про диапазон праздников спасибо, не усмотрел. Теперь ваш файл проверяю, и вот что получается. • Не меняя значений в E2:H2 (то есть, оставляя график 8х5), вбиваю в A10 дату 31.10.2019 12:00, в результате (желтая C10) получается 07.11.2019 16:00, а должно 08.11.2019 12:00. При этом, в C2 разница между датами получается не 40 часов, а 35. •Если же изменить значения в E2:H2 на "Окончание раб дня" = 20:00, "Конец обеда" = 13:00 (то есть, создав график 11х6), то в результате (желтая C10) получается 07.11.2019 12:00, а должно 05.11.2019 19:00. При этом, в C2 разница между датами получается не 40 часов, а 35.
andronus, Возможно проверка и неверна, я ей не заморачивался. Проверяйте по факту, то о чем просили, ячейку С10. Если что не так, готов выслушать. И пользуйтесь ДрГрафик, а то всех запутаете.
skais675, я и проверяю C10, я же написал об этом. Вот сейчас беру ваш файл из поста от 13 Дек 2019 12:52:01. На листе "ДрГрафик" в A10 вбиваю дату, отличную от вашей (иначе если формула работает только на одной дате, то какой в этом смысл?), а именно 31.10.2019 12:00:00, получаю верную итоговую дату 05.11.2019 19:00. Все верно. Спасибо. Подскажите, пожалуйста: 1. Что за столбцы M-Q? То есть, что нужно вбивать в M2, если дата будет, например, май 2020 или январь 2017? 2. Как переделать расчет на другие графики? Я вижу, что в расчетах задействованы ячейки E2-H2, но их изменение ни к чему не приводит.
БМВ, формулы рассчитаны на 8х5, это понятно. Но даже при таком графике расчет неверный, я выше описал мои действия. Не могли бы вы подсказать, как вашу формулу изменить на другой график? Например, на тот же 11х6. Я вижу, что в расчетах задействованы ячейки E2-H2, значит в них для начала надо изменить значения, но потом, видимо, нужно также изменить и расчетную формулу в C10.
andronus Вы все правильно понимаете, Вам был предложен вариант. В идеале можно было бы и макросом, чтоб не было дополнительных таблиц и тд. Но все упирается в то, какие условия и тз в общем. Исходя из этого все выстраивается. В M2 Вы должны вписать начало и дотянуть до конца периода в котором собираетесь работать. Далее обновляете сводную.
Цитата
что в расчетах задействованы ячейки E2-H2, но их изменение ни к чему не приводит.<br>
еще как приводит! Если пересчет происходит и далее обновите сводную.
skais675, скажите, что означает "дотянуть до конца периода в котором собираетесь работать"? Я же не знаю, когда период заканчивается. Этим фактически и занимается формула в C10, что выводит финальный результат в виде прибавки кол-ва часов к дате. Вот моя последовательность действий сейчас в вашем файле от 13 Дек 2019 12:52:01 (на листе "ДрГрафик"): 1. В A10 вбиваю дату 06.12.2019 13:00, в C10 получается 00.01.1900 1:00. 2. В M2 вбиваю 06.12.2019 0:00, после пересчета период дат в столбце M равен 06.12.2019 0:00-01.02.2020 7:00. 3. Обновляю сводную. В C10 получается #Н/Д
Что я делаю не так? Подозреваю, что что-то не так с "дотянуть до конца периода в котором собираетесь работать".
andronus И вопрос, может сделать еще вариант макросом? (UDF)
Скрытый текст
Код
Function ddd(sdate As Date, hours As Integer, tbeg As Integer, tend As Integer, pbeg As Integer, pend As Integer, tip As Integer)
Dim dif_Minute As Long
With Sheets("Праздники")
arr = .Range("A2:A" & .Cells(.Rows.Count, 1).End(xlUp).Row).Value
End With
Set slov = CreateObject("scripting.dictionary")
For i = 1 To UBound(arr)
slov.Item(arr(i, 1)) = i
Next
dif_Minute = Minute(sdate) / 1440
If dif_Minute > 0 Then sdate = sdate + 1 / 24 - dif_Minute
Do While hours > 0
sdate = sdate + 1 / 24
If Not slov.exists(sdate) Then
If Weekday(sdate, 2) <= tip Then
If Hour(sdate) >= tbeg And Hour(sdate) < tend And Not (Hour(sdate) >= pbeg And Hour(sdate) < pend) Then
hours = hours - 1
End If
End If
End If
Loop
ddd = sdate - dif_Minute
End Function
skais675, БМВ, спасибо. Прошу прощения, три дня не было возможности зайти на форум. Проверил сейчас.
skais675, очень классный вариант. Но вбивая в A10 значение 06.12.2019 13:00:00, а в B10 - 120, в результате в C10 получаем 20.12.2019 12:00, что на сутки меньше ожидаемого (должно быть 19.12.2019 12:00).
БМВ, вбивая в A10 значение 06.12.2019 13:00:00, а в B10 - 120, в результате в C10 получаем #Н/Д.
БМВ, почти Вбивая в A10 значение 06.12.2019 13:00:00, а в B10 - 120, в результате в C10 получаем 27.12.2019 13:00:00, хотя должно быть на час меньше - 27.12.2019 12:00:00.
На самом деле , тут реальная проблема определения границ. оно и бомбит. разве что утяжелить формулу и считать не просто куда попал следующий час, а брать промежуток времени от предыдущего часа.
andronus написал: skais675 , очень классный вариант. Но вбивая в A10 значение 06.12.2019 13:00:00, а в B10 - 120, в результате в C10 получаем 20.12.2019 12:00, что на сутки меньше ожидаемого (должно быть 19.12.2019 12:00).
Что-то я не понимаю 120 -это 12 дней вперед (10 рабочих часов в день). 7,9,10,11, 12,13,14,16, 17,18,19,20 Получилось 20, а почему Вы считаете что должно быть 19?