Как да проверя дали дадена дата е официален празник и да броим дни с изключение на празниците в Excel?
Например, имате списък с дати и искате да проверите дали има официални празници в списъка, как можете да го направите бързо? В тази статия ще ви покажа начините за изчисляване на всички дати на американски официални празници в рамките на дадени години и след това ще проверя дали дадена дата е американски празник лесно в Excel.
- Част 1: Изчисляване на официални празници в рамките на дадени години в Excel
- Част 2: Проверете дали дадена дата е официален празник в Excel
- Част 3: Преброяване на дни между два дни с изключение на почивните дни и празниците в 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)).
Тук изброявам формули за изчисляване на всички официални празници в следната таблица. Просто въведете формулите в правилната клетка и натиснете Въведете ключ един по един.
Празник | Клетка | формули |
Нова година | C2 | =ДАТА(C1,1,1;XNUMX;XNUMX) |
Ден на Мартин Лутър Кинг | C3 | =ДАТА(C1,1,1)+14+ИЗБОР(ДЕЛНИЦА(ДАТА(C1,1,1)),1,0,6,5,4,3,2) |
Ден на президента | C4 | =ДАТА(C1,2,1)+14+ИЗБОР(ДЕЛНИЦА(ДАТА(C1,2,1)),1,0,6,5,4,3,2) |
Ден на загиналите във войните | C5 | =ДАТА(C1,6,1)-ДЕЛНИЦА(ДАТА(C1,6,6)) |
Ден на Независимостта | C6 | =ДАТА(C1,7,4;XNUMX;XNUMX) |
Ден на труда | C7 | =ДАТА(C1,9,1)+ИЗБЕРИ(ДЕЛНИЦА(ДАТА(C1,9,1)),1,0,6,5,4,3,2) |
Денят на Колумб | C8 | =ДАТА(C1,10,1)+7+ИЗБОР(ДЕЛНИЦА(ДАТА(C1,10,1)),1,0,6,5,4,3,2) |
Ден на ветераните | C9 | =ДАТА(C1,11,11;XNUMX;XNUMX) |
Ден на благодарността | C10 | =ДАТА(C1,11,1)+21+ИЗБОР(ДЕЛНИЦА(ДАТА(C1,11,1)),4,3,2,1,0,6,5) |
Коледа | C11 | =ДАТА(C1,12,25;XNUMX;XNUMX) |
Забележка: Във формулите в горната таблица C1 е референтната клетка, която намира дадената година. В нашия пример това означава година 2015 и можете да го промените според вашите нужди.
С тези формули можете лесно да изчислите датите на официалните празници с дадени години. Вижте екранна снимка по-долу:
Запазете диапазон като запис в автоматичен текст (останалите клетъчни формати и формули) за повторно използване в бъдеще
Трябва да е много досадно да препращате клетки и да прилагате формули за изчисляване на всеки празник. Kutools за Excel предоставя сладко решение на автотекст помощна програма за запазване на диапазона като запис в автоматичен текст, който може да запази клетъчните формати и формулите в диапазона. И тогава ще използвате повторно този диапазон само с едно кликване. Работата става лесна само с едно кликване, за да вмъкнете тази таблица и да промените годината в тази таблица!
Kutools за Excel - Заредете Excel с над 300 основни инструмента. Насладете се на пълнофункционален 30-дневен БЕЗПЛАТЕН пробен период без кредитна карта! Вземи Го Сега
Част 2: Проверете дали дадена дата е официален празник в Excel
След като изброим всички дати на официални празници за определени години, можем лесно да проверим дали дадена дата е празнична или не с формули в Excel. Да предположим, че имате списък с дати, както е показано на следната екранна снимка, и аз ще ви представя начините да го направите лесно.
Изберете празна клетка освен списъка с дати, казва Cell B18, въведете формулата =АКО(COUNTIF($C$2:$D$11,A18),"Ваканция","Не") в него и след това плъзнете манипулатора за запълване до диапазона, от който се нуждаете. Вижте екранната снимка по-горе:
бележки:
(1) Във формулата =IF(COUNTIF($C$2:$D$11,A18),"Holiday","No"), $C$2:$D$11 е диапазонът от официални празници през определени години и A18 е клетката с дата, която искате да проверите дали е официален празник, и можете да ги промените според вашите нужди. И тази формула ще върне „Празник“, ако конкретната дата е официален празник, и ще върне „Не“, ако не е.
(2) Можете също да приложите тази формула за масив =АКО(ИЛИ($C$2:$D$11=A18),"Ваканция","НЕ") за да проверите дали съответната дата е празнична или не.
Част 3: Преброяване на дни между два дни с изключение на почивните дни и празниците в Excel
В част 1 сме изброили всички празници в дадена година и сега този метод ще ви насочи да преброите броя на дните с изключение на всички почивни дни и празници в даден период от време.
Изберете празна клетка, която ще върне броя на дните, и въведете формулата =МРЕЖИ ДНИ(E1,E2,B2:B10) в него и натиснете Въведете ключ.
Забележка: В горните клетки E1 е началната дата на определен период от време, E2 е крайната дата и B2:B10 е списъкът с празници, който изчисляваме в част 1.
Сега ще получите броя на дните с изключение на почивните дни и празниците в посочения период от време.
Копирайте формулите точно/статично, без да променяте препратките към клетки в Excel
Kutools за Excel Точно копие помощната програма може да ви помогне лесно да копирате множество формули точно, без да променяте препратките към клетки в Excel, предотвратявайки автоматичното актуализиране на относителните препратки към клетки.
Kutools за Excel - Заредете Excel с над 300 основни инструмента. Насладете се на пълнофункционален 30-дневен БЕЗПЛАТЕН пробен период без кредитна карта! Вземи Го Сега
Най-добрите инструменти за продуктивност в офиса
Усъвършенствайте уменията си за Excel с Kutools за Excel и изпитайте ефективност, както никога досега. Kutools за Excel предлага над 300 разширени функции за повишаване на производителността и спестяване на време. Щракнете тук, за да получите функцията, от която се нуждаете най-много...
Раздел Office Внася интерфейс с раздели в Office и прави работата ви много по-лесна
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!