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

Перед проверкой, приходится ли дата на государственный праздник, составьте в Excel список всех праздников за нужные годы. Создание таблицы, подобной приведённой на следующем снимке экрана, значительно упростит вашу задачу.

В США существует три вида государственных праздников:

• (1) Первый вид — государственные праздники с фиксированной датой, например Новый год, который всегда отмечается 1 января. Дату Нового года легко вычислить по формуле =DATE(Given Year,1,1);
• (2) Второй тип — государственные праздники, приходящиеся на определённый день недели, например День президентов. Дату этого праздника легко вычислить с помощью формулы:=DATE(Given Year,1,1)+14+CHOOSE(WEEKDAY(DATE(Given Year,1,1)),1,0,6,5,4,3,2);
• (3) И последний — День поминовения. Его дату легко вычислить по формуле =DATE(Given Year,6,1)-WEEKDAY(DATE(Given Year,6,6)).

В приведённой ниже таблице — формулы для расчёта всех государственных праздников. Просто введите их в соответствующие ячейки и последовательно нажмите клавишу Enter.

Примечание: В приведённых выше формулах ячейка C1 является опорной и содержит заданный год. В нашем примере это 2015 год — вы можете изменить его в соответствии со своими потребностями.

Эти формулы позволят вам легко рассчитать даты государственных праздников для нужных лет. См. снимок экрана ниже:

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

Выберите пустую ячейку рядом со списком дат, например ячейку B18, введите в неё формулу =IF(COUNTIF($C$2:$D$11,A18),«Holiday»,«No») и перетащите маркер заполнения на нужный диапазон. См. снимок экрана выше:

Примечания:

(1) В формуле =IF(COUNTIF($C$2:$D$11,A18),«Holiday»,«No») диапазон $C$2:$D$11 содержит список государственных праздников для конкретных годов, а ячейка A18 — дату, которую нужно проверить на совпадение с праздниками. Вы можете адаптировать эти ссылки под свои задачи. Формула вернёт «Праздник», если дата приходится на государственный праздник, и «Нет» — если не приходится.

(2) Вы также можете использовать эту формулу массива =IF(OR($C$2:$D$11=A18),«Holiday»,«NO») для проверки, приходится ли соответствующая дата на праздник.

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

Выберите пустую ячейку, в которую будет выведено количество дней, введите в неё формулу =NETWORKDAYS(E1,E2,B2:B10) и нажмите клавишу Enter.

Примечание: В приведённых выше ячейках E1 — это начальная дата диапазона (определённая дата), E2 — конечная дата, а B2:B10 — список праздников, рассчитанный в части 1.

Теперь вы получите количество дней в диапазоне «Определённая дата», исключая выходные и праздничные дни.