Как рассчитать рабочее время в Excel

Обновлено: 05.07.2024

С помощью Microsoft Excel вы можете создать рабочий лист, в котором подсчитываются часы, отработанные за любую смену. Следуйте этим пошаговым инструкциям.

Чтобы рассчитать в Excel, сколько часов человек отработал, часто можно вычесть время начала из времени окончания, чтобы получить разницу. Но если рабочая смена длится полдень или полночь, простое вычитание не поможет.

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

Выполните следующие действия:

В поле A1 введите Время начала.
В поле B1 введите Тайм-аут.
В поле C1 введите Отработанные часы.
Выберите A2 и B2 и нажмите [Ctrl]1, чтобы открыть диалоговое окно «Формат ячеек».
На вкладке "Число" выберите "Время" в списке "Категория", выберите "13:30" в списке "Тип" и нажмите "ОК".
Нажмите правой кнопкой мыши на ячейку C2 и выберите "Формат ячеек".
На вкладке "Число" выберите "Время" в списке "Категория", выберите "13:30" в списке "Тип" и нажмите "ОК".
В ячейке C2 введите следующую формулу:

Если вы введете 23:00 в качестве времени начала и 7:00 утра в качестве времени окончания, Excel отобразит 8, правильное количество отработанных часов.

Бонусный совет по Microsoft Excel

Из статьи Сьюзен Харкинс "10 вещей, которые никогда не следует делать в Excel":

Используйте несколько ссылок. Ссылки между двумя книгами распространены и полезны. Но множественные ссылки, в которых значения в workbook1 зависят от значений в workbook2, которые ссылаются на workbook3 и т. д., трудноуправляемы и нестабильны. Пользователи забывают закрывать файлы, а иногда даже перемещают их. Если вы единственный человек, работающий с этими связанными книгами, у вас может не возникнуть проблем, но если другие пользователи просматривают и изменяют их, вы напрашиваетесь на неприятности. Если вам действительно нужно столько ссылок, вы можете подумать о новом дизайне.

Этот бонусный совет по Excel также доступен в бесплатном PDF-файле «30 вещей, которые вы никогда не должны делать в Microsoft Office».

Примечание редактора от 11 марта 2019 г. Впервые эта статья в формате Excel была опубликована в июне 2005 года. С тех пор мы добавили видеоруководство, дополнительный совет и обновили соответствующие ресурсы.< /p>

Чтобы рассчитать общее количество рабочих часов между двумя датами и временем, можно использовать формулу, основанную на функции ЧИСТРАБДНИ. В показанном примере E5 содержит следующую формулу:

где «нижний» — это именованный диапазон H5, а «верхний» — именованный диапазон H6.

Примечание. Этот пример был навеян задачей с формулой на Chandoo, а более полное решение предоставлено специалистом по формулам Барри Гудини на форуме MrExcel.

Эта формула вычисляет общее количество рабочих часов между двумя датами и временем, находящимися между "нижним" и "верхним" временем. В показанном примере нижнее время — 9:00, а верхнее — 17:00. Они отображаются в формуле как именованные диапазоны «нижний» и «верхний».

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

Функция ЧИСТРАБДНИ обрабатывает исключение выходных и праздничных дней (если они указаны в виде диапазона дат). Вы можете переключиться на ЧИСТРАБДНИ.МЕЖД, если в вашем расписании есть нестандартные рабочие дни.

Форматирование вывода

Результатом является число, представляющее общее количество часов. Как и во всех временах Excel, вам нужно будет отформатировать вывод в подходящем числовом формате. В показанном примере мы используем:

Квадратные скобки не позволяют Excel прокручиваться, когда часы больше 24. Другими словами, они позволяют отображать часы больше 24. Если вам нужно десятичное значение для часов, вы можете умножить результат на 24 и форматировать как обычное число.

Простая версия

Если время начала и окончания всегда находится между нижним и верхним временем, вы можете использовать более простую версию этой формулы:

Нет времени начала и окончания

Чтобы рассчитать общее количество рабочих часов между двумя датами, предполагая, что все дни являются полными рабочими днями, вы можете использовать еще более простую формулу:

Чтобы рассчитать общее количество рабочих часов между двумя датами, можно использовать формулу, основанную на функции ЧИСТРАБДНИ, где "начало" — это дата начала, "окончание" — это дата окончания, "праздники" — это диапазон, который включает даты, а «часы» — количество рабочих часов в рабочем дне. В показанном примере формула в D7 выглядит так:

где "праздники" — это именованный диапазон G6:G8.

Эта формула использует функцию ЧИСТРАБДНИ для расчета общего количества рабочих дней между двумя датами с учетом выходных и (необязательно) праздничных дней. Праздничные дни, если они указаны, должны быть диапазоном допустимых дат Excel. Когда известно общее количество рабочих дней, их просто умножают на фиксированное количество часов в день, 8 в показанном примере.

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

В этом примере первые две формулы используют функцию ЧИСТРАБДНИ.

Если ваша рабочая неделя включает дни, отличные от понедельника по пятницу, вы можете переключиться на функцию ЧИСТРАБДНИ.МЕЖД, которая предоставляет аргумент "выходные", который можно использовать для определения того, какие дни недели являются рабочими, а какие - выходными. NETWORKDAYS.INTL можно настроить так же, как и NETWORKDAYS, но он предоставляет дополнительный аргумент, называемый "выходные", для управления тем, какие дни недели считаются рабочими.

Следующие 4 формулы используют функцию ЧИСТРАБДНИ.МЕЖД:

Нажмите на названия функций выше, чтобы узнать больше о параметрах конфигурации.

Индивидуальный график работы

Эта формула предполагает, что все рабочие дни имеют одинаковое количество рабочих часов. Если вам нужно рассчитать рабочее время с помощью пользовательского графика, в котором рабочие часы различаются в зависимости от дня недели, вы можете попробовать следующую формулу:

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

См. видеоруководство и расшифровку ниже:

Подготовленная нами таблица содержит время, когда сотрудник начал и закончил работу. Отработанные часы можно легко рассчитать как разницу во времени — просто возьмите более позднее время и вычтите более раннее.

Не забывайте об одном правиле.

Если Excel должен точно вычитать время и отображать правильный формат времени, ячейки должны быть правильно отформатированы до того, как будет выполнен расчет. Форматирование ячеек в этой таблице было задано в предыдущем уроке. Если вы хотите узнать больше о том, как установить правильное форматирование, посмотрите наше видео о том, как вставлять и форматировать время в Excel.

Если ячейки отформатированы должным образом, щелкните ячейку, в которой вы хотите отобразить результат. В данном случае это ячейка E3.

Введите знак "равно" и нажмите на ячейку, содержащую более позднее значение времени, которое здесь означает 16:30. Поскольку мы вычитаем, вставьте минус и теперь просто добавьте время, зарегистрированное в разделе «Начать работу».

Нажмите «Ввод», и мы получим результат! В понедельник сотрудник проработал в общей сложности девять часов тридцать минут.

Как подсчитать количество отработанных часов в Excel (вся неделя в минуту)

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

Вот оно! Хорошая работа!

Проведен простой подсчет отработанных часов для каждого дня недели!

Но будьте осторожны!

Как видите, в результате за пятницу отображается только набор решеток. Это связано с тем, что время, когда сотрудник начал и закончил работу, приходится на полночь. Рабочее время начиналось в семь часов вечера и заканчивалось на следующее утро в шесть часов.

Чтобы узнать, как поступить в такой ситуации, посмотрите следующий видеоурок EasyClick Academy о том, как рассчитать разницу во времени между двумя периодами времени, прошедшими полночь.

Не упустите прекрасную возможность узнать:

Если вы нашли это руководство полезным, поставьте нам лайк и подпишитесь, чтобы получать больше видео от EasyClick Academy. Ознакомьтесь с другими руководствами, которые помогут вам быстро и легко использовать Excel!

Читайте также: