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

Обновлено: 21.11.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>

Информационный бюллетень TechRepublic Premium Exclusives

Экономьте время с последними загрузками TechRepublic Premium, включая оригинальные исследования, настраиваемые шаблоны ИТ-политики, готовые презентации для обучения, инструменты для найма ИТ-специалистов, калькуляторы рентабельности инвестиций и многое другое. Эксклюзивно для вас!

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Как вы увидите в следующих разделах, Excel позволяет легко прибавлять или вычитать время.

Добавить время

Предположим, вы хотите узнать, сколько часов и минут потребуется для выполнения двух задач. По вашим оценкам, первая задача займет 6 часов 45 минут, а вторая — 9 часов 30 минут.

Вот один из способов настроить это на листе.

Введите 6:45 в ячейку B2 и введите 9:30 в ячейку B3.

В ячейке B4 введите =B2+B3 и нажмите клавишу ВВОД.

Результат: 16:15 — 16 часов 15 минут — на выполнение двух задач.

Совет. Вы также можете суммировать время, используя функцию автосуммы для суммирования чисел. Выберите ячейку B4, а затем на вкладке Главная выберите Автосумма. Формула будет выглядеть так: =СУММ(B2:B3). Нажмите Enter, чтобы получить тот же результат, 16 часов и 15 минут.

Что ж, это было достаточно просто, но есть дополнительный шаг, если ваши часы в сумме превышают 24. Вам нужно применить специальный формат к результату формулы.

Чтобы суммировать более 24 часов:

В ячейке B2 введите 12:45, а в ячейке B3 введите 15:30.

Введите =B2+B3 в ячейку B4 и нажмите клавишу ВВОД.

Результат — 4:15, что не соответствует ожиданиям. Это связано с тем, что время задачи 2 указано в 24-часовом формате. 15:30 равно 3:30.

Чтобы отобразить время более 24 часов, выберите ячейку B4.

На вкладке "Главная" в группе "Ячейки" выберите "Формат", а затем выберите "Формат ячеек".

В поле "Формат ячеек" выберите "Пользовательский" в списке "Категория".

В поле "Тип" в верхней части списка форматов введите [h]:mm;@ и нажмите кнопку "ОК".

Обратите внимание на двоеточие после [h] и точку с запятой после mm.

Результат: 28 часов 15 минут. Формат появится в списке Тип в следующий раз, когда он вам понадобится.

Вычесть время

Вот еще один пример. Допустим, вы и ваши друзья знаете время начала и окончания волонтерского проекта и хотите знать, сколько времени вы потратили в целом.

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

В ячейке B2 введите время начала и добавьте "a" для AM или "p" для PM. Затем нажмите Enter.

В ячейке C2 введите время окончания, включая "a" или "p" в зависимости от обстоятельств, а затем нажмите клавишу ВВОД.

Введите другое время начала и окончания для ваших друзей, Джой и Лесли.

В ячейке D2 вычтите время окончания из времени начала, введя формулу =C2-B2, а затем нажмите клавишу ВВОД.

В поле "Формат ячеек" выберите "Пользовательский" в списке "Категория".

В списке "Тип" выберите ч:мм (часы и минуты), а затем нажмите кнопку "ОК".

Теперь мы видим, что Ричард работал 3 часа 45 минут.

Чтобы получить результаты для Джой и Лесли, скопируйте формулу, выбрав ячейку D2 и перетащив ее в ячейку D4.

Форматирование в ячейке D2 копируется вместе с формулой.

Чтобы вычесть время, превышающее 24 часа:

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

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

Ссылаясь на приведенный выше пример, выберите ячейку B1 и перетащите ее в ячейку B2, чтобы можно было применить формат к обеим ячейкам одновременно.

В поле "Формат ячеек" выберите "Пользовательский" в списке "Категория".

В поле Тип в верхней части списка форматов введите м/д/гггг ч:мм AM/PM.

Обратите внимание на пустое место в конце гггг и мм.

Новый формат будет доступен, когда он вам понадобится в списке Тип.

В ячейке B1 введите дату начала, включая месяц/день/год и время, используя "a" или "p" для обозначения AM и PM.

В ячейке B2 сделайте то же самое для даты окончания.

В ячейке B3 введите формулу =(B2-B1)*24.

Результат – 31,5 часа.

Примечание. В Excel для Интернета можно складывать и вычитать более 24 часов, но нельзя применять пользовательский числовой формат.

Добавить время

В ячейке B2 введите 6:45, а в ячейке B3 введите 9:30.

Введите =B2+B3 в ячейку B4 и нажмите клавишу ВВОД.

Выполнение двух задач займет 16 часов 15 минут.

Совет. Вы также можете суммировать время с помощью автосуммы. Щелкните в ячейке B4. Затем нажмите Главная > Автосумма. Формула будет выглядеть так: =СУММ(B2:B3). Нажмите Enter, чтобы получить результат, 16 часов и 15 минут.

Вычесть время

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

В ячейке B2 введите время начала, введите пробел, затем введите «a» для AM или «p» для PM и нажмите Enter. В ячейке C2 введите время окончания, включая «a» или «p» в зависимости от ситуации, и нажмите Enter. Введите другое время начала и окончания для ваших друзей Джой и Лесли.

В ячейке D2 вычтите время окончания из времени начала, введя формулу: =C2-B2, а затем нажмите клавишу ВВОД. Теперь мы видим, что Ричард работал 3 часа 45 минут.

Чтобы получить результаты для Джой и Лесли, скопируйте формулу, щелкнув ячейку D2 и перетащив ее в ячейку D4. Форматирование в ячейке D2 копируется вместе с формулой.

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