Přejít k hlavnímu obsahu

Jak zkontrolovat, zda je datum státním svátkem a počítat dny kromě svátků v aplikaci Excel?

Například máte seznam dat a chcete zkontrolovat, zda v seznamu existují nějaké svátky, jak byste to mohli udělat rychle? V tomto článku vám ukážu způsoby, jak vypočítat všechna data amerických státních svátků v daných letech, a poté snadno zkontrolovat, zda je datum americkým státním svátkem v aplikaci Excel.


Část 1: Výpočet státních svátků s daným rokem v aplikaci Excel

Než zkontrolujete, zda je datum státním svátkem, musíte v aplikaci Excel uvést seznam všech svátků za dané roky. Proto vám příprava tabulky podle následujícího snímku obrazovky usnadní práci.

kontrola dokladů o státních svátcích 1

Existují tři druhy amerických státních svátků:

(1) Prvním druhem je státní svátek ve stanovený den, například Nový rok je 1. ledna. můžeme snadno vypočítat Nový rok podle vzorce = DATUM (daný rok, 1,1);

(2) Druhým druhem je státní svátek ve stanovený pracovní den, například Den prezidenta. Den prezidenta můžeme snadno vypočítat podle vzorce = DATUM (daný rok, 1,1) + 14 + VÝBĚR (WEEKDAY (DATUM (daný rok, 1,1)), 1,0,6,5,4,3,2);

(3) A posledním druhem je Memorial Day, můžeme snadno vypočítat Memorial Day podle vzorce = DATE (daný rok, 6,1) -WEEKDAY (DATE (daný rok, 6,6)).

Zde uvádíme seznam vzorců pro výpočet všech svátků v následující tabulce. Stačí zadat vzorce do správné buňky a stisknout vstoupit klíč jeden po druhém.

Dovolená Buňka Vzorce
Nový rok C2 = DATUM (C1,1,1)
Martin Luther King Jr. den C3 = DATUM (C1,1,1) + 14 + VÝBĚR (WEEKDAY (DATUM (C1,1,1)), 1,0,6,5,4,3,2)
Den prezidenta C4 = DATUM (C1,2,1) + 14 + VÝBĚR (WEEKDAY (DATUM (C1,2,1)), 1,0,6,5,4,3,2)
Memorial Day C5 = DATE (C1,6,1) -WEEKDAY (DATE (C1,6,6))
Den nezávislosti C6 = DATUM (C1,7,4)
Svátek práce C7 = DATUM (C1,9,1) + VÝBĚR (WEEKDAY (DATUM (C1,9,1)), 1,0,6,5,4,3,2)
Columbus Day C8 = DATUM (C1,10,1) + 7 + VÝBĚR (WEEKDAY (DATUM (C1,10,1)), 1,0,6,5,4,3,2)
Den veteránů C9 = DATUM (C1,11,11)
Den díkůvzdání C10 = DATUM (C1,11,1) + 21 + VÝBĚR (WEEKDAY (DATUM (C1,11,1)), 4,3,2,1,0,6,5)
Štědrý den C11 = DATUM (C1,12,25)

Poznámka: Ve vzorcích v tabulce výše je C1 referenční buňkou, která lokalizuje daný rok. V našem příkladu to znamená rok 2015 a můžete jej změnit podle svých potřeb.

S těmito vzorci můžete snadno vypočítat data svátků s danými roky. Viz snímek obrazovky níže:

kontrola dokladů o státních svátcích 2

Uložte rozsah jako položku automatického textu (zbývající formáty buněk a vzorce) pro budoucí použití

Musí být velmi zdlouhavé odkazovat buňky a používat vzorce pro výpočet každé dovolené. Kutools pro Excel poskytuje roztomilé řešení Automatický text nástroj pro uložení rozsahu jako položku automatického textu, která může zůstat formátem buňky a vzorci v rozsahu. A pak tento rozsah znovu použijete jediným kliknutím. Práce je snadná pouhým jedním kliknutím, vložíte tuto tabulku a změníte rok v této tabulce!


ad auto americké svátky 1

Kutools pro Excel - Supercharge Excel s více než 300 základními nástroji. Užijte si plnohodnotnou 30denní zkušební verzi ZDARMA bez nutnosti kreditní karty! Get It Now

Část 2: Zkontrolujte, zda je datum v aplikaci Excel svátkem

Po uvedení všech dat svátků konkrétních let můžeme pomocí vzorců v aplikaci Excel snadno zkontrolovat, zda je datum svátkem nebo ne. Předpokládejme, že máte seznam dat, jak ukazuje následující snímek obrazovky, a já vám představím způsoby, jak to snadno udělat.

Vyberte prázdnou buňku kromě seznamu dat, říká buňka B18, zadejte vzorec = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Prázdniny", "Ne") do něj a poté přetáhněte rukojeť výplně do požadovaného rozsahu. Viz snímek obrazovky výše:

Poznámky:

(1) Ve vzorci = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Prázdniny", "Ne"), je $ C $ 2: $ D $ 11 rozsah státních svátků v konkrétních letech a A18 je buňka s datem, které chcete zkontrolovat, zda je svátek, a můžete je změnit podle svých potřeb. A tento vzorec vrátí „svátek“, pokud je konkrétním datem státní svátek, a vrátí „ne“, pokud tomu tak není.

(2) Můžete také použít tento vzorec pole = IF (NEBO ($ C $ 2: $ D $ 11 = A18), "Prázdniny", "NE") zkontrolovat, zda je odpovídající datum svátek nebo ne.


Část 3: Počítání dnů mezi dvěma dny kromě víkendů a svátků v aplikaci Excel

V části 1 jsme uvedli všechny svátky v daném roce a nyní vás tato metoda provede počítáním počtu dnů kromě všech víkendů a svátků v časovém období.

Vyberte prázdnou buňku, kterou vrátíte počet dní, a zadejte vzorec = NETWORKDAYS (E1, E2, B2: B10) do něj a stiskněte vstoupit klíč.

Poznámka: Ve výše uvedených buňkách je E1 počátečním datem zadaného časového období, E2 je koncovým datem a B2: B10 je seznam svátků, který počítáme v části 1.

kontrola dokladů o státních svátcích 6

Nyní získáte počet dní kromě víkendů a svátků ve stanoveném časovém období.

Kopírujte vzorce přesně / staticky bez změny odkazů na buňky v aplikaci Excel

Kutools pro Excel Přesná kopie nástroj vám pomůže snadno kopírovat více vzorců přesně bez změny odkazů na buňky v aplikaci Excel, což zabrání automatické aktualizaci relativních odkazů na buňky.


vzorce pro přesnou kopii reklamy 3

Kutools pro Excel - Supercharge Excel s více než 300 základními nástroji. Užijte si plnohodnotnou 30denní zkušební verzi ZDARMA bez nutnosti kreditní karty! Get It Now

Nejlepší nástroje pro produktivitu v kanceláři

Populární funkce: Najít, zvýraznit nebo identifikovat duplikáty   |  Odstranit prázdné řádky   |  Kombinujte sloupce nebo buňky bez ztráty dat   |   Kolo bez vzorce ...
Super vyhledávání: Více kritérií VLookup    VLookup s více hodnotami  |   VLookup na více listech   |   Fuzzy vyhledávání ....
Pokročilý rozevírací seznam: Rychle vytvořte rozevírací seznam   |  Závislý rozbalovací seznam   |  Vícenásobný výběr rozevíracího seznamu ....
Správce sloupců: Přidejte konkrétní počet sloupců  |  Přesunout sloupce  |  Přepnout stav viditelnosti skrytých sloupců  |  Porovnejte rozsahy a sloupce ...
Doporučené funkce: Zaměření mřížky   |  Návrhové zobrazení   |   Velký Formula Bar    Správce sešitů a listů   |  Knihovna zdrojů (Automatický text)   |  Výběr data   |  Zkombinujte pracovní listy   |  Šifrovat/dešifrovat buňky    Odesílat e-maily podle seznamu   |  Super filtr   |   Speciální filtr (filtr tučné/kurzíva/přeškrtnuté...) ...
Top 15 sad nástrojů12 Text Tools (doplnit text, Odebrat znaky, ...)   |   50+ Graf Typ nemovitosti (Ganttův diagram, ...)   |   40+ Praktické Vzorce (Vypočítejte věk na základě narozenin, ...)   |   19 Vložení Tools (Vložte QR kód, Vložit obrázek z cesty, ...)   |   12 Konverze Tools (Čísla na slova, Přepočet měny, ...)   |   7 Sloučit a rozdělit Tools (Pokročilé kombinování řádků, Rozdělit buňky, ...)   |   ... a více

Rozšiřte své dovednosti Excel pomocí Kutools pro Excel a zažijte efektivitu jako nikdy předtím. Kutools for Excel nabízí více než 300 pokročilých funkcí pro zvýšení produktivity a úsporu času.  Kliknutím sem získáte funkci, kterou nejvíce potřebujete...

karta kte 201905


Office Tab přináší do Office rozhraní s kartami a usnadňuje vám práci

  • Povolte úpravy a čtení na kartách ve Wordu, Excelu, PowerPointu, Publisher, Access, Visio a Project.
  • Otevřete a vytvořte více dokumentů na nových kartách ve stejném okně, nikoli v nových oknech.
  • Zvyšuje vaši produktivitu o 50%a snižuje stovky kliknutí myší každý den!
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