Формулы Excel для вычисления, сложения и вычитания времени в табеле
Бухгалтеры и кадровики часто сталкиваются с тем, что Excel некорректно считает отработанные часы или выдает ошибку при попытке вычесть обеденный перерыв. Обычное суммирование времени за месяц сбивается, если итоговое значение превышает 24 часа, а стандартные знаки арифметики не всегда срабатывают с форматом времени.
Правильная excel формула времени помогает автоматизировать заполнение табеля учета рабочего времени и избежать ручных ошибок. Рассмотрим, как быстро складывать, вычитать и вычислять часы для каждого сотрудника.
Что это и зачем
Excel формула времени — это комбинация функций и математических операторов для работы с часами и минутами. В табеле учета рабочего времени она нужна для расчета смен, вычета перерывов на обед, подсчета сверхурочных и суммирования общего количества часов за месяц по каждому сотруднику.
Как сделать пошагово
- Установите для ячеек со временем правильный формат: выделите диапазон, нажмите Ctrl+1 и выберите тип «Время» (ч:мм) или пользовательский формат [ч]:мм для итогов больше 24 часов.
- Введите время начала смены в ячейку A2 (например, 09:00), а время окончания в B2 (18:00).
- Для вычисления отработанного времени примените формулу вычитания:
=B2-A2. - Чтобы вычесть 1 час обеда, допишите формулу:
=B2-A2-"1:00". - Для расчета месячного итога используйте функцию суммирования
=СУММ(C2:C31), предварительно задав формат [ч]:мм.
Пример / расчёт
| Сотрудник | Приход | Уход | Обед | Итого за день |
|---|---|---|---|---|
| Иванов И.И. | 09:00 | 18:00 | 1:00 | 08:00 |
| Петров П.П. | 08:30 | 17:30 | 0:45 | 08:15 |
Мой опыт
За годы работы с табелями я понял главное: Excel воспринимает время как доли суток. Единица — это полные сутки (24 часа), поэтому 12 часов для программы равны 0.5. Как только я это осознал, любые ошибки в формулах с прибавлением и вычитанием часов исчезли навсегда.
Ошибки и нюансы
- Появление решеток (#####) вместо результата означает, что ячейка слишком узкая или получилось отрицательное время.
- Сумма часов за месяц сбрасывается, если забыть применить квадратные скобки в пользовательском формате [ч]:мм.
- Нельзя просто так отнимать текст вместо числового времени, ячейки должны быть настроены строго в формате времени.
FAQ
Почему Excel выдает отрицательное время при вычитании?
Excel по умолчанию не поддерживает отрицательное время и показывает символы #ОШИБКА! или #####. Убедитесь, что время окончания больше времени начала, или настройте систему дат 1904 в параметрах книги.
Как сложить больше 24 часов в табеле?
Необходимо изменить числовой формат ячейки с итоговой суммой на пользовательский, указав маску [ч]:мм вместо стандартной.
Как учесть ночные смены в формуле?
Если смена переходит через полночь (например, с 22:00 до 06:00), формула выглядит так: =КОСТЫЛЬ(B2-A2+(B2=МОД(B2-A2; 1).
Скачать шаблон
Чтобы не настраивать формулы вручную, воспользуйтесь готовым решением. Скачайте настроенный Шаблон «Табель учёта рабочего времени» с уже работающими формулами расчета часов.
Шаблон «Табель учёта рабочего времени»