Как рассчитать чистые рабочие часы между двумя датами в Excel, исключая выходные и праздничные дни?
Во многих компаниях сотрудникам оплата начисляется за отработанные часы. Рассчитать чистые рабочие часы за один день несложно, но как быть с расчётом таких часов в течение диапазона дат? В этой статье приведены формулы для расчёта чистых рабочих часов между двумя датами в Excel с исключением выходных и праздничных дней.
Расчёт рабочих дней без учёта выходных
Расчёт рабочих часов без учёта выходных и праздников
Расчёт рабочих дней без учёта выходных
В этом разделе приведена формула для расчёта количества рабочих дней между двумя датами и временем с исключением выходных.
1. Выделите две ячейки, в которые будете вводить начальную дату и конечную дату с временем, щёлкните правой кнопкой мыши и выберите Установить формат ячейки из контекстного меню. См. снимок экрана:
2. В диалоговом окне Установить формат ячейки перейдите на вкладку Число, выберите Пользовательский в списке Числовые форматы, а затем введите д/м/гггг ч:мм в поле Тип в правой части окна. См. снимок экрана:
3. Нажмите ОК. Затем введите начальную дату начала и конечные дату и время в только что выделенные ячейки — каждое значение отдельно. См. снимок экрана:
4. В ячейке рядом с этими двумя (например, C13) введите следующую формулу: =NETWORKDAYS(A13,B13)-1-MOD(A13,1)+MOD(B13,1), нажмите клавишу Enter — и результат появится в пользовательском формате. Выделите ячейку с результатом, перейдите на вкладку Главная и в списке Числовые форматы выберите «Общий», чтобы корректно отформатировать значение. См. снимок экрана:
Раскройте магию Excel с помощью KUTOOLS AI
- Интеллектуальное выполнение: Выполняйте операции с ячейками, анализируйте данные и создавайте диаграммы — всё это доступно через простые команды.
- Пользовательские формулы: создавайте индивидуальные формулы для оптимизации рабочих процессов.
- Программирование на VBA: Пишите и внедряйте код VBA легко и без усилий.
- Анализ формул: Легко разбирайтесь даже в самых сложных формулах.
- Перевод текста: Ломайте языковые барьеры прямо в ваших таблицах.
Расчёт рабочих часов без учёта выходных и праздников
Если необходимо рассчитать чистые рабочие часы, исключая выходные или праздничные дни, выполните следующие действия:
Расчёт чистых рабочих часов без учёта выходных
1. Выделите две ячейки, задайте им пользовательский формат «д/м/гггг ч:мм» и введите начальную дату и конечные дату с временем. См. снимок экрана:
2. В соседнюю ячейку (например, C2) введите следующую формулу:
=(NETWORKDAYS(A2,B2)-1)*("17:30"-"8:30")+IF(NETWORKDAYS(B2,B2),MEDIAN(MOD(B2,1),"17:30","8:30"),"17:30")-MEDIAN(NETWORKDAYS(A2,A2)*MOD(A2,1),"17:30","8:30"),
затем нажмите клавишу Enter. В результате отобразится значение в виде числа. См. снимок экрана:
3. Щёлкните правой кнопкой мыши по полученной числовой строке и выберите в контекстном меню пункт Установить формат ячейки. В диалоговом окне Установить формат ячейки перейдите на вкладку Пользовательский в списке Числовые форматы и введите в поле Тип следующий код: [ч]:мм. См. снимок экрана:
4. Нажмите ОК. Теперь чистые рабочие часы между двумя датами без учёта выходных рассчитаны.
Совет: в формуле A2 — это дата начала, B2 — дата и время окончания, а 8:30 и 17:30 — стандартное начало и окончание рабочего дня. Вы можете изменить эти значения по своему усмотрению.
Расчёт чистых рабочих часов с исключением выходных и праздничных дней
1. Как и выше, выделите две ячейки, задайте им пользовательский формат д/м/гггг ч:мм, а затем введите начальную дату и конечные дату с временем.
2. Выделите пустую ячейку и введите даты праздников. В данном примере указано три праздничных дня, размещённых по отдельности в диапазоне H1:H3. См. снимок экрана:
3. Выделите пустую ячейку для итогового результата (например, C2),
=(NETWORKDAYS.INTL(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+IF(NETWORKDAYS.INTL(B2,B2,11,H$1:H$3),MEDIAN(MOD(B2,1),"8:30","17:30"),"17:30")-MEDIAN(NETWORKDAYS.INTL(A2,A2,11,H$1:H$3)*MOD(A2,1),"8:30","17:30"),
затем нажмите клавишу Enter — в ячейке появится числовое значение. Примените к нему пользовательский формат [ч]:мм. См. снимок экрана:
Совет: в формуле A2 — это дата начала, B2 — дата и время окончания, 8:30 и 17:30 — стандартное начало и окончание рабочего дня, а H1:H3 — ячейки с праздничными днями. Вы можете изменить эти значения по своему усмотрению.
Легко добавляйте дни, годы, месяцы, часы, минуты и секунды к датам в Excel |
Если в ячейке указана дата, и вам нужно добавить дни, годы, месяцы, часы, минуты или секунды, использование формул может быть сложным и трудно запоминающимся. С помощью Kutools для Excel’s Помощник по дате и временивы без усилий добавите Единица времени к дате, рассчитаете разницу между датами или даже определите возраст человека по дате его рождения — всё это без необходимости запоминать сложные формулы. |
Kutools для Excel— расширьте возможности Excel более чем 300 необходимыми инструментами, чтобы работать быстрее и проще, а также используйте функции ИИ для более умной обработки данных и повышения продуктивности.Получить сейчас |
Лучшие инструменты повышения продуктивности в Office
Раскройте весь потенциал Excel с помощью Kutools для Excel и ощутите эффективность как никогда раньше.Kutools для Excel предлагает более 300 расширенных функций для повышения продуктивности и Экономия времени.Нажмите здесь, чтобы получить нужную Вам функцию…
Office Tab добавляет в Office вкладки и значительно упрощает Вашу работу
- Включите редактирование и чтение во вкладках в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна — вместо того чтобы использовать отдельные окна.
- Повышает вашу продуктивность на 50 % и экономит сотни кликов мышью каждый день!
Все надстройки Kutools — один установщик
Kutools for Office — набор надстроек для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro, идеально подходящий командам, работающим с разными приложениями Office.
- Единый комплект— надстройки для Excel, Word, Outlook и PowerPoint + Office Tab Pro
- Один установщик, одна лицензия— настройка занимает считанные минуты (готово для MSI)
- Лучше работать вместе— оптимизированная продуктивность во всех приложениях Office
- 30-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек