Ugrás a tartalomra

Hogyan ellenőrizhető, hogy a dátum munkaszüneti nap-e, és az Excel-ben a napokat számolja-e a szünnapok kivételével?

Például van egy listája a dátumokról, és szeretné ellenőrizni, hogy vannak-e munkaszüneti napok a listában, hogyan tudná ezt gyorsan elvégezni? Ebben a cikkben megmutatom, hogyan lehet kiszámolni az amerikai munkaszüneti napok összes dátumát az adott éven belül, majd ellenőrizni, hogy egy dátum amerikai munkaszüneti nap az Excel-ben.


1. rész: Számolja ki az adott év munkaszüneti napjait az Excel programban

Mielőtt ellenőrizné, hogy egy dátum munkaszüneti nap-e, fel kell sorolnia az Excelben az adott éveken belüli összes ünnepnapot. Ezért a táblázat elkészítése az alábbi képen látható módon megkönnyíti a munkáját.

doki ellenőrizze, hogy munkaszüneti nap 1

Háromféle amerikai munkaszüneti nap létezik:

(1) Az első fajta munkaszüneti nap egy meghatározott időpontban, például az újév napja január 1-jén van. könnyen kiszámíthatjuk az újév napját a képlettel = DÁTUM (adott év, 1,1);

(2) A második fajta egy meghatározott hétköznapi munkaszüneti nap, például az elnök napja. Könnyen kiszámíthatjuk az elnök napját a képlettel = DÁTUM (adott év, 1,1) + 14 + VÁLASZTÁS (WEEKDAY (DÁTUM (adott év, 1,1)), 1,0,6,5,4,3,2);

(3) És az utolsó fajta az emléknap, könnyen kiszámíthatjuk az emléknapot a képlettel = DÁTUM (adott év, 6,1) - WEEKDAY (DÁTUM (adott év, 6,6)).

A következő táblázatban felsorolom az összes munkaszüneti nap kiszámításához szükséges képleteket. Csak írja be a képleteket a megfelelő cellába, és nyomja meg a gombot belép kulcs egyenként.

Ünnep Sejt képletek
Újév napja C2 = DÁTUM (C1,1,1)
Martin Luther King Jr. nap C3 = DÁTUM (C1,1,1) + 14 + VÁLASZTÁS (HÉTNAP (DÁTUM (C1,1,1)), 1,0,6,5,4,3,2)
Elnök napja C4 = DÁTUM (C1,2,1) + 14 + VÁLASZTÁS (HÉTNAP (DÁTUM (C1,2,1)), 1,0,6,5,4,3,2)
Háborús hősök emléknapja C5 = DÁTUM (C1,6,1) -HETI NAP (DÁTUM (C1,6,6))
Independence Day C6 = DÁTUM (C1,7,4)
Labor Day C7 = DÁTUM (C1,9,1) + VÁLASZTÁS (HÉTNAP (DÁTUM (C1,9,1)), 1,0,6,5,4,3,2)
Columbus Day C8 = DÁTUM (C1,10,1) + 7 + VÁLASZTÁS (HÉTNAP (DÁTUM (C1,10,1)), 1,0,6,5,4,3,2)
Veteránok Napja C9 = DÁTUM (C1,11,11)
Hálaadás napja C10 = DÁTUM (C1,11,1) + 21 + VÁLASZTÁS (HÉTNAP (DÁTUM (C1,11,1)), 4,3,2,1,0,6,5)
Karácsony C11 = DÁTUM (C1,12,25)

Jegyzet: A fenti táblázat képleteiben a C1 az a referenciacella, amely az adott évet megtalálja. Példánkban ez a 2015-ös évet jelenti, és igényei szerint módosíthatja.

Ezekkel a képletekkel könnyedén kiszámíthatja a munkaszüneti napok dátumát adott évekkel. Lásd az alábbi képernyőképet:

doki ellenőrizze, hogy munkaszüneti nap 2

Mentse el a tartományt AutoText bejegyzésként (a fennmaradó cellaformátumok és képletek) a későbbi újrafelhasználáshoz

Nagyon unalmas lehet a cellákra hivatkozni és képleteket alkalmazni az egyes ünnepnapok kiszámításához. A Kutools for Excel aranyos megoldást kínál a Auto szöveg segédprogrammal mentheti a tartományt AutoText bejegyzésként, amely megtarthatja a tartomány cellaformátumait és képleteit. És akkor egyetlen kattintással újra felhasználhatja ezt a tartományt. A munka egyszerűvé válik, ha csak egy kattintással beszúrja ezt a táblázatot és megváltoztatja az évet ebben a táblázatban!


ad auto amerikai ünnepek 1

Kutools az Excel számára - Töltse fel az Excelt több mint 300 alapvető eszközzel. Élvezze a teljes funkcionalitású 30 napos INGYENES próbaverziót hitelkártya nélkül! Szerezd meg most

2. rész: Ellenőrizze, hogy a dátum munkaszüneti nap-e az Excelben

Miután felsorolta az egyes évek munkaszüneti napjainak dátumát, az Excel képleteivel könnyen ellenőrizhetjük, hogy egy dátum munkaszüneti nap-e vagy sem. Tételezzük fel, hogy van egy dátumlista, amint az a következő képernyőképen látható, és bemutatom a könnyű megvalósítás módjait.

Válasszon egy üres cellát a dátumlista mellett - írja a B18 cella - írja be a képletet = HA (COUNTIF ($ C $ 2: $ D $ 11, A18), "Ünnep", "Nem") bele, majd húzza a Fill Handle-t a kívánt tartományba. Lásd a fenti képernyőképet:

Megjegyzések:

(1) Az = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No") képletben a $ C $ 2: $ D $ 11 az adott évek munkaszüneti napjai tartománya, és Az A18 az a cella, amelynek dátumával ellenőrizni szeretné, hogy munkaszüneti nap-e, és az igényei szerint módosíthatja őket. Ez a képlet pedig "Holiday" -ot ad vissza, ha a konkrét dátum munkaszüneti nap, és ha nem, akkor a "No" -ot adja vissza.

(2) Ezt a tömbképletet is alkalmazhatja = HA (VAGY ($ C $ 2: $ D $ 11 = A18), "Ünnep", "NEM") annak ellenőrzése, hogy a megfelelő dátum ünnep-e vagy sem.


3. rész: Számolja meg a két nap közötti napokat, kivéve a hétvégéket és az ünnepeket az Excel programban

Az 1. részben felsoroltuk az adott év összes ünnepét, és most ez a módszer arra készteti Önt, hogy számlálja a napok számát az összes hétvége és ünnepnap kivételével.

Válasszon egy üres cellát, amellyel megkapja a napok számát, és adja meg a képletet = HÁLÓZATI NAPOK (E1, E2, B2: B10) bele, és nyomja meg a gombot belép kulcs.

Megjegyzések: A fenti cellákban E1 a megadott dátumtartomány kezdő dátuma, E2 a befejezés dátuma és B2: B10 az ünnepi lista, amelyet az 1. részben kiszámítottunk.

doki ellenőrizze, hogy munkaszüneti nap 6

Most megkapja a megadott dátumtartományban a hétvégék és ünnepek kivételével a napok számát.

Pontosan / statikusan másolja a képleteket az Excel cellahivatkozásainak megváltoztatása nélkül

Kutools az Excel számára Pontos másolás segédprogram segítségével könnyedén másolhat több képletet pontosan az Excel cellahivatkozásainak módosítása nélkül, megakadályozva a relatív cellahivatkozások automatikus frissítését.


hirdetés pontos másolási képletei 3

Kutools az Excel számára - Töltse fel az Excelt több mint 300 alapvető eszközzel. Élvezze a teljes funkcionalitású 30 napos INGYENES próbaverziót hitelkártya nélkül! Szerezd meg most

A legjobb irodai hatékonyságnövelő eszközök

Népszerű szolgáltatások: Ismétlődések keresése, kiemelése vagy azonosítása   |  Üres sorok törlése   |  Oszlopok vagy cellák kombinálása adatvesztés nélkül   |   Kerek Formula nélkül ...
Szuper keresés: Több kritérium VLookup    Többértékű VLookup  |   VLookup több munkalapon   |   Fuzzy Lookup ....
Speciális legördülő lista: Gyors legördülő lista létrehozása   |  Függő legördülő lista   |  Többszörösen válassza ki a legördülő listát ....
Oszlopkezelő: Adjon meg egy adott számú oszlopot  |  Oszlopok mozgatása  |  Kapcsolja be a Rejtett oszlopok láthatósági állapotát  |  Tartományok és oszlopok összehasonlítása ...
Kiemelt funkciók: Rács fókusz   |  Design nézet   |   Nagy Formula bár    Munkafüzet és lapkezelő   |  Erőforrás-könyvtár (Auto szöveg)   |  Dátumválasztó   |  Kombinálja a munkalapokat   |  Cellák titkosítása/dekódolása    E-mailek küldése listánként   |  Szuper szűrő   |   Speciális szűrő (félkövér/dőlt/áthúzott szűrés...) ...
A 15 legjobb eszközkészlet12 szöveg Eszközök (Szöveg hozzáadása, Karakterek eltávolítása,...)   |   50 + Táblázatos Típusai (Gantt-diagram,...)   |   40+ Praktikus képletek (Számolja ki az életkort a születésnap alapján,...)   |   19 beszúrás Eszközök (Helyezze be a QR-kódot, Kép beszúrása az útvonalból,...)   |   12 Átalakítás Eszközök (Számok szavakig, Valuta átváltás,...)   |   7 Egyesítés és felosztás Eszközök (Haladó kombinált sorok, Hasított sejtek,...)   |   ... és több

Töltsd fel Excel-készségeidet a Kutools for Excel segítségével, és tapasztald meg a még soha nem látott hatékonyságot. A Kutools for Excel több mint 300 speciális funkciót kínál a termelékenység fokozásához és az időmegtakarításhoz.  Kattintson ide, hogy megszerezze a leginkább szükséges funkciót...

kte lap 201905


Az Office lap füles felületet hoz az Office-ba, és sokkal könnyebbé teszi a munkáját

  • Füles szerkesztés és olvasás engedélyezése Wordben, Excelben és PowerPointban, Publisher, Access, Visio és Project.
  • Több dokumentum megnyitása és létrehozása ugyanazon ablak új lapjain, mint új ablakokban.
  • 50% -kal növeli a termelékenységet, és naponta több száz kattintással csökkenti az egér kattintását!
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