Tip: andere talen zijn Google-Vertaald. Je kunt het English versie van deze link.
Log in
x
or
x
x
Registreren
x

or

Hoe kan ik controleren of een datum een ​​officiële feestdag is en aantal dagen tellen, behalve feestdagen in Excel?

U hebt bijvoorbeeld een lijst met datums en wilt controleren of er feestdagen in de lijst bestaan, hoe zou u het snel gedaan kunnen krijgen? In dit artikel laat ik je de manieren zien om alle datums van Amerikaanse feestdagen in bepaalde jaren te berekenen en kijk dan of een datum een ​​Amerikaanse officiële feestdag in Excel is.

Deel 1: Bereken feestdagen in bepaalde jaren in Excel

Deel 2: Controleer of een datum een ​​feestdag is in Excel

Deel 3: tel dagen tussen twee dagen behalve in het weekend en op feestdagen in Excel

Formules exact / statisch kopiëren zonder celverwijzingen in Excel te wijzigen

Kutools for Excel Exacte kopie hulpprogramma kan u helpen om eenvoudig meerdere formules te kopiëren zonder celverwijzingen in Excel te wijzigen, waardoor wordt voorkomen dat relatieve celverwijzingen automatisch worden bijgewerkt. Volledige functie Gratis proef 60-dag!

ad exacte kopieerformules 3

Tabblad Office Schakel bewerken en browsen met tabbladen in Office in en maak uw werk veel eenvoudiger ...
Kutools voor Excel brengt 300 geavanceerde functies naar Excel en verhoogt uw productiviteit met 80%
  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Kutools werkt met Office 2007-2019 en 365. Het ondersteunt alle talen en is zeer eenvoudig te installeren of te implementeren. Volledige functionaliteit 60-daagse gratis proefversie.

pijl blauwe rechterbelDeel 1: Bereken feestdagen met een bepaald jaar in Excel

Voordat u kunt controleren of een datum een ​​feestdag is, moet u alle feestdagen binnen een aantal jaren vermelden in Excel. Daarom zal het voorbereiden van een tafel zoals in de volgende schermopname uw werk gemakkelijker maken.

doc check of feestdag 1

Er zijn drie soorten Amerikaanse feestdagen:

(1) De eerste soort is een officiële feestdag op een vaste datum, zoals Nieuwjaarsdag is op Jan 1st. we kunnen eenvoudig de nieuwjaarsdag berekenen met de formule = DATUM (Gegeven jaar, 1,1);

(2) De tweede soort is een officiële feestdag op een vaste weekdag, zoals President Day. We kunnen eenvoudig de president-dag berekenen met de formule = DATE (Gegeven jaar, 1,1) + 14 + Kiezen (WEEKDAY (DATE (Gegeven jaar, 1,1)), 1,0,6,5,4,3,2);

(3) En de laatste soort is de Memorial Day, we kunnen de Memorial Day eenvoudig berekenen met de formule = DATUM (Gegeven jaar, 6,1) -WEEKDAY (DATE (Gegeven jaar, 6,6)).

Hier noteer ik formules om alle feestdagen te berekenen in de volgende tabel. Voer de formules gewoon in de juiste cel in en druk op invoeren sleutel één voor één.

vakantie Cel Formules
Nieuwjaarsdag C2 = DATE (C1,1,1)
Martin Luther King Jr. Day C3 = DATE (C1,1,1) + + 14 CHOOSE (WEEKDAY (DATE (C1,1,1)), 1,0,6,5,4,3,2)
President Day C4 = DATE (C1,2,1) + + 14 CHOOSE (WEEKDAY (DATE (C1,2,1)), 1,0,6,5,4,3,2)
Memorial Day C5 = DATE (C1,6,1) -WEEKDAY (DATE (C1,6,6))
Independence Day C6 = DATE (C1,7,4)
Dag van de Arbeid C7 = DATE (C1,9,1) + CHOOSE (WEEKDAY (DATE (C1,9,1)), 1,0,6,5,4,3,2)
Columbus Day C8 = DATE (C1,10,1) + + 7 CHOOSE (WEEKDAY (DATE (C1,10,1)), 1,0,6,5,4,3,2)
Veterans Day C9 = DATE (C1,11,11)
Thanksgiving Day C10 = DATE (C1,11,1) + + 21 CHOOSE (WEEKDAY (DATE (C1,11,1)), 4,3,2,1,0,6,5)
Kerstdag C11 = DATE (C1,12,25)

Let op: In de formules in bovenstaande tabel is de C1 de referentiecel die het gegeven jaar lokaliseert. In ons voorbeeld betekent dit Year 2015, en u kunt het veranderen op basis van uw behoeften.

Met deze formules kunt u eenvoudig de datums van de feestdagen met bepaalde jaren berekenen. Zie screenshot hieronder:

doc check of feestdag 2


Sla een bereik op als AutoTekst-invoer (resterende celindelingen en formules) voor hergebruik in de toekomst

Het moet erg vervelend zijn om cellen te verwijzen en formules toe te passen voor het berekenen van elke vakantie. Kutools voor Excel biedt een leuke oplossing Auto-tekst utility om het bereik op te slaan als een AutoTekst-invoer, wat de celindelingen en formules in het bereik kan blijven. En dan zult u dit bereik opnieuw gebruiken met slechts één klik. Werk wordt gemakkelijk door slechts één keer klikken om deze tabel in te voegen en het jaar in deze tabel te veranderen! Volledige functie Gratis proef 60-dag!

ad auto american holidays 1

pijl blauwe rechterbelDeel 2: Controleer of een datum een ​​feestdag is in Excel

Na het vermelden van alle datums van feestdagen van bepaalde jaren, kunnen we eenvoudig controleren of een datum een ​​officiële feestdag is of niet met formules in Excel. Stel dat je een datalijst hebt zoals de volgende schermopname wordt getoond, en ik zal de manieren introduceren om dit gemakkelijk te doen.

Selecteer een lege cel naast de datumlijst, zegt cel B18, voer de formule in = ALS (AANTAL.ALS ($ C $ 2: $ D $ 11, A18), "Holiday", "No") erin en sleep vervolgens de vulgreep naar het bereik dat u nodig hebt. Zie screenshot hierboven:

Notes:

(1) In de formule = IF (AANTAL.ALS ($ C $ 2: $ D $ 11, A18), "Feestdagen", "Nee"), is de $ C $ 2: $ D $ 11 het bereik van feestdagen in specifieke jaren, en A18 is de cel met datum die u wilt controleren of het een officiële feestdag is en u kunt deze op basis van uw behoeften wijzigen. En deze formule retourneert 'Vakantie' als de specifieke datum een ​​feestdag is en geeft 'Nee' als dit niet het geval is.

(2) U kunt deze matrixformule ook toepassen = IF (OR ($ C $ 2: $ D $ 11 = A18), "Holiday", "NO") om te controleren of de corresponderende datum vakantie is of niet.


pijl blauwe rechterbel Deel 3: tel dagen tussen twee dagen behalve in het weekend en op feestdagen in Excel

In Deel 1 hebben we alle vakanties in een bepaald jaar vermeld en nu helpt deze methode je om het aantal dagen te tellen, behalve alle weekenden en feestdagen in een periode.

Selecteer een lege cel, u zult het aantal dagen retourneren en de formule invoeren = NETTO.WERKDAGEN (E1, E2, B2: B10) erin en druk op de invoeren sleutel.

Notes: In bovenstaande cellen is E1 de begindatum van het opgegeven datumbereik, E2 is de einddatum en B2: B10 is de vakantielijst die we in deel 1 berekenen.

doc check of feestdag 6

U krijgt nu het aantal dagen behalve het weekend en de feestdagen in het opgegeven datumbereik.


Kutools voor Excel - De beste Office-productiviteitstool Verhoog uw productiviteit met 80%

  • Super Formula Bar (bewerk eenvoudig meerdere regels tekst en formule); Lay-out lezen (gemakkelijk grote aantallen cellen lezen en bewerken); Plakken op gefilterd bereik...
  • Cellen / rijen / kolommen samenvoegen en gegevens bewaren; Inhoud gesplitste cellen; Combineer dubbele rijen en som / gemiddelde... voorkomen dubbele cellen; Ranges vergelijken...
  • Selecteer Dupliceren of Uniek rijen; Selecteer Lege rijen (alle cellen zijn leeg); Super Find en Fuzzy Find in veel werkboeken; Willekeurig selecteren ...
  • Exacte kopie Meerdere cellen zonder formule-referentie te wijzigen; Automatisch referenties maken naar meerdere vellen; Voeg kogels toe, Selectievakjes en meer ...
  • Favoriete en snel formules invoegen, Bereiken, grafieken en afbeeldingen; Coderen van cellen met wachtwoord; Maak een mailinglijst en stuur e-mails ...
  • extract Text, Tekst toevoegen, verwijderen op positie, Verwijder de spatie; Subtotalen voor paging maken en afdrukken; Converteren tussen cellen Inhoud en opmerkingen...
  • Super filter (bewaar en pas filterschema's toe op andere bladen); Geavanceerde sortering per maand / week / dag, frequentie en meer; Speciaal filter door vet, cursief ...
  • Combineer werkmappen en werkbladen; Tabellen samenvoegen op basis van sleutelkolommen; Gegevens splitsen in meerdere bladen; Batch Converteer xls, xlsx en PDF...
  • Werkt met Office 2007-2019 en 365 en ondersteunt alle talen. Het is eenvoudig te implementeren in uw bedrijf. Volledige functionaliteit 60-daagse gratis proefversie.
kte-tab 201905

Tabblad Office Brengt interface met tabbladen naar Office en maakt uw werk veel eenvoudiger

  • Bewerken en lezen met tabbladen inschakelen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
  • Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
  • Verhoogt uw productiviteit met 50% en verlaagt dagelijks honderden muisklikken voor u!
Officetab onderaan
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    KC · 11 months ago
    How to make it show the actual name of the holiday instead of just "holiday"?
    • To post as a guest, your comment is unpublished.
      kellytte · 7 months ago
      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.
  • To post as a guest, your comment is unpublished.
    Shawn Bull · 2 years ago
    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 :-)
  • To post as a guest, your comment is unpublished.
    Mark · 2 years ago
    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.
    • To post as a guest, your comment is unpublished.
      Tboo Ted Marshall · 2 years ago
      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.
      • To post as a guest, your comment is unpublished.
        Josh · 10 months ago
        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))
  • To post as a guest, your comment is unpublished.
    LINDA · 3 years ago
    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)