Gå til hovedindhold

Hvordan kontrolleres, om en dato er helligdag og tæller dage undtagen helligdage i Excel?

For eksempel har du en liste med datoer og vil kontrollere, om der findes helligdage på listen, hvordan kan du få det gjort hurtigt? I denne artikel vil jeg vise dig måderne til at beregne alle datoer for amerikanske helligdage inden for givne år og derefter kontrollere, om en dato let er en amerikansk helligdag i Excel.


Del 1: Beregn helligdage med givet år i Excel

Før du kontrollerer, om en dato er en helligdag, skal du angive alle helligdage inden for givne år i Excel. Derfor gør det lettere for dig at forberede en tabel som vist på skærmbilledet.

dok. tjek om offentlig helligdag 1

Der er tre slags amerikanske helligdage:

(1) Den første slags er en helligdag på en fast dato, såsom nytårsdag er den 1. januar. vi kan let beregne nytårsdagen med formlen = DATO (givet år, 1,1);

(2) Den anden slags er en helligdag på en fast hverdag, såsom præsidentens dag. Vi kan let beregne præsidentens dag med formlen = DATO (givet år, 1,1) + 14 + VÆLG (UDAG (DATO (givet år, 1,1)), 1,0,6,5,4,3,2);

(3) Og den sidste slags er Memorial Day, vi kan let beregne Memorial Day med formlen = DATO (givet år, 6,1) -UGEDAG (DATO (givet år, 6,6)).

Her viser jeg formler til beregning af alle helligdage i følgende tabel. Indtast bare formlerne i den rigtige celle, og tryk på Indtast nøgle en efter en.

Ferie Cell formler
Nytårsdag C2 = DATO (C1,1,1)
Martin Luther King Jr. Day C3 = DATO (C1,1,1) + 14 + VÆLG (UKEDAG (DATO (C1,1,1)), 1,0,6,5,4,3,2)
Præsidentens dag C4 = DATO (C1,2,1) + 14 + VÆLG (UKEDAG (DATO (C1,2,1)), 1,0,6,5,4,3,2)
Memorial Day C5 = DATO (C1,6,1) -UGEDAG (DATO (C1,6,6))
Independence Day C6 = DATO (C1,7,4)
Labor Day C7 = DATO (C1,9,1) + VÆLG (UDAG (DATO (C1,9,1)), 1,0,6,5,4,3,2)
Columbus Day C8 = DATO (C1,10,1) + 7 + VÆLG (UKEDAG (DATO (C1,10,1)), 1,0,6,5,4,3,2)
Veterans Day C9 = DATO (C1,11,11)
Thanksgiving Day C10 = DATO (C1,11,1) + 21 + VÆLG (UKEDAG (DATO (C1,11,1)), 4,3,2,1,0,6,5)
Juledag C11 = DATO (C1,12,25)

Bemærk: I formlerne i ovenstående tabel er C1 referencecellen, der lokaliserer det givne år. I vores eksempel betyder det år 2015, og du kan ændre det ud fra dine behov.

Med disse formler kan du nemt beregne datoer for helligdage med givne år. Se skærmbillede nedenfor:

dok. tjek om offentlig helligdag 2

Gem et interval som AutoText-post (resterende celleformater og formler) til genbrug i fremtiden

Det skal være meget kedeligt at henvise celler og anvende formler til beregning af hver ferie. Kutools til Excel giver en sød løsning på Autotekst værktøj til at gemme området som en AutoText-post, som kan forblive celleformaterne og formlerne i området. Og så vil du genbruge dette interval med kun et klik. Arbejdet bliver let ved kun at klikke for at indsætte denne tabel og ændre året i denne tabel!


annonce auto amerikanske helligdage 1

Kutools til Excel - Supercharge Excel med over 300 vigtige værktøjer. Nyd en 30-dages GRATIS prøveperiode uden behov for kreditkort! Hent den nu

Del 2: Kontroller, om en dato er helligdag i Excel

Efter at have angivet alle datoer for helligdage for bestemte år, kan vi nemt kontrollere, om en dato er helligdag eller ej med formler i Excel. Antag at du har en datoliste som følgende skærmbillede vist, og jeg vil introducere måderne at få det gjort let.

Vælg en tom celle udover datolisten, siger Cell B18, indtast formlen = HVIS (COUNTIF ($ C $ 2: $ D $ 11, A18), "Ferie", "Nej") ind i det, og træk derefter fyldhåndtaget til det område, du har brug for. Se skærmbillede ovenfor:

Noter:

(1) I formlen = HVIS (COUNTIF ($ C $ 2: $ D $ 11, A18), "Ferie", "Nej"), er $ C $ 2: $ D $ 11 området for helligdage i bestemte år, og A18 er cellen med den dato, du vil kontrollere, om det er en helligdag, og du kan ændre dem ud fra dine behov. Og denne formel returnerer "Ferie", hvis den specifikke dato er en offentlig helligdag, og returnerer "Nej", hvis den ikke er.

(2) Du kan også anvende denne matrixformel = HVIS (ELLER ($ C $ 2: $ D $ 11 = A18), "Ferie", "NEJ") for at kontrollere, om den tilsvarende dato er ferie eller ej.


Del 3: Tæl dage mellem to dage undtagen weekender og helligdage i Excel

I del 1 har vi listet alle helligdage i et givet år, og nu vil denne metode guide dig til at tælle antallet af dage undtagen alle weekender og helligdage i et datointerval.

Vælg en tom celle, du vil returnere antallet af dage, og indtast formlen = NETVÆRKDAGER (E1, E2, B2: B10) ind i det, og tryk på Indtast nøgle.

Bemærk: I ovenstående celler er E1 startdatoen for det angivne datointerval, E2 er slutdatoen, og B2: B10 er den ferieliste, vi beregner i del 1.

dok. tjek om offentlig helligdag 6

Nu får du antallet af dage undtagen weekender og helligdage i det angivne datointerval.

Kopier formler nøjagtigt / statisk uden at ændre cellereferencer i Excel

Kutools til Excel Præcis kopi hjælpeprogram kan hjælpe dig med let at kopiere flere formler nøjagtigt uden at ændre cellereferencer i Excel og forhindre, at relative cellereferencer opdateres automatisk.


annonce nøjagtige kopi formler 3

Kutools til Excel - Supercharge Excel med over 300 vigtige værktøjer. Nyd en 30-dages GRATIS prøveperiode uden behov for kreditkort! Hent den nu

Bedste kontorproduktivitetsværktøjer

🤖 Kutools AI Aide: Revolutionér dataanalyse baseret på: Intelligent udførelse   |  Generer kode  |  Opret brugerdefinerede formler  |  Analyser data og generer diagrammer  |  Aktiver Kutools funktioner...
Populære funktioner: Find, fremhæv eller identificer dubletter   |  Slet tomme rækker   |  Kombiner kolonner eller celler uden at miste data   |   Runde uden formel ...
Super opslag: VLookup med flere kriterier    Multiple Value VLookup  |   VOpslag på tværs af flere ark   |   Fuzzy Lookup ....
Avanceret rulleliste: Opret hurtigt rulleliste   |  Afhængig rulleliste   |  Multivælg rulleliste ....
Column Manager: Tilføj et bestemt antal kolonner  |  Flyt kolonner  |  Skift synlighedsstatus for skjulte kolonner  |  Sammenlign områder og kolonner ...
Fremhævede funktioner: Grid fokus   |  Designvisning   |   Stor Formel Bar    Arbejdsbog & Ark Manager   |  Ressourcebibliotek (Autotekst)   |  Datovælger   |  Kombiner regneark   |  Krypter/Dekrypter celler    Send e-mails efter liste   |  Superfilter   |   Specielt filter (filter fed/kursiv/gennemstreget...) ...
Top 15 værktøjssæt12 tekst Værktøjer (tilføje tekst, Fjern tegn, ...)   |   50 + Chart Typer (Gantt kort, ...)   |   40+ Praktisk formler (Beregn alder baseret på fødselsdag, ...)   |   19 Indsættelse Værktøjer (Indsæt QR-kode, Indsæt billede fra sti, ...)   |   12 Konvertering Værktøjer (Tal til ord, Valutaomregning, ...)   |   7 Flet og del Værktøjer (Avancerede kombinere rækker, Opdel celler, ...)   |   ... og mere

Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid.  Klik her for at få den funktion, du har mest brug for...

Beskrivelse


Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
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