Загрузите образец книги
В этом руководстве будет показано, как рассчитать отработанное время в Excel и Google Таблицах.
Расчет часов с помощью простой формулы
Мы можем рассчитать отработанные часы, вычтя время окончания из времени начала. Информация должна быть введена в Excel в правильном формате времени, чтобы формула работала.
| 1 | = (D3-C3) * 24 |
Формула D3-C3 даст нам процент отработанного дня, исходя из 24 часов в сутки. Чтобы получить отработанный час, нам нужно умножить это значение на 24.
Дата и время хранятся в Excel в виде чисел. Если бы мы изменили формат столбцов времени начала и окончания на приведенном выше рисунке на общий числовой формат, мы получили бы числовые эквиваленты показанного времени.
Расчет сверхурочных
Мы можем использовать те же формулы для расчета сверхурочных.
В приведенном выше примере стандартное время составляет 8 часов.
Следующая формула вычисляет нормальное время, отработанное в день.
| 1 | = ЕСЛИ ((D3-C3) * 24> $ H $ 13, $ H $ 13, (D3-C3) * 24) |
Если сотрудник проработал более 8 часов, формула вернет максимум 8 часов.
Чтобы рассчитать сверхурочную работу, мы можем получить остаток отработанного времени, используя следующую формулу:
| 1 | = ЕСЛИ ((D3-C3) * 24> $ H $ 13, ((D3-C3) * 24) - $ H $ 13,0) |
MOD Функция
Если время окончания раньше времени начала, это может быть связано с работой ночной смены. Чтобы решить проблему формулы, возвращающей отрицательное число, мы используем функцию МОД.
| 1 | = МОД (D3-C3,1) * 24 |
Как и в случае с простой формулой, нам нужно умножить значение, вычисленное функцией MOD, на 24, чтобы получить количество отработанных часов.
Расчет рабочего времени в недельном расписании
Мы можем использовать функции ЕСЛИ, СУММ и МАКС для расчета отработанных часов за неделю с разбивкой по обычному времени и сверхурочным часам.
Рассчитать сверхурочные
Сверхурочная работа рассчитывается, если человек работает более 40 часов в неделю.
| 1 | = ЕСЛИ (СУММ ($ E $ 4: E4)> 40; СУММ ($ E $ 4: E4) -40,0) |
Первая часть первого диапазона функции СУММ является абсолютной, а вторая часть - нет. Когда вы копируете эту формулу в нижнюю часть таблицы, вы заметите, что функция СУММ суммирует все отработанные часы в столбце E. По мере увеличения диапазона СУММ увеличивается количество отработанных часов. Однажды СУММ превышает 40 часов, сверхурочные часы помещаются в столбец Сверхурочные как увеличивающаяся сумма.
Рассчитать обычные часы
Обычные часы рассчитываются на основе общего количества часов и сверхурочной работы.
| 1 | = МАКС (E4-G4,0) |
Мы используем функцию MAX, чтобы не получить отрицательные часы, когда сотрудник работал сверхурочно, как если бы результат был отрицательным, тогда функция MAX вернет ноль.
Расчет рабочего времени в недельном расписании по проектам
Продвигая вышеприведенный расчет еще на один шаг, мы можем разделить часы, отработанные в проекте, над которыми работал сотрудник, с помощью функции СУММЕСЛИ.
| 1 | = СУММЕСЛИ ($ H $ 4: $ H $ 10; H4; $ F $ 4: $ F $ 10) |
Функция СУММЕСЛИ суммирует Обычные часы в столбце F в соответствии с критериями, выбранными в столбце H - в случае приведенной выше формулы она будет искать проект ABC и суммировать Обычные часы для этого проекта.
Расчет часов работы в Google Таблицах
Все приведенные выше примеры Excel работают в Google Таблицах одинаково.
