Note: The other languages of the website are Google-translated. Back to English

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

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

Рассчитать рабочий день без выходных

Расчет рабочего времени без учета выходных / праздников


стрелка синий правый пузырь Рассчитать рабочий день без выходных

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

1. Выберите две ячейки, в которые вы будете вводить время даты начала и время даты окончания, и щелкните правой кнопкой мыши, чтобы выбрать Формат ячеек сформировать контекстное меню. Смотрите скриншот:
док нетто время работы 1

2. в Формат ячеек диалоговое окно, нажмите Число Вкладка и выберите На заказ сформировать Категория список и введите м / д / гггг ч: мм в Тип текстовое поле в правом разделе. Смотрите скриншот:
док нетто время работы 2

3. Нажмите OK. И введите время даты начала и время окончания в две ячейки по отдельности. Смотрите скриншот:
док нетто время работы 3

4. В ячейке рядом с этими двумя ячейками, например, C13, введите эту формулу =NETWORKDAYS(A13,B13)-1-MOD(A13,1)+MOD(B13,1), и нажмите Enter ключ, и вы получите результат в настраиваемом формате, выберите ячейку результата и нажмите Главная вкладку и перейдите в Формат номера список, чтобы выбрать Общие, чтобы отформатировать его как правильный формат. Смотрите скриншот:
док нетто время работы 4


стрелка синий правый пузырь Расчет рабочего времени без учета выходных / праздников

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

Расчет рабочих часов без учета выходных

1. Выберите две ячейки и отформатируйте их как пользовательский формат m / d / yyyy h: mm, а затем введите время даты начала и время даты окончания. Смотрите скриншот:
док нетто время работы 5

док нетто время работы 6

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 key, то вы получите числовую строку. Смотрите скриншот:
док нетто время работы 7

3. Щелкните правой кнопкой мыши числовую строку и щелкните Формат ячеек из контекстного меню и в Формат ячеек диалоговое окно, выберите На заказ форма Категория список под Number и введите это [Н]: мм в текстовое поле Тип. Смотрите скриншот:
док нетто время работы 8

4. Нажмите OK. Теперь подсчитывается чистое рабочее время между двумя датами, исключая выходные.
док нетто время работы 9

Функции: В формуле A2 - время даты начала, B2 - время даты окончания, 8:30 и 17:30 - общее время начала и время окончания каждого дня, вы можете изменить их по своему усмотрению.

Расчет рабочих часов без учета выходных и праздничных дней

1. Как и выше, выберите две ячейки и отформатируйте их как пользовательский формат. м / д / гггг ч: мм, и введите время даты начала и время даты окончания.
док нетто время работы 10

2. Выделите пустую ячейку и введите в нее дату праздника. Здесь у меня 3 праздника, и я ввожу их отдельно в H1: H3. Смотрите скриншот:
док нетто время работы 11

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 key, вы получите числовую строку и отформатируете ее как пользовательский формат [Н]: мм. Смотрите скриншот:
док нетто время работы 12

Функции: В формуле A2 - это время даты начала, B2 - время даты окончания, 8:30 и 17:30 - общее время начала и время окончания каждого дня, H1: H3 - ячейки выходных, вы можете их изменить. как вам нужно.

Легко добавлять дни / годы / месяц / часы / минуты / секунды к дате и времени в Excel

Предположим, у вас есть данные о формате даты и времени в ячейке, и теперь вам нужно добавить к этой дате количество дней, лет, месяцев, часов, минут или секунд. Обычно использование формул является первым методом для всех пользователей Excel, но запомнить все формулы сложно. С участием Kutools for ExcelАвтора Помощник по дате и времени вы можете легко добавить дни, годы, месяцы или часы, минуты или секунды к дате и времени, кроме того, вы можете вычислить разницу дат или возраст на основе данного дня рождения, вообще не запоминая формулу. Нажмите, чтобы получить полнофункциональную бесплатную пробную версию в 30 дней!
док добавить час минута секунда
 
Kutools for Excel: с более чем удобными надстройками Excel 300, которые можно попробовать бесплатно без ограничений в 30 дней.

Лучшие инструменты для работы в офисе

Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%

  • Снова использовать: Быстро вставить сложные формулы, диаграммы и все, что вы использовали раньше; Зашифровать ячейки с паролем; Создать список рассылки и отправлять электронные письма ...
  • Бар Супер Формулы (легко редактировать несколько строк текста и формул); Макет для чтения (легко читать и редактировать большое количество ячеек); Вставить в отфильтрованный диапазон...
  • Объединить ячейки / строки / столбцы без потери данных; Разделить содержимое ячеек; Объединить повторяющиеся строки / столбцы... Предотвращение дублирования ячеек; Сравнить диапазоны...
  • Выберите Дубликат или Уникальный Ряды; Выбрать пустые строки (все ячейки пустые); Супер находка и нечеткая находка во многих рабочих тетрадях; Случайный выбор ...
  • Точная копия Несколько ячеек без изменения ссылки на формулу; Автоматическое создание ссылок на несколько листов; Вставить пули, Флажки и многое другое ...
  • Извлечь текст, Добавить текст, Удалить по позиции, Удалить пробел; Создание и печать промежуточных итогов по страницам; Преобразование содержимого ячеек в комментарии...
  • Суперфильтр (сохранять и применять схемы фильтров к другим листам); Расширенная сортировка по месяцам / неделям / дням, периодичности и др .; Специальный фильтр жирным, курсивом ...
  • Комбинируйте книги и рабочие листы; Объединить таблицы на основе ключевых столбцов; Разделить данные на несколько листов; Пакетное преобразование xls, xlsx и PDF...
  • Более 300 мощных функций. Поддерживает Office/Excel 2007-2021 и 365. Поддерживает все языки. Простое развертывание на вашем предприятии или в организации. Полнофункциональная 30-дневная бесплатная пробная версия. 60-дневная гарантия возврата денег.
вкладка kte 201905

Вкладка Office: интерфейс с вкладками в Office и упрощение работы

  • Включение редактирования и чтения с вкладками в Word, Excel, PowerPoint, Издатель, доступ, Visio и проект.
  • Открывайте и создавайте несколько документов на новых вкладках одного окна, а не в новых окнах.
  • Повышает вашу продуктивность на 50% и сокращает количество щелчков мышью на сотни каждый день!
офисный дно
Сортировать комментарии по
Комментарии (61)
Оценок пока нет. Оцените первым!
Этот комментарий был сведен к минимуму модератором на сайте
У меня есть несколько дат, которые мне нужно использовать для расчета часов, потраченных на итерацию. A - полученный запрос: 1 14:17 B - запрос завершен: 3 43:1 C - отправлено разъяснение: 16. /17 23:03 D - получено разъяснение : 1 16:17 Мне нужно найти разницу BA = E и затем DC = F теперь EF должен дать мне нет. часов тратится на эту работу, которая должна быть менее 20 часов
Этот комментарий был сведен к минимуму модератором на сайте
Действительно хорошая информация, но можно ли ее преобразовать в минуты? Спасибо, Стив.
Этот комментарий был сведен к минимуму модератором на сайте
да, отформатируйте ячейку результата в пользовательском формате [мм] вместо [ч]:мм
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, мне нужна помощь по той же теме. В моем случае я определил дату и время начала (дд.мм.гг и чч.мм), и у меня есть часы, необходимые для производства некоторого материала (всего 17 часов). Моя проблема в том, как вычтите нерабочие часы из общего времени, необходимого третья смена не работает с 0:00 до 7:00, пожалуйста, помогите
Этот комментарий был сведен к минимуму модератором на сайте
Привет. Работает ли эта формула в Excel 2007? Потому что я пытался использовать формулу для расчета чистого рабочего времени, исключая выходные и праздничные дни, но она не работает. Я получаю "#ИМЯ?" как вывод. Я использую его для расчета с 9:6 до 18:00 (XNUMX:XNUMX).
Этот комментарий был сведен к минимуму модератором на сайте
Привет. Работает ли эта формула в Excel 2007? Потому что я пытался использовать формулу для расчета чистого рабочего времени, исключая выходные и праздничные дни, но она не работает. Я получаю "#ИМЯ?" как вывод. Я использую его для расчета с 9:6 до 18:00 (XNUMX:XNUMX). Пожалуйста помоги.
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо, в целом эта формула отлично работает для меня, спасибо, но как мне это сделать для периодов времени, превышающих месяц? Моя формула такова: =(ЧИСТРАБДНИ(L22,M22,BankHols17to21)-1)*("18:00"-"8:00")+IF(NETWORKDAYS(M22,M22,BankHols17to21),MEDIAN(MOD(M22,1, 18),"00:8","00:18"),"00:22")-МЕДИАНА(ЧИСТРАБДНИ(L22,L22,1)*MOD(L18),"00:8","00:11" ) Таким образом, эти две даты правильно отображаются как 18 рабочих часов: 05/2017/08 00:19 05/17/09 00:17 Но эта, разница между датами которой составляет более года, показывает 8 дней 18 часов: 05 /17/00 00:28 05/18/09 00:XNUMX Спасибо.
Этот комментарий был сведен к минимуму модератором на сайте
У меня такая же проблема.
Вы решили это?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, я пытался использовать эту функцию, чтобы получить минуты между двумя днями, исключая праздничные и выходные дни. Независимо от того, что я всегда получаю 0 минут в моем ответе. Я буду более чем счастлив поделиться своим файлом Excel, если это необходимо. Ваша помощь будет принята с благодарностью.
Этот комментарий был сведен к минимуму модератором на сайте
Спасибо за ваше сообщение. Чтобы получить минуты между двумя датами, исключая праздники и выходные, вам просто нужно изменить [h]:mm на [mm] на последнем шаге.
Этот комментарий был сведен к минимуму модератором на сайте
=(ЧИСТРАБДНИ.МЕЖД(A2,B2,11,H$1:H$3)-1)*("17:30"-"8:30")+ЕСЛИ(ЧИСТРАБДНИ.МЕЖД(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"), Что такое число 11 в приведенной выше формуле.?? А также, как написать праздник в формуле, если у меня есть только один праздник в месяце??
Этот комментарий был сведен к минимуму модератором на сайте
Число 11 (воскресенье как выходной) относится к номеру выходного дня Номер выходного дня Выходные дни 1 или опущено Суббота, воскресенье 2 Воскресенье, понедельник 3 Понедельник, вторник 4 Вторник, среда 5 среда, четверг 6 четверг, пятница 7 пятница, суббота 11 только воскресенье 12 понедельник только 13 только вторник 14 только среда 15 только четверг 16 только пятница 17 только суббота
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуй ,

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

Не могли бы вы написать формулу для расчета времени ч / б дней, включая выходные (сб и вс)
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте,

Пожалуйста, напишите, как рассчитать время ч / б две даты, включая выходные.
Этот комментарий был сведен к минимуму модератором на сайте
=(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"),


не могли бы вы объяснить, как это работает.
Этот комментарий был сведен к минимуму модератором на сайте
Привет, большое спасибо за вашу формулу, она действительно очень помогает мне в моей работе. но моя проблема заключается в том, как вы можете убрать время перерыва с 12:1 до 30:8 на основе вашего рабочего времени с 5:XNUMX до XNUMX:XNUMX. Для меня действительно очень много значит, если вы решите мою проблему. пожалуйста помоги..
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте, не знаете ли вы, где я могу найти формулу, включающую упомянутый вами перерыв? Благодарю вас !
Этот комментарий был сведен к минимуму модератором на сайте
Привет, для меня некоторые значения времени начала/окончания приходятся на выходные или время вне определенного окна. Для этих ячеек значение отображается как 00:00:00. Есть ли способ исправить это?
Этот комментарий был сведен к минимуму модератором на сайте
Эта формула работает, чтобы вернуть чистые рабочие часы и минуты. Как преобразовать часы в дни, исходя из восьмичасового рабочего дня? Например, результат «0 дней, 18 часов, 45 минут» должен быть «2 дня, 2 часа, 45 минут».
Этот комментарий был сведен к минимуму модератором на сайте
разделите часы на 8 вместо 24, так как рабочее время вашей команды составляет 8 часов. 18 часов 45 минут / 8 часов = 2 дня 2 часа 45 минут (2 дня = 2 * 8 = 16 часов)
Этот комментарий был сведен к минимуму модератором на сайте
привет, мне нужно сравнить время входа в систему, если оно находится между диапазоном времени, в случае, если его время падения диапазона 1, тогда время авторизации одного и того же входа находится в пределах определенного времени диапазона 1, и у нас есть от 3 до 4 диапазонов разных времен и его авторизация соответственно?
кто-нибудь может помочь?
Этот комментарий был сведен к минимуму модератором на сайте
Привет, я пытаюсь найти формулу, которая включает время перерыва с 12:1 до 30:8 на основе вашего рабочего времени с 5:XNUMX до XNUMX:XNUMX. он опубликован? Большое спасибо за вашу помощь, этот блог мне очень помог!
Этот комментарий был сведен к минимуму модератором на сайте
Удачи с этим? Я также ищу формулу для вычитания одного часа в день, но не обязательно установленного времени. В моей настройке рабочий день 8-5, но засчитывается только 8 часов. Спасибо!
Этот комментарий был сведен к минимуму модератором на сайте
а если выходные только воскресенье
Этот комментарий был сведен к минимуму модератором на сайте
используйте формулу =NETWORKDAYS.INTL, вы получите весь синтаксис для вашего запроса
Этот комментарий был сведен к минимуму модератором на сайте
Утро,

I4 = Дата запуска MFG (9 14:18)
J4:M4 = Расчетное количество часов работы (28)
N4 = Sum(J4:M4)/8, где 8 представляет количество часов работы для расчета требуемого количества дней.
O4 = Время выполнения MFG.. вот здесь у меня возникла проблема..

Я хочу, чтобы электронная таблица сообщала мне, когда работа будет завершена; точнее время. Однако я не знаю, как написать формулу, поэтому она учитывает только 7:00–17:00 и исключает 17:00–7:00.


Прямо сейчас у меня есть 3.5-дневный LT, начинающийся 9 в 14:18, а вывод дает мне 8 в 00:09. Но я не могу устроиться на 17:18, потому что это вне стандартного рабочего времени. Желаемый результат должен быть 20 00:20.

Дата начала: 09 14:18 - 8:00 17, 00 1:9 - 15:18 8, 00 17:00 - 2:9 16, 18/ 8/00 17:00 - 3:9 это 17. есть идеи?
Этот комментарий был сведен к минимуму модератором на сайте
what is "11" in the formula --> (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"),
Этот комментарий был сведен к минимуму модератором на сайте
Привет всем,


В этой формуле ошибка:

=(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"),


Если время окончания больше времени начала, формула дает неверное значение. Мы должны обратить на это внимание.


Но формула очень помогла! Спасибо
Этот комментарий был сведен к минимуму модератором на сайте
тогда это еще не конец :)
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте господа,
Даже я нахожу ту же ошибку, о которой вы упоминали выше, не могли бы вы поделиться ответом, если у вас есть

Дева
Этот комментарий был сведен к минимуму модератором на сайте
Здравствуйте

I used your formula "=(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")" works great

Но я получаю с этими данными отрицательные ошибки даже при использовании «системы дат 1904 года».

Пример данных: 01.10.2018 15:10 / 03.10.2018 11:15 (формат даты tmjjjj чч:мм)

Ошибка: данные и время, которые являются отрицательными или слишком большими, отображаются как #######

У вас есть идея?

Хуан
Этот комментарий был сведен к минимуму модератором на сайте
Формула включает субботу. Номер 11 в

ЧИСТРАБДНИ.МЕЖД(A2,B2,11,H$1:H$3)

означает пн-сб. Замените 11 на 1 для пн-пт :-)

=(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"),
Здесь еще нет комментариев
Загрузить ещё
Оставляйте свои комментарии
Публикация как гость
×
Оценить этот пост:
0   Характеристики
Предлагаемые места

Подписывайтесь на Нас

Copyright © 2009 - www.extendoffice.ком. | Все права защищены. Питаться от ExtendOffice, | Карта сайта
Microsoft и логотип Office являются товарными знаками или зарегистрированными товарными знаками Microsoft Corporation в США и / или других странах.
Защищено Sectigo SSL