Перейти до основного матеріалу

Як перевірити, чи є дата святковим днем, і чи вважати дні, крім святкових днів, у Excel?

Наприклад, у вас є список дат і ви хочете перевірити, чи є в цьому списку якісь святкові дні, як ви могли це зробити швидко? У цій статті я покажу вам способи обчислення всіх дат американських святкових днів протягом певних років, а потім перевіряю, чи є ця дата американським святковим днем ​​у Excel.


Частина 1: Обчислити державні свята із заданим роком у Excel

Перш ніж перевіряти, чи є дата святковим днем, ви повинні перерахувати всі свята протягом певного року в Excel. Тому підготовка таблиці, як показано на наступному знімку екрану, полегшить вам роботу.

doc перевірити, чи державне свято 1

Є три види американських святкових свят:

(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)
День Мартіна Лютера Кінга 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)
День праці 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)
День подяки C10 = ДАТА (C1,11,1) + 21 + ВИБІР (ВИХОДНИЙ (ДЕНЬ (C1,11,1)), 4,3,2,1,0,6,5)
Різдво C11 = ДАТА (C1,12,25)

Примітка: У формулах, наведених у наведеній вище таблиці, C1 - це еталонна комірка, яка визначає даний рік. У нашому прикладі це означає 2015 рік, і ви можете змінити його відповідно до своїх потреб.

За допомогою цих формул ви можете легко розрахувати дати святкових днів із заданими роками. Дивіться знімок екрана нижче:

doc перевірити, чи державне свято 2

Збережіть діапазон як запис автотексту (інші формати комірок та формули) для повторного використання в майбутньому

Посилання на комірки та застосування формул для розрахунку кожного свята має бути дуже нудним. Kutools для Excel пропонує гарний обхідний шлях Автотекст утиліта для збереження діапазону як запису автотексту, який може залишати формати комірок та формули в діапазоні. І тоді ви повторно використаєте цей діапазон лише одним клацанням миші. Робота стає простою, лише одним клацанням миші можна вставити цю таблицю та змінити рік у цій таблиці!


оголошення авто американські свята 1

Kutools для Excel - Доповніть Excel понад 300 основними інструментами. Насолоджуйтесь повнофункціональною 30-денною БЕЗКОШТОВНОЮ пробною версією без кредитної картки! Get It Now

Частина 2: Перевірте, чи є дата в Excel святковим днем

Перерахувавши всі дати державних свят певних років, ми можемо легко перевірити, чи є дата святковим чи ні, за допомогою формул у Excel. Припустимо, у вас є список дат, як показано на наступному знімку екрана, і я представив способи, як легко це зробити.

Виберіть порожню клітинку, крім списку дат, говорить комірка B18, введіть формулу = IF (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

У першій частині ми перерахували всі свята в певному році, і тепер цей метод допоможе вам підрахувати кількість днів, крім усіх вихідних та святкових днів у діапазоні дат.

Виберіть порожню комірку, для якої ви повернете кількість днів, і введіть формулу = МЕРЕЖІ (E1, E2, B2: B10) в неї та натисніть Що натомість? Створіть віртуальну версію себе у ключ

примітки: У клітинках вище E1 - дата початку зазначеного діапазону дат, E2 - дата закінчення, а B2: B10 - список відпусток, який ми обчислюємо в Частині 1.

doc перевірити, чи державне свято 6

Тепер ви отримаєте кількість днів, крім вихідних та святкових днів у вказаному діапазоні дат.

Копіюйте формули точно / статично, не змінюючи посилання на комірки в Excel

Kutools для Excel Точна копія Утиліта може допомогти вам легко точно скопіювати кілька формул, не змінюючи посилання на комірки в Excel, запобігаючи автоматичному оновленню відносних посилань на комірки.


формули точної копії оголошення 3

Kutools для Excel - Доповніть Excel понад 300 основними інструментами. Насолоджуйтесь повнофункціональною 30-денною БЕЗКОШТОВНОЮ пробною версією без кредитної картки! Get It Now

Найкращі інструменти продуктивності офісу

Популярні функції: Знайдіть, виділіть або визначте дублікати   |  Видалити порожні рядки   |  Об’єднайте стовпці або клітинки без втрати даних   |   Раунд без Формули ...
Супер пошук: VLookup за кількома критеріями    Багатозначний VLookup  |   VLookup на кількох аркушах   |   Нечіткий пошук ....
Розширений розкривний список: Швидке створення випадаючого списку   |  Залежний спадний список   |  Виберіть розкривний список, що вибирається ....
Менеджер колонок: Додайте конкретну кількість стовпців  |  Перемістити стовпці  |  Перемкнути статус видимості прихованих стовпців  |  Порівняйте діапазони та стовпці ...
Особливості: Фокус сітки   |  Перегляд дизайну   |   Велика панель формул    Диспетчер робочих книг і аркушів   |  Бібліотека ресурсів (автотекст)   |  Вибір дати   |  Об’єднайте робочі аркуші   |  Шифрування/розшифрування клітинок    Надсилайте листи за списком   |  Супер фільтр   |   Спеціальний фільтр (фільтр жирний/курсив/закреслений...) ...
Топ-15 наборів інструментів12 текст Tools (додати текст, Видалити символи, ...)   |   50 + Графік типи (діаграма Ганта, ...)   |   40+ Практичний Формули (Розрахуйте вік на основі дня народження, ...)   |   19 вставка Tools (Вставте QR-код, Вставити зображення зі шляху, ...)   |   12 Перетворення Tools (Числа до слів, Валютна конверсія, ...)   |   7 Злиття та розділення Tools (Розширені комбіновані ряди, Розділені клітини, ...)   |   ... і більше

Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу.  Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...

вкладка kte 201905


Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу

  • Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
  • Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
  • Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
Comments (9)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Like the Observed Holiday, I need to also recognize additional days off outside of the Holiday or observed holidays. Example: If Christmas is on a Friday, I need to calculate the days before and after (until New Years) off.
This comment was minimized by the moderator on the site
How would you calculate 15 calendar days from a given date including weekends but excluding holidays using a list/table of holiday dates?
This comment was minimized by the moderator on the site
How to make it show the actual name of the holiday instead of just "holiday"?
This comment was minimized by the moderator on the site
Hi KC,
You can change the text “holiday” to INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)) in the formula, and the whole formula will be changed to
=IF(COUNTIF($C$2:$C$11,A18),INDEX($A$1:$A$11,MATCH(A18,$C$1:$C$11,0)),"No")

Please note that the dates you will check should be placed in one column.
This comment was minimized by the moderator on the site
Thanks, I have worked out a system to determine whether a public holiday is a weekday, but this also gives an alternative. The problem is that if one does it per month, then there are gaps between days where public holidays occur during weekdays. An example as below taking part of December 2017. the figures to immediate right of dates (Col B) are the WEEKDAY values. If date falls on a Saturday or Sunday (value 6 or 7) then the C Column reflects a blank cell ("") if a weekday the Cell has a "1", if a Public Holiday during a weekday then a "0" 21/12/2017 4 1 22/12/2017 5 1 23/12/2017 6 24/12/2017 7 25/12/2017 1 0 26/12/2017 2 0 27/12/2017 3 1 28/12/2017 4 1 29/12/2017 5 1 30/12/2017 6 31/12/2017 7 I can then sort manually using the Filter approach to get the 1's in one continuous column of rows without the blanks or 0's. Copy and paste to a worksheet where I can import the data into the temperature charts. I am trying to get the filter section automated either via formula by deleting all the 0's and blank cells with the resultant shifting up of cells containing the 1's, or via VBA. The ultimate prize would be combining the steps in Column A and Column C into one formula. The end game is to populate a temperature chart with the workday name and in the next corresponding row the day of the required month Mon Tue Thu Fri 7 8 10 11 Using August as an example where the 9th is a public holiday that falls during a work day, resulting in the data relating to the Wed being removed and the rest of the column shifting up one (or more) places. Then transposed into the above cells. I hope I am explaining with sufficient clarity :-)
This comment was minimized by the moderator on the site
How could I make this work for Federal Holiday? Meaning if the date of a holiday happens to fall on a weekend then the Federal holiday would either be Friday or Monday.
This comment was minimized by the moderator on the site
I used the formulas above to calculate the actual day of the holiday and made a second column for Observed holiday. I made this formula to accomplish this: =IF((WEEKDAY(B15))=1,B15+1,IF((WEEKDAY(B15))=7,B15-1,B15)). The cell reference B15 is referring to the holiday which is in the actual holiday column, in this case New Years Day. When the actual holiday falls on a Saturday, the Observed holiday will be listed as Friday and for actual holidays falling on Sunday, the observed holiday will be listed as Monday. Hope this helps.
This comment was minimized by the moderator on the site
This is an accurate function which will work for New Years Day that would fall on a weekend (years 2022 and 2023): =WORKDAY(DATE(CalendarYear,1,1),--(WEEKDAY(DATE(CalendarYear,1,1),2)>5))
This comment was minimized by the moderator on the site
trying to make a formula for subtracting CALENDAR DAYS and holidays. I have been able to figure out for WORKDAYS and HOLIDAY, but I cannot figure out how to do CALENDAR days and holidays. here is what I am currently using for WORKDAYS AND HOLIDAYS. Help! So I need this to be CALENDAR days instead of WORKDAYS.] =WORKDAY(B28-5,1,HOLIDAYS)
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations