Excel: Расчет переработок после 18:00

[Данила]

Доброго времени суток!

Начало переработки:
если начало работы меньше или равно 18:00, то начало переработки 18:00
если начало работы больше 18:00, то начало переработки - это начало работы

Время переработки (кол-во минут переработки):
если начало работы меньше или равно 18:00, то сметное время минус (18:00 минус начало работы)
если начало работы больше 18:00, то время переработки совпадает со сметным временем

Конец переработки:
начало переработки + время переработки

Все остальные действия с переходом из вечера в ночь, из первой половины ночи во вторую аналогичны самой первой теме, где разбирали формулу с техниками, за исключением лишь того, что правило расчёта переработок теперь то, что описано выше.

В выходные и праздничные дни первое условие начинает действовать с 9 утра, оставшиеся 2 также действуют в отведенное для них время.

Примеры расчёта времени переработки

Примеры

Все примеры для рабочих дней. Для выходных по-другому.

Пример 1
Начало работы 9:00, конец работы 18:00, сметное время 900.
18:00-9:00=540 минут
900-540=360

Пример 2
Начало работы 9:00, конец работы 19:00, сметное время 900.
18:00 - 9:00 = 540 минут
900-540 = 360

Пример 3
Начало работы 15:00, конец работы 17:00, сметное время 240.
18:00-15:00=180 минут
240-180=60 минут

Пример 4
Начало работы 18:00, конец работы 21:00, сметное время 240.
18:00-18:00=0 минут
240-0=240 минут

[свернуть]

Полностью расчёт

Спойлер

Начало работы 19:00, конец работы 21:00, сметное время 240

Время начала лежит за границей 18:00, значит время переработки совпадает со сметным временем.
Рассчитываем время окончания переработки: 19:00 + 4 часа = 23:00
4 часа - это сметные минуты, переведённые в часы: 240/60=4

23 не переходит за границу 00:00, значит это первый вид переработки:
240*4,05=972 рубля

[свернуть]

[вложение удалено администратором]

[Администратор]

Решение в файле.
Нет умножения 4,05 на 60, т.к. расчёты изначально находятся в минутах. То есть в расчётах используется кол-во минут переработки.

[вложение удалено администратором]

[Данила]

Спасибо за формулу.

Но возник нюанс, который я решил самостоятельно частично. Если техник работает в выходной ДЕНЬНЕД(Е3;2)>5 и/или в праздничные (это я не смог реализовать в прошлой формуле и не знаю как реализовать здесь) дни, то границу по первому условию я передвинул до 00:00 часов и всё заработало, но формула стала больше в два раза:

Формула

=ЕСЛИ(ДЕНЬНЕД(E3;2)>5;
(ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(STime_fact<=--"00:00";--"00:00";STime_fact);"[м]");0) +
ЕСЛИ(IsSecond;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"03:00";1+"03:00";ETime_sm)-ЕСЛИ(STime_fact<=1+"00:00";1+"00:00";STime_fact);"[м]")*1,7;0) +
ЕСЛИ(IsThird;ТЕКСТ(ETime_sm-ЕСЛИ(STime_fact<=1+"03:00";1+"03:00";STime_fact);"[м]")*2,5;0))*4,05;

(ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(STime_fact<=--"18:00";--"18:00";STime_fact);"[м]");0) +
ЕСЛИ(IsSecond;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"03:00";1+"03:00";ETime_sm)-ЕСЛИ(STime_fact<=1+"00:00";1+"00:00";STime_fact);"[м]")*1,7;0) +
ЕСЛИ(IsThird;ТЕКСТ(ETime_sm-ЕСЛИ(STime_fact<=1+"03:00";1+"03:00";STime_fact);"[м]")*2,5;0))*4,05)

[свернуть]

Если сократить для понимания в данном случае до первого условия, где граница стоит до 18:00, потому что остальные два без изменений, то получается так:

=ЕСЛИ(ДЕНЬНЕД(E3;2)>5;                                           
((ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(STime_fact<=--"00:00";--"00:00";STime_fact);"[м]");0);                                                                       
((ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(STime_fact<=--"18:00";--"18:00";STime_fact);"[м]");0)

Ну и собственно вопрос - как это условие ЕСЛИ(ДЕНЬНЕД(E3;2)>5 встроить в формулу очередным ЕСЛИ, чтобы не удлинять формулу почти полным дублированием (Значение, если истина); (Значение, если ложь) за исключением изменения по границе в первом условии?

И второй вопрос - как сюда подкрутить государственные праздничные дни, которые бы воспринимались формулой тоже как выходные?

Если по второму вопросу -трудозатратно или формула увеличится в разы, то можно и проигнорировать данный вопрос. Не отсохнут руки у меня в праздники ручками позаполнять данные.

[Администратор]

Я изменил имя IsFirst. Теперь оно учитывает будние и выходные.
Изменил саму формулу.
Листу дал имя "лист", чтобы удобно писать формулы, чтобы в имени было короче.

[вложение удалено администратором]

[Данила]

А праздники на каждый год нужно выписывать отдельно и этот диапазон сверять с заполняемым в столбце Е? Функции специальной нет в эксель на этот случай?

[Данила]

Вот этот параметр за что в формуле отвечает "[м]"?

[Администратор]

Даёт информацию, сколько в дате минут.

[Администратор]

Создал имя Раб_день и внёс изменения в IsFirst.
На лист1 праздники записывайте в умную таблицу - имя использует имя умной таблицы и столбец умной таблицы. При добавлении данных в умную таблицу формула будет видеть добавленные данные.
В столбце C просто вписывайте даты, начиная со строки 2 - столбец C полностью весь будет просматриваться.

[вложение удалено администратором]

[Данила]

Высший пилотаж... Круто... Спасибо... А можете объяснить алгоритм?

У меня в теме:Расчёт выхода на работы с помощью формулы они тоже будут задействованы. Хочу попробовать сам всё сделать, но для этого нужно понимание алгоритма...

[Администратор]

По какому месту из этой формулы у вас вопрос:?
=(ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(И(Раб_день;STime_fact<=--"18:00");--"18:00";STime_fact);"[м]");0) +
ЕСЛИ(IsSecond;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"03:00";1+"03:00";ETime_sm)-ЕСЛИ(STime_fact<=1+"00:00";1+"00:00";STime_fact);"[м]")*1,7;0) +
ЕСЛИ(IsThird;ТЕКСТ(ETime_sm-ЕСЛИ(STime_fact<=1+"03:00";1+"03:00";STime_fact);"[м]")*2,5;0))*4,05

[Данила]

(ЕСЛИ(IsFirst;ТЕКСТ(ЕСЛИ(ETime_sm>=1+"00:00";1+"00:00";ETime_sm)-ЕСЛИ(И(Раб_день;STime_fact<=--"18:00");--"18:00";STime_fact);"[м]");0)

[Данила]

И для чего нужен столбец С в Листе 1?

[Администратор]

На листе 1 в столбец C записывайте рабочие выходные. Это когда происходит перенос праздников.

[Администратор]

Например, какое-то воскресенье может быть рабочим. Функция ЧИСТРАБДНИ не узнает о том, что такое воскресенье рабочий день и посчитает его выходным. Поэтому нужно корректировать, используя список из рабочих выходных.

[Данила]

Я понял. Но я также понял, что не смогу это сделать сам...