Страницы: 1 2 След.
RSS
Прибавка рабочих часов к дате
 
Здравствуйте.

Я опять по мотивам двух тем, в которых ранее мне помогли:
Разница в минутах между датами без учета определенных условий
Неправильно рассчитывается разница в минутах между датами без учета определенных условий

И всё бы ничего, но как обычно это бывает, появилась еще одна необходимость, а именно - как прибавить к дате количество рабочих часов, среди которых не учитываются выходные, праздники, время обеда и нерабочие часы?

Нашел эту тему - Подсчет даты и времени с учетом рабочих часов и нерабочих дней - но в ней непонятные для меня условия, и я так и не смог имеющиеся там формулы адаптировать под те, что есть у меня (ссылки на темы с формулами выше).

Прикладываю файл, в котором рассчитывается разница в часах между двумя датами без учета выходных, праздников, времени обеда и нерабочих часов. Нужно в ячейке C10 к дате из ячейки A10 прибавить, например, 40 часов (только рабочих - без учета выходных, праздников, времени обеда и нерабочих часов) из ячейки B10.

Насколько я понимаю, простой переменой мест уменьшаемого/вычитаемого/разности тут не обойтись.
 
C1          =МАКС(M:M)
K1:K100 =(1-ОСТАТ(СТРОКА();2))*(СЧЁТЕСЛИМН(Праздники!A:A;ЦЕЛОЕ(J1))=0)
L2:L100 =МИН(МИН(($A$10<=J3)*(J3-$A$10)*K2;J3-J2);$B$10/24-СУММ($L$1:L1))
M2:M100 =(J2+L2)*(L2>0)
J1 =ДАТА(ГОД($A$10);МЕСЯЦ($A$10);ДЕНЬ($A$10))
J2 =$J$1+$E$2
J3 =$J$1+$G$2
J4 =$J$1+$H$2
J5 =$J$1+$F$2
J6:J100 =J2+1

Файл не грузится.
 
МатросНаЗебре, простите, но что-то непонятно без файла ваше сообщение...
 
Вариант, без учета минут.
Изменено: skais675 - 12.12.2019 15:57:19
 
skais675, хороший вариант, но да - это только часы. Львиная доля в виде минут теряется, а без этого никак :(
 
andronus с учетом минут для Вашей задачи.
Изменено: skais675 - 12.12.2019 16:27:26
 
skais675, работает, спасибо!

Но почему при смене в диапазоне N:N формулы на учет нестандартных выходных на:
Код
ЧИСТРАБДНИ.МЕЖД(M2;M2;11;Праздники!A:A)
ничего не меняется?

Я думал, смогу подправить формулы под свои условия, как уже делал ранее (для учета нестандартных рабочих графиков, отличных от 8х5), но вот не получается.
 
andronus приложите пример (может пересчет выключен?).
Или смотрите мой. Не забывайте обновлять сводную.
Изменено: skais675 - 12.12.2019 17:57:04
 
skais675, см. лист "ДрГрафик". В нем изменена формула в диапазоне N:N, а также рабочий график:
Начало раб дня - 9
Окончание раб дня - 20
Обед - 13
Конец обеда - 13

Тем самым, рабочий график: 11х6 с одним выходным (воскресенье).

С этими изменениями результат расчета в C10 не изменился и остался аналогичен расчету на листе "Разница".
 
andronus, формулы не причем, это сводную что-то перекосило, пересоздал ее.
 
 первой формуле из прошлой темы все ж надо подправить диапазон с праздниками
массивная
=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 - 13.12.2019 13:00:33
 
andronus, обе формулы рассчитаны на выходные в Сб и Вс. и для 11x6 нужно  переделывать.  Другой принцип определения.
По вопросам из тем форума, личку не читаю.
 
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 написал:
Но даже при таком графике расчет неверный

Да есть проблемка с определением попал ли выбранный час в рабочее время. Я подумаю как подправить, ну добавить учет суббот не проблема.
По вопросам из тем форума, личку не читаю.
 
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 - 13.12.2019 15:40:50
 
andronus И вопрос, может сделать еще вариант макросом? (UDF)
Скрытый текст
Изменено: skais675 - 13.12.2019 17:09:20
 
Для целых часов вроде так.
По вопросам из тем форума, личку не читаю.
 
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+MATCH(B10;MMULT(--(ROW(A1:INDEX(A:A;B10*7))>=TRANSPOSE(ROW(A1:INDEX(A:A;B10*7))));
ISNA(MATCH(INT(A10+(ROW(A1:INDEX(A:A;B10*7)))/24);Праздники!A1:A99;))*
(WEEKDAY(A10+(ROW(A1:INDEX(A:A;B10*7)))/24;2)<I2+1)*
SIGN((HOUR(A10+(ROW(A1:INDEX(A:A;B10*7)))/24)>HOUR(E2))*(HOUR(A10+(ROW(A1:INDEX(A:A;B10*7)))/24)<=HOUR(IF(G2;G2;F2)))+
(HOUR(A10+(ROW(A1:INDEX(A:A;B10*7)))/24)>HOUR(IF(H2;H2;E2)))*(HOUR(A10+(ROW(A1:INDEX(A:A;B10*7)))/24)<=HOUR(F2))));)/24
Изменено: БМВ - 17.12.2019 13:09:41
По вопросам из тем форума, личку не читаю.
 
БМВ, почти :(
Вбивая в A10 значение 06.12.2019 13:00:00, а в B10 - 120, в результате в C10 получаем 27.12.2019 13:00:00, хотя должно быть на час меньше - 27.12.2019 12:00:00.
 
ничего не знаю, пусть работают  :D

На самом деле , тут реальная проблема определения границ. оно и бомбит. разве что утяжелить формулу и считать не просто куда попал следующий час, а брать промежуток времени от предыдущего часа.
По вопросам из тем форума, личку не читаю.
 
Цитата
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?
Изменено: skais675 - 18.12.2019 17:15:45
 
skais675, 11 часов было всегда в этой теме :)
 
Это как с 9 до 20 и с обедом 1 час?
 
skais675, в графике 11х6 (с 09:00 до 20:00) нет обеда. В графике 8х5 (с 09:00 до 18:00) есть - с 13:00 до 14:00.
 
andronus В этом случае в моей формуле замените 14 (6-й параметр) на 13 типа обеда нет (с 13 до 13). Вы формулу поняли? Там все параметры задаются.
Изменено: skais675 - 18.12.2019 17:36:54
 
Цитата
andronus написал:
Прибавка рабочих часов к дате
если к дате 18.12.2019 нужно прибавить 8 рабочих часов так и пишете:
указанная дата + 8/24 в итоге получите 43817.33333333333333333333
Программисты - это люди, решающие проблемы, о существовании которых Вы не подозревали, методами, которых Вы не понимаете!
Страницы: 1 2 След.
Наверх