Hoe controleer ik of een datum een officiële feestdag is en tel dagen behalve feestdagen in Excel?
U heeft bijvoorbeeld een lijst met datums en wilt controleren of er feestdagen in de lijst staan, hoe kunt u dat dan snel doen? In dit artikel laat ik je zien hoe je alle datums van Amerikaanse feestdagen binnen een bepaald jaar kunt berekenen en vervolgens in Excel eenvoudig kunt controleren of een datum een Amerikaanse feestdag is.
- Deel 1: Bereken feestdagen binnen bepaalde jaren in Excel
- Deel 2: Controleer of een datum een officiële feestdag is in Excel
- Deel 3: tel dagen tussen twee dagen behalve weekends en feestdagen in Excel
Deel 1: Bereken feestdagen met een bepaald jaar in Excel
Voordat u controleert of een datum een officiële feestdag is, moet u alle feestdagen binnen bepaalde jaren in Excel vermelden. Daarom zal het voorbereiden van een tafel zoals in de volgende schermafbeelding uw werk gemakkelijker maken.
Er zijn drie soorten Amerikaanse feestdagen:
(1) De eerste soort is een feestdag op een vaste datum, zoals nieuwjaarsdag op 1 januari. we kunnen de nieuwjaarsdag gemakkelijk berekenen met de formule = DATUM (gegeven jaar, 1,1);
(2) De tweede soort is een feestdag op een vaste weekdag, zoals President Day. Met de formule kunnen we de President-dag gemakkelijk berekenen = DATUM (Gegeven jaar, 1,1) + 14 + KIEZEN (WEEKDAG (DATUM (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 gemakkelijk berekenen met de formule = DATUM (Gegeven jaar, 6,1) -WEEKDAG (DATUM (Gegeven jaar, 6,6)).
Hier som ik formules op om alle feestdagen te berekenen in de volgende tabel. Voer gewoon de formules in de juiste cel in en druk op Enter sleutel een voor een.
vakantie | Cel | Formules |
Nieuwjaarsdag | C2 | = DATUM (C1,1,1) |
Martin Luther King Jr. Day | C3 | = DATUM (C1,1,1) + 14 + KIES (WEEKDAG (DATUM (C1,1,1)), 1,0,6,5,4,3,2) |
Dag van de president | C4 | = DATUM (C1,2,1) + 14 + KIES (WEEKDAG (DATUM (C1,2,1)), 1,0,6,5,4,3,2) |
Memorial Day | C5 | = DATUM (C1,6,1) -WEEKDAG (DATUM (C1,6,6)) |
Independence Day | C6 | = DATUM (C1,7,4) |
Dag van de Arbeid | C7 | = DATUM (C1,9,1) + KIES (WEEKDAG (DATUM (C1,9,1)), 1,0,6,5,4,3,2) |
Columbus Day | C8 | = DATUM (C1,10,1) + 7 + KIES (WEEKDAG (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 + KIES (WEEKDAG (DATUM (C1,11,1)), 4,3,2,1,0,6,5) |
Kerstdag | C11 | = DATUM (C1,12,25) |
Opmerking: In de formules in bovenstaande tabel is de C1 de referentiecel die het gegeven jaar lokaliseert. In ons voorbeeld betekent het Jaar 2015 en u kunt het wijzigen op basis van uw behoeften.
Met deze formules kunt u gemakkelijk datums van de feestdagen met opgegeven jaren berekenen. Zie screenshot hieronder:
Sla een bereik op als AutoTekst-invoer (resterende celindelingen en formules) om in de toekomst opnieuw te gebruiken
Het moet erg vervelend zijn om cellen te verwijzen en formules toe te passen voor het berekenen van elke feestdag. Kutools voor Excel biedt een leuke oplossing voor Auto-tekst hulpprogramma om het bereik op te slaan als een AutoTekst-fragment, dat de celopmaak en formules in het bereik kan blijven. En dan hergebruik je dit bereik met slechts één klik. Werk wordt gemakkelijk door slechts één klik om deze tabel in te voegen en het jaar in deze tabel te wijzigen!
Kutools for Excel - Geef Excel een boost met meer dan 300 essentiële tools. Geniet van een GRATIS proefperiode van 30 dagen zonder creditcard! Snap het nu
Deel 2: Controleer of een datum een officiële feestdag is in Excel
Nadat we alle datums van feestdagen van bepaalde jaren hebben opgesomd, kunnen we eenvoudig controleren of een datum een officiële feestdag is of niet met formules in Excel. Stel dat je een datumlijst hebt zoals in de volgende schermafbeelding, en ik zal de manieren introduceren om het gemakkelijk voor elkaar te krijgen.
Selecteer een lege cel naast de datumlijst, zegt cel B18, voer de formule in = ALS (AANTAL.ALS ($ C $ 2: $ D $ 11, A18), "Feestdag", "Nee") erin en sleep vervolgens de vulhendel naar het gewenste bereik. Zie screenshot hierboven:
Opmerkingen:
(1) In de formule = ALS (AANTAL.ALS ($ C $ 2: $ D $ 11, A18), "Feestdag", "Nee"), is $ C $ 2: $ D $ 11 het bereik van feestdagen in specifieke jaren, en A18 is de cel met de datum waarop u wilt controleren of het een officiële feestdag is, en u kunt deze wijzigen op basis van uw behoeften. En deze formule retourneert "Feestdag" als de specifieke datum een feestdag is, en retourneert "Nee" als dat niet het geval is.
(2) U kunt deze matrixformule ook toepassen = ALS (OF ($ C $ 2: $ D $ 11 = A18), "Feestdag", "NEE") om te controleren of de bijbehorende datum een feestdag is of niet.
Deel 3: tel dagen tussen twee dagen behalve weekends en feestdagen in Excel
In deel 1 hebben we alle feestdagen in een bepaald jaar opgesomd, en nu helpt deze methode je om het aantal dagen te tellen behalve alle weekenden en feestdagen in een datumbereik.
Selecteer een lege cel waarin u het aantal dagen retourneert en voer de formule in = NETWERKDAGEN (E1, E2, B2: B10) erin en druk op de Enter sleutel.
Note: In bovenstaande cellen is E1 de startdatum van het opgegeven datumbereik, E2 is de einddatum en B2: B10 is de vakantielijst die we in Deel 1 berekenen.
Nu krijgt u het aantal dagen behalve weekends en feestdagen in het opgegeven datumbereik.
Kopieer formules exact / statisch zonder celverwijzingen in Excel te wijzigen
Kutools for Excel Exacte kopie hulpprogramma kan u helpen om eenvoudig meerdere formules exact te kopiëren zonder celverwijzingen in Excel te wijzigen, waardoor wordt voorkomen dat relatieve celverwijzingen automatisch worden bijgewerkt.
Kutools for Excel - Geef Excel een boost met meer dan 300 essentiële tools. Geniet van een GRATIS proefperiode van 30 dagen zonder creditcard! Snap het nu
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, 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 vermindert honderden muisklikken voor u elke dag!