Как посчитать количество рабочих дней в месяце в excel
Довольно распространенная задача: определить количество рабочих дней в период между двумя датами – это частный случай расчета числа дней, который мы уже рассматривали ранее. Тем не менее, в Excel для этого даже существуют специальные функции.
Не совсем правильно будет пользоваться простейшими методами вычитания одной даты из другой либо функцией РАЗНДАТ. Ведь мы при этом не учитываем наш график труда и отдыха, что весьма важно.
Microsoft Excel имеет две основных функции, специально предназначенных для расчета будних дней – РАБДЕНЬ (WORKDAY) и ЧИСТРАБДНИ (NETWORKDAYS). Мы также остановимся на дополнительных РАБДНИ.МЕЖД и ЧИСТРАБДНИ.МЕЖД, которые могут быть очень полезными.
Функция РАБДЕНЬ определяет момент времени, который наступит через какое-то количество рабочих дней в будущем или в прошлом. Вы можете использовать ее для добавления или вычитания рабочих дней к определенной точке времени.
Используя формулу ЧИСТРАБДНИ, вы можете рассчитать, сколько рабочих дней во временном интервале.
А теперь давайте подробнее рассмотрим каждую их них отдельно и посмотрим, как можно использовать для расчета будних дней в ваших таблицах Excel.
Функция РАБДЕНЬ.
Она возвращает момент времени, который наступит через определенное число рабочих дней (или наступил ранее). Она не учитывает дни отдыха и любые праздники, которые вы отдельно укажете.
Она подходит для расчета производственных графиков и сроков исполнения на основе стандартного производственного календаря, причем суббота и воскресенье – это для нее всегда дни отдыха.
РАБДЕНЬ - это встроенная функция в Excel 2007, 2010, 2013 и 2016. Ее можно ввести руками либо при помощи мастера функций. В более ранних версиях вам нужно специально включать пакет инструментов анализа.
Вам необходимо использовать следующие аргументы:
РАБДЕНЬ(дата_начала; дни; [праздничные_дни])
Первые два обязательны, а последний – по желанию:
- Дата_начала – с нее начинается подсчет дней.
- Дни - рабочие дни, которые нужно добавить или вычесть из начальной точки отсчета. Положительное число возвращает будущий момент времени, отрицательное — возвращает в прошлое.
- Праздничные_дни - необязательный список дат, которые следует считать нерабочими днями. Это может быть либо диапазон ячеек, содержащих даты, которые вы хотите исключить из расчетов, либо константа массива.
Теперь, когда вы знаете основы, давайте посмотрим, как вы можете использовать РАБДЕНЬ в ваших таблицах Excel.
Как добавить или вычесть рабочие дни.
Чтобы рассчитать рабочие дни в Эксель, следуйте этим простым правилам:
- Чтобы добавить будние дни, введите положительное число в качестве аргумента дни в формуле РАБДЕНЬ.
- Чтобы вычесть будние дни, используйте там же отрицательное число.
Предположим, у вас в ячейке A10 указан начальный момент времени, а список праздников — в ячейках А2: А7. Вы хотите узнать, какое число будет через 90 рабочих дней в будущем и прошлом.
Вы можете сделать это, используя следующие формулы:
Чтобы добавить столько же к начальной точке, но учитывая праздничные дни в А2: А7:
Чтобы вычесть 90 рабочих дней, исключая праздники:
Чтобы рассчитать сроки на основе текущей даты, используйте функцию СЕГОДНЯ() в качестве точки начала:
Чтобы добавить 90 рабочих дней:
Чтобы переместиться на такое же время назад по отношению к сегодня:
Чтобы указать время начала непосредственно в формуле, используйте формулу ДАТА():
На следующем скриншоте показаны результаты всех этих и еще нескольких формул РАБДЕНЬ:
И, естественно, вы можете указать, сколько будних дней нужно добавить либо вычесть из точки начала в некоторой ячейке, а затем сослаться на эту ячейку в своей формуле. Например:
Где A10 - начальная датировка, а C1 - число рабочих дней до (отрицательные числа) или после (положительные числа) нее, без учета праздников.
Настраиваем свой график выходных дней.
В случае, если у вас применяется какой-то особенный график нерабочих дней (не суббота и воскресенье), то вам в помощь – формула РАБДЕНЬ.МЕЖД (WORKDAY.INTL в английской версии).
Она появилась в Excel 2010, в более старых версиях программы ее нет.
Дополнительно к своей предшественнице, она позволяет указывать, какие именно дни недели предназначены у вас для отдыха.
Синтаксис ее выглядит следующим образом:
РАБДЕНЬ.МЕЖД (дата_начала, дни, [выходные], [праздничные_дни])
Первые два аргумента являются обязательными и знакомыми нам:
Дата_начала – начальный момент времени.
Дни - будние дней до (отрицательное значение) или после (положительное значение) начальной точки. Если этот аргумент в результате каких-то расчетов передается в виде десятичного числа, то дробная часть отбрасывается.
Последние два аргумента являются необязательными:
Выходные - указывает, что именно следует считать днями отдыха. Это может быть либо число, либо текст, как показано ниже.
Число
Выходные дни
1 или отсутствует
Только в пятницу
Текст для указания выходного дня - серия цифр из семи нулей и единиц, представляющих семь дней недели, начиная с понедельника. 1 означает нерабочий день, и 0 — соответственно наоборот. Например:
- «0000010» — суббота - отдыхаем.
- «1000001» — понедельник и воскресенье - отдыхаем.
И вот как это может выглядеть на примере:
Я бы рекомендовал обратить внимание на конструкцию
Просто переставляя 0 и 1, можно прямо указать любой график труда и отдыха.
Все остальное, думаю, достаточно хорошо понятно из скриншота.
Замечание. Функции Excel РАБДЕНЬ и РАБДЕНЬ.МЕЖД возвращают порядковые номера (числа), представляющие даты. Чтобы они отображались как нужно, выделите нужные ячейки и нажмите Ctrl + 1, чтобы открыть диалоговое окно «Формат ячеек». На вкладке «Число» выберите «Дата» и определите нужный формат.
Как рассчитать, сколько рабочих дней между двумя датами?
В ситуациях, когда вам нужно получить число рабочих дней в каком-то временном промежутке, используйте формулу ЧИСТРАБДНИ() (в английском варианте – NETWORKDAYS) :
ЧИСТРАБДНИ (дата_начала; дата_окончания; [праздничные_дни])
Первые два аргумента должны уже быть вам знакомы, а третий (необязательный) аргумент позволяет исключить пользовательский список праздников из подсчета.
Чтобы узнать, сколько будних дней между двумя точками в столбцах A и B, используйте следующую формулу:
При желании вы можете ввести свой список праздников в отдельный диапазон и дополнить формулу, чтобы исключить эти дни:
В результате учитываются только будние дни.
А если у нас свой особенный график труда и отдыха?
Если вам нужно обрабатывать какие-то особенные нерабочие дни (например, отдыхаем только в воскресенье, в понедельник и воскресенье и т.д), используйте функцию ЧИСТРАБДНИ.МЕЖД, которая позволяет указать, какие дни недели предназначены для отдыха.
ЧИСТРАБДНИ.МЕЖД (WORKDAY.INTL в английском варианте) - более мощная модификация функции ЧИСТРАБДНИ, которая работает с пользовательскими параметрами выходных . Как и его предшественница, она возвращает число рабочих дней в будущем или в прошлом, но позволяет вам определить, какие именно дни недели в вашем графике нужно считать нерабочими.
Формула ЧИСТРАБДНИ.МЕЖД появилась впервые в Excel 2010, и поэтому недоступна в более ранних версиях программы.
Синтаксис ее выглядит следующим образом:
ЧИСТРАБДНИ.МЕЖД (дата_начала, дни, [выходные], [праздничные_дни])
Первые два аргумента являются обязательными и встречались нам уже неоднократно:
Дата_начала - начальная точка.
Дни - будние дней до (отрицательное значение) или после (положительное значение) точки начала. Если этот аргумент в результате каких-то расчетов предоставляется в виде десятичного числа, то он усекается до целого.
Последние два аргумента являются необязательными:
Выходные - указывает, в какие дни недели вы не работаете. Это может быть либо число, либо текст, как показано ниже.
Число
Выходные дни
1 или отсутствует
Только в пятницу
Текст для указания выходного дня - серия цифр из семи нулей и единиц, представляющих семь дней недели, начиная с понедельника. 1 означает день отдыха, а 0 — будний день. Например:
- «0000011» - суббота и воскресенье – не работаем.
- «1000001» - понедельник и воскресенье - отдыхаем.
На первый взгляд, такие конструкции могут показаться громоздкими и ненужными, но лично мне этот метод больше нравится, потому что вы можете создавать графики выходных «на лету», не запоминая никаких чисел.
Праздники - необязательный список дней, которые вы хотите исключить из своего календаря. Это может быть диапазон ячеек, содержащих даты, или константа массива из последовательных значений, представляющих их. Все, как и ранее.
А теперь несколько примеров.
=ЧИСТРАБДНИ.МЕЖД(A10;B10;1) – это знакомый нам вариант, когда нерабочими считаются суббота и воскресенье.
А вот =ЧИСТРАБДНИ(A10;B10;11) – отдых только в воскресенье,
=ЧИСТРАБДНИ.МЕЖД(A10;B10;2) – в воскресенье и понедельник. Такой расчет полезен, если в вашей организации свой график работы, не такой, как у всех.
не работаем в понедельник и вторник, учитывается также список праздников.
Например, давайте посмотрим, сколько нужно еще отработать до конца 2020 года. Для этого введем 31 декабря 2020 года в формате даты, и используем следующее выражение:
="Осталось "&ЧИСТРАБДНИ(СЕГОДНЯ();ДАТА(2020;12;31);A2:A7)&" дн. до конца года!"
Ух, осталось всего 203 дня! Не так много, как я думал :)
Примечание. Чтобы учесть свой индивидуальный график работы, используйте ЧИСТРАБДНИ.МЕЖД().
Как определить ближайший рабочий день?
Предположим, мы должны определить, какого числа мы можем выставлять счета своим клиентам, а какого - нет. Думаю, вы согласитесь, что делать это в выходной день не стоит. Поэтому в зависимости от даты мы должны либо оставить ее неизменной, либо предложить другой день - рабочий.
Если выходными днями у нас традиционно считаются суббота и воскресенье, то ближайший рабочий день - это либо пятница, либо понедельник. Вот как это может выглядеть.
Для субботы подсчитать дату ближайшего понедельника можно двумя способами:
Ближайший рабочий день в прошлом (то есть прошлая пятница) определяется так:
Ну и если немного усложнить условие - если день попадает на субботу, тогда ближайший рабочий день - прошедшая пятница, а если на воскресенье - тогда предстоящий понедельник:
Как видите, на слишком уж сложно.
Возможные ошибки при расчетах.
- аргументы начала и конца временного отрезка передают неверные данные (не являются датой, записаны как текст скорее всего)
- аргумент «выходные» указан неверно (например, 22 или «000000011»).
Надеюсь, эти примеры и инструкции были вам полезны. Если еще остались вопросы - пишите в комментариях. Постараюсь ответить.
Также рекомендуем о работе с датами:
Как вывести месяц из даты - На примерах мы покажем, как получить месяц из даты в таблицах Excel, преобразовать число в его название и наоборот, а также многое другое. Думаю, вы уже знаете, что дата в… Функция Excel СЕГОДНЯ — синтаксис и основные применения - Функция СЕГОДНЯ в Excel делает именно то, на что указывает ее имя - возвращает сегодняшнюю дату. Синтаксис ее настолько прост, насколько это вообще возможно - у нее вообще нет аргументов. Всякий… Как быстро вставить сегодняшнюю дату в Excel? - Это руководство показывает различные способы ввода дат в Excel. Узнайте, как вставить сегодняшнюю дату и время в виде статической метки времени или динамических значений, как автоматически заполнять столбец или строку… Как посчитать количество дней между датами в Excel? Примеры и советы. - Из этого обзора вы узнаете несколько быстрых и простых способов подсчета, сколько дней проходит между двумя датами в Excel. Вам интересно, сколько составляет разница в днях, месяцах и годах между… 8 способов — как посчитать возраст в Excel по дате рождения. - В Excel нет специальной функции, чтобы посчитать возраст, однако существует несколько различных способов его определения на основе даты рождения. Вы узнаете несколько приемов, как рассчитать его в количестве полных лет,… Как преобразовать текст в дату в Excel или извлечь ее из сложной фразы? - Сейчас вы научитесь использовать функции Excel для преобразования текста в дату. Вы также узнаете, как это сделать без использования формул. Поскольку Excel - не единственное приложение, которое вы используете, иногда…Как вы знаете, дней в феврале високосного года на 1 день больше, чем дней в обычном феврале. Итак, как быстро подсчитать количество дней или рабочих дней в феврале любого года в Excel? В этой статье предлагаются два обходных пути, позволяющих легко подсчитать количество дней или рабочих дней в любом месяце.
Подсчитайте числа всех выходных / будних дней / определенного дня недели между двумя датами в ExcelKutools for Excel предоставляет простые обходные пути для быстрого подсчета числа всех выходных, выходных или определенного дня недели в диапазоне дат с Считать выходные между двумя свиданиями формула, Считайте будние дни между двумя датами формула и Подсчитайте количество определенных дней недели Формула.
- Повторное использование чего угодно: Добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстовых функций: Извлечь число из текстовой строки; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния : Несколько книг и листов в одну; Объединить несколько ячеек / строк / столбцов без потери данных; Объедините повторяющиеся строки и сумму.
- Разделить инструменты : Разделение данных на несколько листов в зависимости от ценности; Из одной книги в несколько файлов Excel, PDF или CSV; От одного столбца к нескольким столбцам.
- Вставить пропуск Скрытые / отфильтрованные строки; Подсчет и сумма по цвету фона ; Отправляйте персонализированные электронные письма нескольким получателям массово.
- Суперфильтр: Создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделям, дням, периодичности и др .; Фильтр жирным шрифтом, формулы, комментарий .
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Подсчитайте количество дней в месяце по формуле
Удивительный! Использование эффективных вкладок в Excel, таких как Chrome, Firefox и Safari!
Экономьте 50% своего времени и сокращайте тысячи щелчков мышью каждый день!
Этот метод представит формулы для подсчета количества дней в месяце на основе заданной даты или заданного года и месяца в Excel.
1. Выберите пустую ячейку, в которую вы поместите результат подсчета, и введите формулу. = ДЕНЬ (ДАТА (ГОД (B1); МЕСЯЦ (B1) +1,)) (B1 - ячейка с заданной датой) в нее и нажмите Enter ключ.
И тогда вы получите общее количество дней в указанном месяце на основе заданной даты. Смотрите скриншот:
Примечание: Если вы хотите подсчитать количество дней в месяце в зависимости от года и месяца, примените эту формулу = ДЕНЬ (ДАТА ( Given_Year , Geiven_Month +1,0)) , например = ДЕНЬ (ДАТА ( 2015 , 2 +1,0)).
Подсчитайте количество рабочих дней в месяце с помощью Kutools for Excel
Если у вас установлен Kutools for Excel, вы можете применить его Считайте дни недели между двумя датами формула для быстрого подсчета общего количества рабочих дней в месяце.
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Бесплатная пробная версия сейчас!
1. Выберите пустую ячейку, в которую вы поместите результат подсчета, и нажмите Kutools > Формулы > Считайте дни недели между двумя датами. Смотрите скриншот:
2. В открывшемся диалоговом окне Помощник по формулам введите первую дату указанного месяца в поле Началовведите последнюю дату указанного месяца в поле Дата окончания, и нажмите Ok кнопку.
Внимание: Пожалуйста, заключите дату начала и дату окончания в кавычки. Смотрите скриншот:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Затем подсчитывается общее количество дней недели в указанном месяце и возвращается в выбранную ячейку.
Примечание: Kutools for Excel также предоставляет Считать выходные между двумя свиданиями формула для быстрого подсчета общего количества всех выходных в месяц. Смотрите скриншот:
Kutools for Excel - Включает более 300 удобных инструментов для Excel. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Get It Now
Доброго дня!
В некоторых случаях при работе с документами Excel очень нужно, чтобы проставлялась текущая дата: например, вы часто делаете отчеты по одной и той же форме (глаз со временем "замыливается" и их можно перепутать) ; или, скажем, у вас есть десяток-другой задач и вы каждый день по ним просматриваете как продвигается дело, и сколько дней осталось до завершения.
Для решения всех подобных "загвоздок" в Excel есть несколько специальных формул. Собственно, о них и будет сегодняшняя заметка.
Примечание: скриншоты в статье приведены из Excel 2019 (материал актуален также для версии 2016, 2013).
Работа с датами в Excel
ШАГ 1: вставка текущей даты
Самый простой и быстрый вариант поставить в ячейку текущую дату и время — это воспользоваться функцией " ТДАТА() " (достаточно скопировать в любую ячейку Excel, разумеется, без кавычек) . См. пример ниже.
Альтернативный вариант - использовать формулу " СЕГОДНЯ() ". Работает аналогично (единственное, по умолчанию, не ставит текущее время).
ШАГ 2: сколько всего дней между двумя датами
Это еще один популярный вопрос.
Определить общее количество дней между двумя датами можно с помощью формулы " =ДНИ(B2;A2) " (где B2 и A2 - конечная и начальная дата, соответственно) . Разумеется, зная значение в днях — легко узнать и значение в годах (достаточно разделить число на 365. ).
Ко-во дней посчитано
Кстати, если у вас ячейки имеют формат дат — то можно поступить даже проще: просто вычесть из одной даты другую (как если бы это были числа). Причем, если у вас в одной из ячеек будет указано время (как в моем примере) — Excel посчитает вплоть до десятых.
ШАГ 3: определение кол-ва рабочих дней между датами
Более интересный вариант, когда нам нужно не просто определить кол-во дней, но и узнать сколько из них выходных, а сколько рабочих.
В этом случае может помочь формула " =ЧИСТРАБДНИ(A2;B2) " (где A2 и B2 - начальная дата и конечная, соответственно) .
51 рабочий день
Кстати, если вам нужно подсветить ячейки красно-зеленым цветом (чтобы сразу же видеть те строки, где, например, горят сроки — рекомендую использовать инструмент "Условное форматирование" в разделе "Главная" ). Пример ниже.
Правила выделения ячеек
В общем-то, выглядит это все достаточно наглядно (например, те задачи, которым осталось времени на выполнение меньше 4 дней - у меня в отчете подсвечиваются красным. Удобно!)
Примечание : для обновления текущей даты и времени — нажмите сочетание кнопок Ctrl+R.
Добрый день уважаемый читатель!
Сегодня я хочу немного облегчить жизнь и работу тех специалистов, в обязанностях которых существует работа с датами, хотя статья будет полезная многим. Существует много задач с датами, но в статье я рассмотрю и покажу, как вычислить рабочие дни в Excel. Эта возможность очень полезна и жизненно необходима для бухгалтеров по начислению зарплаты, экономистов и нормировщиков рабочего времени, специалистов отдела кадров. Я думаю, что о преимуществах автоматического вычисления дат вам очень пригодятся.
Для начала, вы должны знать, что Excel видит любую дату как обыкновенное числовое значение, которое начинается с цифры 1 и соответствует дате 1 января 1900 г. К примеру, 1 января 2018 г. будет равняться числу 43101 и разница между датами составит 43100 дней. А ввиду этого мы очень просто можем манипулировать датами, так же, как и числами, а это значительно делает проще нашу работу.
Количество дней между двумя датами в Excel
Это очень простой и понятный пример, в котором необходимо от последней даты отнять начальную дату, а полученное значение преобразовать в числовой формат. Как видите это можно произвести без помощи функций, но для получения рабочих дней нам это не подойдет.
Вычисление рабочих дней в Excel между двумя датами
Итак, поскольку количество календарных дней нам не подходит, а нужны дни работы, пользуемся для решения этой задачи функцией ЧИСТРАБДНИ.
Синтаксис этой функции достаточно прост и понятен:
= ЧИСТРАБДНИ(_нач_дата_; _кон_дата_; _[праздники]_), где:
- Начальная дата – является обязательным аргументом и указывается дата начала отсчёта;
- Конечная дата – также обязательный аргумент и указывает дату окончания периода расчётов;
- Праздники – не обязательный аргумент, являет собой список дат или ссылку на них, которые следует изъять из расчётов, например, государственные или религиозные праздники.
Простой пример:
У нас есть даты начала периода в диапазоне ячеек А2:А5 и даты конца периода – В2:В5. Используя нашу функцию, мы получаем нужный нам интервал в рабочих днях. Сложный пример:
Усложним нашу задачу добавив несколько праздничных дней в диапазоне Е2:Е5. Тогда наша формула пересчитает рабочие дни и отминусует праздники, попавшие в вычитаемый период. Диапазон с праздниками советую закрепить абсолютными ссылками, так как чтобы при копировании формулы она не выдавала ошибку и предоставляла правильный результат.
А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями, прочитанным и ставьте лайк!
Читайте также: