ЧИСТРАБДНИ (функция ЧИСТРАБДНИ)
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше
В этой статье описаны синтаксис формулы и использование функции ЧИСТРАБДНИ в Microsoft Excel.
Описание
Возвращает количество рабочих дней между датами «нач_дата» и «кон_дата». Праздники и выходные в это число не включаются. Функцию ЧИСТРАБДНИ можно использовать для вычисления заработной платы работника на основе количества дней, отработанных в указанный период.
Совет: Чтобы вычислить количество полных рабочих дней между двумя датами с использованием параметров, определяющих количество выходных в неделе и то, какие это дни, используйте функцию ЧИСТРАБДНИ.МЕЖД.
Синтаксис
ЧИСТРАБДНИ(нач_дата;кон_дата;[праздники])
Аргументы функции ЧИСТРАБДНИ описаны ниже.
-
нач_дата — обязательный аргумент. Начальная дата.
-
кон_дата Обязательный. Конечная дата.
-
Праздники Необязательный. Список из одной или нескольких дат, которые требуется исключить из рабочего календаря, например государственные праздники. Список может представлять собой диапазон ячеек, содержащих даты, или константу массива, содержащую числа, которые представляют даты.
Важно: Даты должны быть введены с использованием функции ДАТА или как результат вычисления других формул и функций. Например, для указания даты 23 мая 2012 г. воспользуйтесь выражением ДАТА(2012,5,23). Если ввести даты как текст, это может привести к возникновению проблем.
Замечания
-
В приложении Microsoft Excel даты хранятся в виде последовательных чисел, что позволяет использовать их в вычислениях. По умолчанию 1 января 1900 г. имеет порядковый номер 1, а 1 января 2012 г. — 40909, так как от 1 января 1900 г. он отс0 до 40 909 дней.
-
Если какой-либо из аргументов не является допустимой датой, networkDAYS возвращает #VALUE! значение ошибки #ЗНАЧ!.
Пример
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу ВВОД. При необходимости измените ширину столбцов, чтобы видеть все данные.
|
Дата |
Описание |
|
|
01.10.2012 |
Дата начала проекта |
|
|
01.03.2013 |
Дата окончания проекта |
|
|
22.11.2012 |
Праздник |
|
|
04.12.2012 |
Праздник |
|
|
21.01.2012 |
Праздник |
|
|
Формула |
Описание |
Результат |
|
=ЧИСТРАБДНИ(A2;A3) |
Число рабочих дней между датой начала (01.10.2012) и датой окончания (01.03.2013). |
110 |
|
=ЧИСТРАБДНИ(A2;A3;A4) |
Число рабочих дней между датой начала (01.10.2012) и датой окончания (01.03.2013) с вычетом праздничного дня 22.11.2012 как выходного. |
109 |
|
=ЧИСТРАБДНИ(A2;A3;A4:A6) |
Число рабочих дней между датой начала (01.10.2012) и датой окончания (01.03.2013) с вычетом трех праздничных дней. |
107 |
К началу страницы
Нужна дополнительная помощь?
На чтение 6 мин. Просмотров 287 Опубликовано 21.05.2021
Например, у вас есть список дат и вы хотите проверить, есть ли в нем какие-либо государственные праздники, как бы вы могли это сделать быстро? В этой статье я покажу вам способы расчета всех дат американских государственных праздников в течение заданных лет, а затем легко проверю, является ли дата американским государственным праздником в Excel.
- Часть 1. Расчет государственных праздников в определенные годы в Excel
- Часть 2. Проверка того, является ли дата выходным днем в Excel
- Часть 3. Подсчет дней между двумя днями, кроме выходных и праздников, в Excel
Вкладка Office Включает редактирование и просмотр с вкладками в Office и делает вашу работу намного проще …
Подробнее … Бесплатная загрузка …
Kutools for Excel решает большинство ваших проблем и увеличивает вашу производительность на 80%
- Повторное использование чего угодно: добавляйте наиболее часто используемые или сложные формулы, диаграммы и все остальное в избранное и быстро используйте их в будущем.
- Более 20 текстов особенности: Извлечь номер из текста S tring; Извлечь или удалить часть текстов; Преобразование чисел и валют в английские слова.
- Инструменты слияния: несколько книг и листов в одну; Объединить несколько ячеек/строк/столбцов без потери данных; Объедините повторяющиеся строки и суммируйте.
- Инструменты разделения: разделение данных на несколько листов в зависимости от значения; Из одной книги в несколько файлов Excel, PDF или CSV; Один столбец в несколько столбцов.
- Вставить пропуск скрытых/отфильтрованных строк; Подсчет и сумма по цвету фона; Массовая отправка персонализированных писем нескольким получателям.
- Суперфильтр: создавайте расширенные схемы фильтров и применяйте их к любым листам; Сортировать по неделе, дню, частоте и т. Д. Фильтр жирным шрифтом, формулами, комментарием …
- Более 300 мощных функций; Работает с Office 2007-2019 и 365; Поддерживает все языки; Простое развертывание на вашем предприятии или в организации.
Подробнее … Бесплатная загрузка …
->
Содержание
- Часть 1: Расчет государственных праздников для данного года в Excel
- Сохраните диапазон как запись автотекста (оставшиеся форматы ячеек и формулы) для повторного использования в будущем
- Часть 2: Проверьте, является ли дата государственным праздником в Excel
- Часть 3: Подсчет дней между двумя днями, кроме выходных и праздников, в Excel
- Точное/статическое копирование формул без изменения ссылок на ячейки в Excel
Часть 1: Расчет государственных праздников для данного года в Excel
Прежде чем проверять, является ли дата государственным праздником, вы должны перечислить все праздники в течение данных лет в Excel. Поэтому подготовка таблицы, как показано на следующем снимке экрана, облегчит вашу работу.
Существует три вида государственных праздников в США:
(1) Первый вид – это государственный праздник с фиксированной датой, например, Новый год – 1 января. мы можем легко вычислить Новый год по формуле = ДАТА (данный год, 1,1);
(2) Второй вид – это государственный праздник в фиксированный будний день, например День президента.. Мы можем легко вычислить день президента по формуле = ДАТА (данный год, 1,1) + 14 + ВЫБРАТЬ (ДЕНЬ НЕДЕЛИ (ДАТА (данный год, 1,1)), 1,0,6,5,4,3, 2);
(3) И последний вид – это День поминовения, мы можем легко вычислить День поминовения по формуле = ДАТА (данный год, 6,1) -НЕДЕЛЬ (ДАТА (данный Год, 6,6)).
Здесь я перечисляю формулы для расчета всех государственных праздников в следующей таблице. Просто введите формулы в соответствующую ячейку и одну за другой нажимайте клавишу Enter .
| Holiday | Cell | Формулы |
| Новый год | C2 | = ДАТА (C1,1,1) |
| День Мартина Лютера Кинга | C3 | = ДАТА (C1,1,1) + 14 + ВЫБРАТЬ (ДЕНЬ НЕДЕЛИ (ДАТА (C1,1,1)), 1,0,6,5,4,3,2) |
| День президента | C4 | = DATE (C1,2,1) + 14 + CHOOSE (WEEKDAY (DATE (C1, 2,1)), 1,0,6,5,4,3,2) |
| День памяти | C5 | =DATE(C1,6,1)-WEEKDAY(DATE(C1,6,6)) |
| День Независимости | C6 | =DATE(C1,7,4) |
| День труда | C7 | = DATE (C1,9,1) + CHOOSE (WEEKDAY (DATE (C1,9,1)), 1,0,6,5,4 , 3,2) |
| День Колумба | C8 | = DATE (C1,10 , 1) + 7 + ВЫБРАТЬ (ДЕНЬ НЕДЕЛИ (ДАТА (C1,10,1)), 1,0,6,5,4,3,2) |
| День ветеранов | C9 | =DATE(C1,11,11) |
| День Благодарения | C10 | = DATE (C1,11,1) + 21 + CHOOSE (WEEKDAY (DATE (C1,11,1)), 4,3,2,1 , 0,6,5) |
| Рождество | C11 | = DATE (C1 , 12,25) |
Примечание. В формулах в приведенной выше таблице C1 является ссылочной ячейкой. который определяет данный год. В нашем примере это означает 2015 год, и вы можете изменить его в зависимости от ваших потребностей.
С помощью этих формул вы можете легко вычислить даты государственных праздников с заданными годами. См. Снимок экрана ниже:
Сохраните диапазон как запись автотекста (оставшиеся форматы ячеек и формулы) для повторного использования в будущем
Ссылка на ячейки и применение формул для расчета каждого праздника должно быть очень утомительным. Kutools for Excel предоставляет симпатичный обходной путь утилиты AutoText , чтобы сохранить диапазон как запись автотекста, который может сохранить форматы ячеек и формулы в диапазоне. И тогда вы сможете повторно использовать этот диапазон одним щелчком мыши. Работа становится легкой: достаточно одного щелчка мышки, чтобы вставить эту таблицу и изменить год в этой таблице! Полнофункциональная бесплатная пробная версия на 30 дней!
Kutools for Excel – включает более 300 удобных инструментов для Excel.. Полнофункциональная бесплатная 30-дневная пробная версия, кредитная карта не требуется! Получить сейчас
Часть 2: Проверьте, является ли дата государственным праздником в Excel
После перечисления всех дат государственных праздников определенных лет мы можем легко проверить, является ли дата государственным праздником или нет, с помощью формул в Excel . Предположим, у вас есть список дат, показанный на следующем снимке экрана, и я расскажу, как это легко сделать.
Выберите пустую ячейку помимо списка дат, – говорит ячейка B18. введите в нее формулу = ЕСЛИ (СЧЁТЕСЛИ ($ C $ 2: $ D $ 11, A18), «Праздник», «Нет»), а затем перетащите маркер заполнения в нужный диапазон. См. Снимок экрана выше:
Примечания :
(1) В формуле = ЕСЛИ (СЧЁТЕСЛИ ($ C $ 2: $ D $ 11, A18), «Праздник», «Нет»), $ C $ 2: $ D $ 11 – это диапазон государственных праздников в определенные годы, а A18 – это ячейка с датой, которую вы хотите проверить, является ли это государственным праздником, и вы можете изменить их в зависимости от ваших потребностей. И эта формула вернет «Праздник», если конкретная дата является государственным праздником, и «Нет», если это не так.
(2) Вы также можете применить эту формулу массива = ЕСЛИ (ИЛИ ( $ C $ 2: $ D $ 11 = A18), «Праздник», «НЕТ»), чтобы проверить, является ли соответствующая дата выходным или нет.
Часть 3: Подсчет дней между двумя днями, кроме выходных и праздников, в Excel
В Части 1 мы перечислили все праздники в данном году, и теперь этот метод поможет вам подсчитать количество дней, кроме всех выходных и праздничных дней, в диапазоне дат.
Выберите пустую ячейку, вы вернете количество дней, и введите в нее формулу = ЧИСТРАБДНИ (E1, E2, B2: B10) и нажмите клавишу Enter .
Примечание : в ячейках выше E1 – это дата начала указанного диапазона дат, E2 – дата окончания, а B2: B10 – это список праздников, который мы вычисляем в Части 1.
Теперь вы получите количество дней, кроме недели. заканчивается и праздники в указанном диапазоне дат.
Точное/статическое копирование формул без изменения ссылок на ячейки в Excel
Kutools for Excel Утилита Exact Copy может помочь вам легко скопировать несколько формул без изменения ссылок на ячейки в Excel, предотвращая автоматическое обновление относительных ссылок на ячейки. 30-дневная бесплатная пробная версия полнофункциональной версии!
Kutools for Excel – включает более 300 удобных инструментов для Excel. Полнофункциональная 30-дневная бесплатная пробная версия, кредитная карта не требуется! Получить сейчас
На чтение 2 мин Опубликовано 22.07.2015
В этом примере мы расскажем, как вычислить в Excel дату праздника для любого года.
Прежде, чем мы начнем, давайте заострим внимание на 2-х моментах:
- Функция CHOOSE (ВЫБОР) возвращает значение, зависящее от порядкового номера позиции. Например: =CHOOSE(3,»Car»,»Train»,»Boat»,»Plane») возвращает «Boat».
- Функция WEEKDAY (ДЕНЬНЕД) возвращает число от 1 (воскресенье) до 7 (суббота), представляющее номер дня недели.
На рисунке ниже вы видите, как выглядит готовая таблица. Если ввести год в ячейку C2, Excel выводит все праздники этого года. Конечно же, Новый год, День независимости, День ветеранов и Рождество рассчитать легко.
Все другие праздники можно описать подобным образом: n-й день месяца (за исключением Дня памяти, который немного отличается). Давайте взглянем на День благодарения 2015 года. Если вы поймёте, как вычислить День благодарения, то разберётесь и с остальными праздниками. День благодарения празднуется каждый четвёртый четверг ноября.
=DATE(C2,11,1)+21+CHOOSE(WEEKDAY(DATE(C2,11,1)),4,3,2,1,0,6,5)
=ДАТА(C2;11;1)+21+ВЫБОР(ДЕНЬНЕД(ДАТА(C2;11;1));4;3;2;1;0;6;5)
Календарь ниже поможет понять, когда будет День благодарения в 2015 году:
Пояснение:
- Выражение ДАТА(C2;11;1) — это 1 ноября 2015 года.
- Выражение ДЕНЬНЕД(ДАТА(C2;11;1)) возвращает 1, т.е. воскресенье.
- ВЫБOP(1;4;3;2;1;0;6;5) возвращает 4 (от воскресенья до четверга 4 дня).
- Получаем: 01.11.2015 + 21 + 4 = 26.11.2015
Примечание: Нам нужны 4 дополнительных дня, поскольку до первого четверга в ноябре не хватает 4 дня. К этой дате нужно добавить еще 21 день (3 недели), чтобы получить 4-й четверг ноября.
Не имеет значения, на какой день выпадает 1 ноября. Функция CHOOSE (ВЫБОР) всё равно добавит правильное количество дней до первого четверга ноября. Потом формула прибавит еще 21 день до 4-го четверга ноября. Аналогичным образом эта формула работает для каждого года.
Теперь давайте взглянем на День Мартина Лютера Кинга, который отмечается ежегодно в третий понедельник января:
=DATE(C2,1,1)+14+CHOOSE(WEEKDAY(DATE(C2,1,1)),1,0,6,5,4,3,2)
=ДАТА(C2;1;1)+14+ВЫБОР(ДЕНЬНЕД(ДАТА(C2;1;1));1;0;6;5;4;3;2)
Эта формула почти идентична предыдущей. В этот раз:
- Функция DATE (ДАТА) возвращает 1-е января.
- В аргументах функции CHOOSE (ВЫБОР) позиция со значением 0 находится на втором месте в списке значений, т.к. мы ищем понедельник.
- Если 1 января выпадает на понедельник, получаем: 14 + ВЫБОР(2;1;0;6;5;4;3;2) = 14 + 0 = 14 дней (или 2 недели) до 3-го понедельника января.
Оцените качество статьи. Нам важно ваше мнение:
Определение даты какого-нибудь праздника может быть непростой задачей. Некоторые праздники, такие как Новый год или День независимости США, не дадут вам поломать голову, потому что всегда отмечаются в один и тот же день. Для этих видов праздников вы можете просто использовать функцию ДАТА.
Например, чтобы вычислить день празднования Нового года (который всегда выпадает на 1 января) для конкретного года, хранящегося в ячейке А1, вы можете воспользоваться такой функцией: =ДАТА(А1;1;1). Другие праздники, в свою очередь, отмечаются в определенный день недели и месяц. Например, День труда в США приходится на первый понедельник сентября.
Во всех формулах подразумевается, что ячейка А1 содержит значение года (например, 2010). Заметьте, что Новый год, День независимости, День ветеранов и Рождество — каждый этот праздник всегда отмечается в определенный день в году, поэтому дату можно вычислить с помощью простой функции ДАТА.
Новый год
Этот праздник всегда приходится на 1 января. Еще раз повторю формулу для его вычисления: =ДАТА(А1;1;1).
День Мартина Лютера Кинга
Этот праздник приходится на третий понедельник января. Следующая формула рассчитывает дату Дня Мартина Лютера Кинга для года, указанного в ячейке А1:
=ДАТА(А1;1;1)+ЕСЛИ(2<ДЕНЬНЕД(ДАТА(А1;1;1));7-ДЕНЬНЕД(ДАТА(А1;1:1))+2;2-ДЕНЬНЕД(ДАТА(А1:1:1)))+((3-1)*7).
День президента
День президента приходится на третий понедельник февраля. Формула для вычисления Дня президента для года из ячейки А1 такова:
=ДАТА(А1;2;1)+ЕСЛИ(2<ДЕНЬНЕД(ДАТА(А1;2;1));7-ДЕНЬНЕД(ДАТА(А1;2;1))+2;2-ДЕНЬНЕД(ДАТА(А1;2:1)))+((3-1)*7).
Католическая Пасха
Нахождение даты Пасхи затрудняется из-за сложного метода расчета. Пасха — это первое воскресенье после полнолуния, случившегося после дня весеннего равноденствия. Я нашел эти формулы для вычисления дня католической Пасхи в Интернете, но понятия не имею, как они работают. Они не подойдут, если ваша книга использует систему дат 1904 (см. эту статью для получения более подробной информации о системе дат 1904).
=РУБЛЬ(("4/"&A1)/7+ОСТАТ(19*ОСТАТ(А1;19)-7;30)*14%;)*7-6.
Вот еще одна формула — она короче, но не менее замысловатая:
=0КРВНИЗ(ДЕНЬ(МИНУТЫ(A1/38)/2+56)&/5/"&A1;7)-34
День памяти
Последним понедельником в мае является День памяти. Следующая формула вычисляет День памяти для года из ячейки А1:
=ДАТА(А1;6;1)+ЕСЛИ(2<ДЕНЬНЕД(ДАТА(А1;6;1));7-ДЕНЬНЕД(ДАТА(А1;6;1))+2;2-ДЕНЬНЕД(ДАТА(А1;6;1)))+((1-1)*7)-7
Обратите внимание, что формула вычисляет первый понедельник в июне, а затем отнимает значение 7 от результата, возвращая таким образом последний понедельник мая.
День независимости
Праздник День независимости США всегда выпадает на 4 июля: =ДАТА(А1;7;4)
День труда
День труда отмечают в первый понедельник сентября. Следующая формула вычисляет День труда для года, указанного в ячейке А1:
=ДАТА(А1;9;1)+ЕСЛИ(2<ДЕНЬНЕД(ДАТА(А1;9;1));7-ДЕНЬНЕД(ДАТА(А1;9;1))+2;2-ДЕНЬНЕД(ДАТА(А1;9;1)))+((1-1)*7)
День Колумба
Такой праздник, как День Колумба, выпадает на второй понедельник октября. Следующая формула вычисляет День Колумба для года из ячейки А1:
=ДАТА(А1;10;1)+ЕСЛИ(2<ДЕНЬНЕД(ДАТА(А1;10;1));7-ДЕНЬНЕД(ДАТАСА1;10;1))+2;2-ДЕНЬНЕД(ДАТА(А1;10;1)))+((2-1)*7)
День ветеранов
Праздник День ветеранов всегда приходится на 11 ноября: =ДАТА(A1;11;11)
День благодарения
День благодарения отмечается в четвертый четверг ноября. Следующая формула вычисляет День благодарения для года, указанного в ячейке А1:
=ДАТА(А1;11;1)+ЕСЛИ(5<ДЕНЬНЕД(ДАТА(А1;11;1));7-ДЕНЬНЕД(ДАТА(А1;11;1))+5;5-ДЕНЬНЕД(ДАТА(А1;11;1)))+((4-1)*7)
Рождество
Католическое Рождество всегда приходится на 25 декабря: =ДАТА(А1:12;25)
Предположим, что счет за продукцию нужно выставлять только в рабочие дни, несмотря на дату доставки. Напишем формулу, которая определяет: если дата доставки попадает на выходной или праздничный день, то дата счета – следующий понедельник (или прошедшая пятница). Решение также учитывает рабочие субботы.
Решение этой задачи без учета праздников и переносов рассмотрено в статье
Ближайший рабочий день
.
В этой статье добавим два перечня дат:
Праздники
, т.е. праздничные дни, которые попадают на рабочие и
Рабочие_дни
, т.е. перенесенные рабочие дни, которые попадают на субботу. Перечни разместим на листе
Календарь
(см.
Файл примера
).
Создадим через
Диспетчер имен
(
) два
динамических диапазона
:
Праздники
=СМЕЩ(календарь!$A$3;;;СЧЁТЗ(календарь!$A$3:$A$31))
и
Рабочие_дни
=СМЕЩ(календарь!$D$3;;;СЧЁТЗ(календарь!$D$3:$D$31))
Если дата попадает на субботу (не рабочую), воскресенье или праздник, то нижеследующая формула возвращает дату
следующего понедельника, точнее следующего рабочего дня
:
=ЕСЛИ(ЕНД(ВПР(A2;Рабочие_дни;1;ЛОЖЬ));РАБДЕНЬ(A2-1;1;Праздники);A2)
Другая формула возвращает дату
прошедшей пятницы
,
точнее последнего рабочего дня перед датой
=ЕСЛИ(ЕНД(ВПР(A2;Рабочие_дни;1;ЛОЖЬ)); ЕСЛИ(НЕ(ЕНД(ВПР(A2-1;Рабочие_дни;1;ЛОЖЬ)));A2-1;РАБДЕНЬ(A2+1;-1;Праздники)); A2)
