© Сорокин Б.Б. 2016 г.

Табель учета рабочего времени

"Добрый день. Есть время работы в месяц, и по этому календарю надо сделать табель учета рабочего времени, так как не все сотрудники работают одинаковое время, допустим Сидоров в понедельник отработал 6 часов, а Иванов 8 часов, вот и надо чтобы не считать на калькуляторе время, а просто ввел часы за месяц и табель уже раскидал время."

К письму прилагается табличка с табелем учета рабочего времени, фрагмент которой прилагаю.

табель учета рабочего времени табель учета рабочего времени

Сразу видно, что табличка отражает не столько учет рабочего времени, сколько график работы. Табличка несомненно удобна для пользователя т.к. сразу видно сколько работников в одно время работают, что несомненно важно для организации работы, а вот считать рабочие часы в таком виде действительно неудобно из-за самого вида и текстового формата значений.

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

Первая задача преобразовать значения типа "8\17" в число отработанных часов. Кроме этого 17-8=9, а из таблички видно, что рабочий день 8-часовой, значит сюда заложен еще и перерыв на обед - 1 час. А если работа с "8/11" 11-8=3, то перерыва на обед нет. С учетом дополнительных условий, задачу можно решить с помощью следующей формулы.

формула

Общий смысл этой формулы заключается в том, что сначала находится и преобразуется в цифру время конца рабочего дня, далее - начало рабочего дня и берется между ними разница. Кроме этого проверяются логические условия на заполненность ячейки и длительность рабочего дня для определения наличия "обеда". И так, время конца рабочего дня возвращает фрагмент формулы.

формула

Опорой для этого служит знак "\", который разделяет значения начала и конца рабочего дня. Далее аналогично находится время начала рабочего дня и берется между ними разница.

формула

Учитывая, что функция НАЙТИ("\";B7;1) возвращает ошибку "#ЗНАЧ!" для пустых ячеек таблицы, т.к. не находит опорный знак "\", в начало формулы добавляем условие, которое позволяет возвращать "0" если ячейка таблички вообще не заполнена.

=ЕСЛИ(ЕОШ(НАЙТИ("\";B7;1));0;

Теперь осталось разобраться с перерывом на обед. Условие такое, если рабочий день длится больше 4 часов, то перерыв есть и 1 час надо вычитать из общего рабочего времени, в противном случае перерыва нет. Эту задачу решает следующий фрагмент формулы.

формула

Полученную формулу копируем в дополнительные расчетные ячейки.

рабочие часы

Теперь осталось разобраться с графой "Ночь", т.е. с учетом ночных часов работы, видимо они оплачиваются отдельно. Дело хорошее, строго соответствует КЗОТ, поэтому надо что то придумывать. Условие такое: ночные часы считаются с 22.00 и рабочий день заканчивается в 24.00. За основу возьмем выше приведенную формулу.

формула

Фрагмент формулы

формула

проверяет оканчивается ли рабочий день в ночные часы, т.е. после 22.00.

Фрагмент формулы

формула

проверяет начинается ли рабочий день в ночные часы, т.е. после 22.00. В зависимости от результата проверки указанные фрагменты возвращают либо "0" либо часы начала и конца работы в ночное время. Далее берется разница этих значений.

формула

Полученную формулу копируем в дополнительные расчетные ячейки.

ночные часы

Далее осталось только просуммировать часы работы за месяц и ночные часы. Это уже совсем просто.

часы работы в месяц

Ограничения к применению.

1. В табеле не менять стиль заполнения рабочего времени, особенно это касается знака "\" по нему идет поиск и преобразование текстовых значений часов в числовые. Часы могут заполняться двух или одно-значными цифрами.

2. Формулы рассчитаны на приведенный график работы т.е. рабочий день с 8.00 до 24.00, после 22.00 считается ночное время, если график выйдет за указанные значения возможны ошибки.

Архив с файлом примера можно скачать.