Hur kontrollerar jag om ett datum är allmän helgdag och räknar dagar utom helgdagar i Excel?
Du har till exempel en lista med datum och vill kontrollera om det finns helgdagar i listan, hur kan du få det gjort snabbt? I den här artikeln visar jag hur du kan beräkna alla datum för amerikanska helgdagar inom givna år och sedan kontrollera om ett datum är en amerikansk helgdag i Excel enkelt.
- Del 1: Beräkna helgdagar inom givna år i Excel
- Del 2: Kontrollera om ett datum är helgdag i Excel
- Del 3: Räkna dagar mellan två dagar utom helger och helgdagar i Excel
Del 1: Beräkna helgdagar med angivet år i Excel
Innan du kontrollerar om ett datum är en allmän helgdag måste du lista alla helgdagar inom givna år i Excel. Därför förbereder du ett bord enligt följande skärmdump kommer att göra ditt arbete enklare.
Det finns tre typer av amerikanska helgdagar:
(1) Den första typen är en allmän helgdag på ett fast datum, till exempel nyårsdagen är den 1 januari. vi kan enkelt beräkna nyårsdagen med formeln = DATUM (givet år, 1,1);
(2) Den andra typen är en allmän helgdag på en fast vardag, såsom presidentens dag. Vi kan enkelt beräkna presidentdagen med formeln = DATUM (given år, 1,1) + 14 + VÄLJ (VECKODAG (DATUM (given år, 1,1)), 1,0,6,5,4,3,2);
(3) Och den sista typen är Memorial Day, vi kan enkelt beräkna Memorial Day med formeln = DATUM (given år, 6,1) -WEEKDAY (DATE (given år, 6,6)).
Här listar jag formler för att beräkna alla helgdagar i följande tabell. Ange bara formlerna i rätt cell och tryck på ange nyckel en efter en.
| Semester | Cell | Formler |
| Nyårsdagen | C2 | = DATUM (C1,1,1) |
| Martin Luther King Jr. Day | C3 | = DATUM (C1,1,1) + 14 + VÄLJ (VECKODAG (DATUM (C1,1,1)), 1,0,6,5,4,3,2) |
| Presidentens dag | C4 | = DATUM (C1,2,1) + 14 + VÄLJ (VECKODAG (DATUM (C1,2,1)), 1,0,6,5,4,3,2) |
| MINNESDAGEN | C5 | = DATUM (C1,6,1) -VECKODAG (DATUM (C1,6,6)) |
| SJÄLVSTÄNDIGHETSDAGEN | C6 | = DATUM (C1,7,4) |
| Labor Day | C7 | = DATUM (C1,9,1) + VÄLJ (VECKODAG (DATUM (C1,9,1)), 1,0,6,5,4,3,2) |
| Columbus Day | C8 | = DATUM (C1,10,1) + 7 + VÄLJ (VECKODAG (DATUM (C1,10,1)), 1,0,6,5,4,3,2) |
| Veterans Day | C9 | = DATUM (C1,11,11) |
| Thanksgiving Day | C10 | = DATUM (C1,11,1) + 21 + VÄLJ (VECKODAG (DATUM (C1,11,1)), 4,3,2,1,0,6,5) |
| JULDAGEN | C11 | = DATUM (C1,12,25) |
Notera: I formlerna i ovanstående tabell är C1 referenscellen som lokaliserar det angivna året. I vårt exempel betyder det år 2015, och du kan ändra det baserat på dina behov.
Med dessa formler kan du enkelt beräkna datum för helgdagar med givna år. Se skärmdump nedan:
Spara ett intervall som AutoText-post (återstående cellformat och formler) för återanvändning i framtiden
Det måste vara mycket tråkigt att hänvisa celler och tillämpa formler för att beräkna varje helgdag. Kutools för Excel ger en söt lösning på Automatisk text verktyg för att spara intervallet som en AutoText-post, som kan förbli cellformaten och formlerna i intervallet. Och sedan kommer du att återanvända detta intervall med bara ett klick. Arbetet blir enkelt genom att bara klicka för att infoga den här tabellen och ändra året i denna tabell!
Kutools för Excel - Superladda Excel med över 300 viktiga verktyg. Njut av en fullfjädrad 30-dagars GRATIS provperiod utan behov av kreditkort! Hämta den nu
Del 2: Kontrollera om ett datum är helgdag i Excel
Efter att ha listat alla datum för helgdagar för specifika år kan vi enkelt kontrollera om ett datum är helgdag eller inte med formler i Excel. Antag att du har en datumlista som följande skärmdump visas, och jag kommer att presentera sätten att få det gjort enkelt.
Välj en tom cell förutom datumlistan, säger Cell B18, ange formeln = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No") in den och dra sedan Fill Handle till det intervall du behöver. Se skärmdump ovan:
Anmärkningar:
(1) I formeln = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No") är $ C $ 2: $ D $ 11 utbudet av allmänna helgdagar under specifika år, och A18 är cellen med datum som du vill kontrollera om det är en helgdag och du kan ändra dem baserat på dina behov. Och den här formeln returnerar "Semester" om det specifika datumet är en allmän helgdag och returnerar "Nej" om det inte är det.
(2) Du kan också använda denna matrisformel = OM (ELLER ($ C $ 2: $ D $ 11 = A18), "Holiday", "NO") för att kontrollera om motsvarande datum är semester eller inte.
Del 3: Räkna dagar mellan två dagar utom helger och helgdagar i Excel
I del 1 har vi listat alla helgdagar under ett visst år, och nu kommer den här metoden att vägleda dig att räkna antalet dagar utom alla helger och helgdagar i ett datumintervall.
Välj en tom cell som du returnerar antalet dagar och ange formeln = NÄTVERKSDAGAR (E1, E2, B2: B10) in i den och tryck på ange nyckel.
Anmärkningar: I ovanstående celler är E1 startdatumet för det angivna datumintervallet, E2 är slutdatumet, och B2: B10 är den semesterlista vi beräknar i del 1.
Nu får du antalet dagar utom helger och helgdagar i det angivna datumintervallet.
Kopiera formler exakt / statiskt utan att ändra cellreferenser i Excel
Kutools för Excel Exakt kopia verktyget kan hjälpa dig att enkelt kopiera flera formler exakt utan att ändra cellreferenser i Excel, vilket förhindrar att relativa cellreferenser uppdateras automatiskt.
Kutools för Excel - Superladda Excel med över 300 viktiga verktyg. Njut av en fullfjädrad 30-dagars GRATIS provperiod utan behov av kreditkort! Hämta den nu
Bästa kontorsproduktivitetsverktyg
Uppgradera dina Excel-färdigheter med Kutools för Excel och upplev effektivitet som aldrig förr. Kutools för Excel erbjuder över 300 avancerade funktioner för att öka produktiviteten och spara tid. Klicka här för att få den funktion du behöver mest...
Fliken Office ger ett flikgränssnitt till Office och gör ditt arbete mycket enklare
- Aktivera flikredigering och läsning i Word, Excel, PowerPoint, Publisher, Access, Visio och Project.
- Öppna och skapa flera dokument i nya flikar i samma fönster, snarare än i nya fönster.
- Ökar din produktivitet med 50 % och minskar hundratals musklick för dig varje dag!
