Как проверить, является ли дата государственным праздником, и подсчитать количество дней, исключая праздники, в 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 — это ячейка с датой, которую вы хотите проверить на предмет того, является ли она государственным праздником, и вы можете изменить их в зависимости от ваших потребностей. Эта формула вернет "Holiday", если указанная дата является государственным праздником, и вернет "No", если это не так.

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

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

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

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

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