Добрый день. Маленькая жизненная статья о рабочих делах.
Столкнулся в работе с микро задачей, сделать в гугл таблице автоматический подсчет времени работы сотрудников без привязки к дате (это важно) с возможностью выбора дополнительных условий. Все что ниже смело применимо для расчета продолжительности пути в часах, продолжительности чего угодно если оно может длиться больше суток, но не дольше 2х.
Итак у нас есть время начала работы и время завершения. А так же есть день недели во всплывающем флажке. В зависимости от дня недели меняется расчет.
В конце получаем количество часов в виде числа.
Формула:
=IFS(F4="суббота";IFS(G4+H4<24;ЕСЛИ(G4>H4;24+(ЧАС(H4)+МИНУТЫ(H4)/60)-(ЧАС(G4)+МИНУТЫ(G4)/60);ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60));G4+H4>24;ЕСЛИ(G4>H4;(24-(ЧАС(G4+H4)+(МИНУТЫ(G4+H4)/60)));ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)));F4="пятница";IFS(G4+H4<24;ЕСЛИ(G4>H4;(24+(ЧАС(H4)+МИНУТЫ(H4)/60)-(ЧАС(G4)+МИНУТЫ(G4)/60))-8;ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8);G4+H4>24;ЕСЛИ(G4>H4;(24-(ЧАС(G4+H4)+(МИНУТЫ(G4+H4)/60)))-8;(ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8)));F4="воскресенье";IFS(G4+H4<24;ЕСЛИ(G4>H4;24+(ЧАС(H4)+МИНУТЫ(H4)/60)-(ЧАС(G4)+МИНУТЫ(G4)/60);ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60));G4+H4>24;ЕСЛИ(G4>H4;(24-(ЧАС(G4+H4)+(МИНУТЫ(G4+H4)/60)));ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)));F4="четверг";IFS(G4+H4<24;ЕСЛИ(G4>H4;(24+(ЧАС(H4)+МИНУТЫ(H4)/60)-(ЧАС(G4)+МИНУТЫ(G4)/60))-8;ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8);G4+H4>24;ЕСЛИ(G4>H4;(24-(ЧАС(G4+H4)+(МИНУТЫ(G4+H4)/60)))-8;(ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8)));F4="среда";IFS(G4+H4<24;ЕСЛИ(G4>H4;(24+(ЧАС(H4)+МИНУТЫ(H4)/60)-(ЧАС(G4)+МИНУТЫ(G4)/60))-8;ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8);G4+H4>24;ЕСЛИ(G4>H4;(24-(ЧАС(G4+H4)+(МИНУТЫ(G4+H4)/60)))-8;(ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8)));F4="вторник";IFS(G4+H4<24;ЕСЛИ(G4>H4;(24+(ЧАС(H4)+МИНУТЫ(H4)/60)-(ЧАС(G4)+МИНУТЫ(G4)/60))-8;ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8);G4+H4>24;ЕСЛИ(G4>H4;(24-(ЧАС(G4+H4)+(МИНУТЫ(G4+H4)/60)))-8;(ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8)));F4="понедельник";IFS(G4+H4<24;ЕСЛИ(G4>H4;(24+(ЧАС(H4)+МИНУТЫ(H4)/60)-(ЧАС(G4)+МИНУТЫ(G4)/60))-8;ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8);G4+H4>24;ЕСЛИ(G4>H4;(24-(ЧАС(G4+H4)+(МИНУТЫ(G4+H4)/60)))-8;(ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8)));F4="праздничный день/выходной с отгулом";IFS(G4+H4<24;ЕСЛИ(G4>H4;24+(ЧАС(H4)+МИНУТЫ(H4)/60)-(ЧАС(G4)+МИНУТЫ(G4)/60);ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60));G4+H4>24;ЕСЛИ(G4>H4;(24-(ЧАС(G4+H4)+(МИНУТЫ(G4+H4)/60)));ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60))))
БЕЗ ПАНИКИ! Она только выглядит страшно, тут все просто!
Теперь разберемся с этой красоткой.
Соответственно для ваших целей в флажке может быть любое буквенное или числовое обозначение, но не забудьте внести его строго так же в кавычки в формуле, одна заглавная буква и формула перестает корректно работать.
Формула написана через функцию (IFS). Расшифровывается это так: Если выбрано одно из нескольких значений, например "суббота" считать так( все что в скобке). Сразу в скобке стоит еще один оператор "IFS" сравнивающий время начала работы и конец(B>C,B<C,B+C>24). Это нужно для того чтобы формула корректно считала случаи когда начало работы поздно вечером, а завершение утром следующего дня. Так как гугл без привязки к дате не может дать сумму времени больше 24 часов( так же не может дать 24 часа, автоматически заменяется на 0). После сравнения в зависимости от итогов получается простая математическая операция сложения или альтернативная с использованием 24 (не забываем что изначальное условие не привязывать данные к дате, поэтому пришлось похитрить и добавить простой автоматический анализ вводимых данных). Время переводится в число и совершается операция. Обратите внимание, что в указанной формуле в будние дни из результата вычитается 8 часов, а в выходные нет: (ЧАС(C2-B2)+(МИНУТЫ(C2-B2)/60)-8))))
перенося формулу себе, не забудьте убрать -8 для корректных подсчетов из всех будних дней.
Закрепим. Чем меньше вариантов условий, тем меньше формула. Это не она большая, это дней недели много.
Вот пример на два условия(два дня недели или обозначение будний/выходной день):
IFS(F4="суббота";IFS(G4+H4<24;ЕСЛИ(G4>H4;24+(ЧАС(H4)+МИНУТЫ(H4)/60)-(ЧАС(G4)+МИНУТЫ(G4)/60);ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60));G4+H4>24;ЕСЛИ(G4>H4;(24-(ЧАС(G4+H4)+(МИНУТЫ(G4+H4)/60)));ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)));F4="пятница";IFS(G4+H4<24;ЕСЛИ(G4>H4;(24+(ЧАС(H4)+МИНУТЫ(H4)/60)-(ЧАС(G4)+МИНУТЫ(G4)/60))-8;ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8);G4+H4>24;ЕСЛИ(G4>H4;(24-(ЧАС(G4+H4)+(МИНУТЫ(G4+H4)/60)))-8;(ЧАС(H4-G4)+(МИНУТЫ(H4-G4)/60)-8))))
В разы меньше же! Только проверьте количество скобок в конце.
Первый оператор IFS смотрит что из множества значений стоит в ячейке с флажком сейчас и выполняет соответствующую операцию.
Второй оператор IFS выполняет элементарный анализ введенных значений. А именно смотрит: "Начало работы больше или меньше конца работы? Сумма начала и конца больше 24 часов?". И так далее.
Что там за часы и минуты в формуле? Отвечаю! Это ВЕЩЬ функция перевода времени в число: (ЧАС(H4)+МИНУТЫ(H4)/60)+(ЧАС(G4)+МИНУТЫ(G4)/60). Так как вводится именно время. То для математических операций удобнее перевести его в число. ЧАС - переводит значение в часах, в число часов. МИНУТЫ - переводит значение в минутах в число минут, то есть в числа после запятой в нашем случае. Так же если очень нужно можно добавить секунды, но это давайте сами.
Как видите ничего сложного.
