Определение рабочих дней в excel vba. Определение выходных дней в табеле. Вычисление рабочих дней в Excel между двумя датами
Задача: подсчитать, сколько рабочих дней попадает между двумя датами. Функция Excel ЧИСТРАБДНИ прекрасно справляется с задачей, если у вас 5-дневная рабочая неделя с субботой и воскресеньем в качестве выходных. Эта заметка покажет вам, как выполнить расчет рабочей недели любой продолжительности и любым набором выходных.
Перенос выходных дней
Примечание. Каждая функция определяется сначала, а затем аргументы функции, за которой следует синтаксис функции — как устроена формула, которая включает имя функции и ее аргументы. Помните: Аргументы всегда окружены круглыми скобками, а отдельные аргументы разделяются запятыми.
Прим. Багузина. Следует отметить, что, начиная с версии 2010 в Excel появилась более гибкая функция ЧИСТРАБДНИ.МЕЖД, которая дает возможность расчета для любой продолжительности рабочей недели и любого (но постоянного) набора выходных дней. Эта функция будет описана во второй части заметки. Тем не менее, подход Билла Джелена остается интересным и сам по себе.
Аргументами для этой функции являются.
Обратите внимание, что формула появляется в строке формул.
Вы также можете рассчитать серию дат по рабочим дням или получить дату начала на основе заданной даты, за исключением определенных дней, таких как праздничные дни, выходные дни или нерабочие дни.
Обратите внимание, что мы используем положительное число для расчета будущих дат и отрицательного числа для расчета прошлых дат. Ответ возвращает серийный номер, который представляет конкретную дату.
Результат: возвращает следующую или предыдущую рабочую дату на основе введенных данных.
Не забудьте использовать положительное число для будущих дат и отрицательное число для прошлых дат. Кроме того, введите праздничные дни как диапазон ячеек, где указаны конкретные даты отпуска, или как список серийных номеров, которые представляют фактические даты праздников.
Используйте ту же таблицу, что и выше. Используйте следующие коды дней недели для определения дней выходных дней. Если оставить этот параметр пустым, по умолчанию он будет равен числу.
Наши требования в отношении вычислений могут отличаться, но обычно мы можем иметь следующие ситуации.
Для начала определите диапазон, и назовите его Праздники. Для этого выделите диапазон вместе с заголовком и пройдите по меню ФОРМУЛЫ –> Создать из выделенного (рис. 2). Убедитесь, что выбрана опция в строке выше . Кликните Ok в окне Создание имени из выделенного диапазона .
Вычислить количество рабочих дней между двумя датами или в определенные дни с определенной даты. Суббота и воскресенье будут считаться выходными днями. Праздники: Представляет собой праздники и их необходимо исключить из расчета. Это может быть дата или ссылка на ячейку, которая содержит дату или диапазон ячеек, содержащих несколько дат.
Определив диапазон, снова введите формулу в С3. Теперь она примет вид: =ЧИСТРАБДНИ(A3;B3;Праздники)
Теперь перейдем к расчету числа рабочих дней при 6-дневной рабочей недели (рис. 3). Формула в С3: =СУММПРОИЗВ(- — (СЧЁТЕСЛИ(Праздники;СТРОКА(ДВССЫЛ(A3& » : » &B3)))=0); — — (ДЕНЬНЕД(СТРОКА(ДВССЫЛ(A3& » : » &B3));2)
Вы также можете рассчитать серию дат по рабочим дням или получить дату начала на основе заданной даты, за исключением определенных дней, таких как праздничные дни, выходные дни или нерабочие дни.
Учите КОМПЬЮТЕР вместе с нами: Как отсортировать сотрудников по дням рождения в Excel
InStr возвращает число, обозначающее положение символа в String1, где было обнаружено String2. Если InStr не находит String2 в String1, то возвращается 0. Если String1 (или String2) имеет значение Null, то функция также возвращает Null.
Учите КОМПЬЮТЕР вместе с нами!
Стандартная сортировка списка сотрудников в Excel по дате их рождения не решает поставленной задачи, поскольку такая сортировка выстраивает их в хронологическом порядке, с учетом не только дня и месяца, но и года рождения.
В результате такой сортировки мы будем видеть, кто из сотрудников старше или моложе. А вот, чтобы сказать, у кого скоро будет день рождения — для этого придется вручную просматривать от начала до конца весь список, что неудобно.
Видеоурок по данной теме «Как отсортировать сотрудников по дням рождения в Excel» можно найти на нашем канале «Учите компьютер вместе с нами».
Идеальная сортировка для такого случая — это упорядочивание людей по возрастанию их месяца рождения, а в пределах каждого месяца — по дате. Этим мы сейчас и займемся.
Но прежде всего, какой вид будут иметь исходные данные? Пусть, в колонке «А» располагается порядковый номер сотрудника, в колонке «В» — его фамилия и инициалы, а в колонке «С» — дата рождения, как показано на рисунке ниже.
Первоочередная наша задача — это выделить из даты рождения каждого сотрудника отдельно месяц и день. Для этого будем использовать вспомогательные колонки «D» и «E«.
Далее, выделяем ячейки D2:Е2 и за правый нижний угол растягиваем введенные формулы вниз, до конца списка. В результате мы получим таблицу вида:
Ну а теперь, заключительный штрих. Нам осталось выполнить сортировку всех сотрудников по двум последним колонкам.
Для этого, выделяем область сортировки, протягивая левой кнопкой мыши по заголовкам колонок А:Е. Затем переходим во вкладку «Данные» и в секции «Сортировка и фильтр» нажимаем кнопку «Сортировка«.
В появившемся окне «Сортировка» из выпадающего списка «Сортировать по» выбираем поле «Месяц«. Далее нажимаем кнопку «Добавить уровень» и в списке «Затем по» выбираем поле «День«.
Все остальные параметры сортировки оставляем без изменений и нажимаем кнопку «ОК«. В результате, получим как раз то, что и требовалось:
Как видим, теперь все сотрудники упорядочены по возрастанию месяца их рождения. Если в течение месяца день рождения наступает сразу у нескольких человек, тогда они упорядочены по возрастанию дней.
Зная текущую дату, теперь не сложно определить, у кого из них скоро будет день рождения и в какой последовательности.
Как отфильтровать даты между двумя конкретными датами в Excel?
Объедините несколько рабочих листов или рабочих книг в один рабочий лист или рабочая тетрадь может стать огромной задачей в вашей повседневной работе. Но если у вас есть Kutools for Excel , его мощная утилита — Combine может помочь вам быстро объединить несколько листов, книг в один лист или книгу.
