Astuce: Les autres langues sont Google-Traduction. Vous pouvez visiter le English version de ce lien.
Se connecter
x
or
x
x
S'enregistrer
x

or

Comment vérifier si une date est un jour férié et compter les jours sauf les jours fériés dans Excel?

Par exemple, vous avez une liste de dates et vous voulez vérifier si des jours fériés existent dans la liste, comment pourriez-vous le faire rapidement? Dans cet article, je vais vous montrer les moyens de calculer toutes les dates des jours fériés américains dans les années données, puis vérifier si une date est un jour férié américain facilement dans Excel.

Partie 1: Calculer les jours fériés dans les années données dans Excel

Partie 2: Vérifier si une date est un jour férié dans Excel

Partie 3: compte jours entre deux jours sauf week-ends et jours fériés dans Excel

Copier les formules exactement / statiquement sans changer les références de cellule dans Excel

Kutools for Excel Copie exacte L'utilitaire peut vous aider à copier facilement plusieurs formules sans modifier les références de cellule dans Excel, ce qui empêche la mise à jour automatique des références de cellules. Fonctionnalité complète Essai gratuit 30-day!

formules exactes de copie 3

Onglet Office Activer l'édition et la navigation par onglets dans Office, et simplifiez grandement votre travail ...
Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%
  • Réutiliser n'importe quoi: Ajoutez les formules, graphiques et autres éléments les plus utilisés ou les plus complexes à vos favoris et réutilisez-les rapidement.
  • Plus que le texte 20 comprend: Extraire le numéro de la chaîne de texte; Extraire ou supprimer une partie des textes; Convertissez les nombres et les devises en mots anglais.
  • Fusionner les outils: Plusieurs classeurs et feuilles en un; Fusionner plusieurs cellules / lignes / colonnes sans perdre de données; Fusionner les lignes en double et la somme.
  • Outils Split: Fractionner les données en plusieurs feuilles en fonction de la valeur; Un classeur pour plusieurs fichiers Excel, PDF ou CSV; Une colonne à plusieurs colonnes.
  • Coller Sauter Lignes cachées / filtrées; Compte et somme par couleur de fond; Envoyez des e-mails personnalisés à plusieurs destinataires en bloc.
  • Super filtre: Créez des schémas de filtrage avancés et appliquez-les à toutes les feuilles. Trier par semaine, jour, fréquence et plus; Filtre en gras, formules, commentaires ...
  • Plus que de puissantes fonctionnalités 300; Fonctionne avec Office 2007-2019 et 365; Prend en charge toutes les langues; Déploiement facile dans votre entreprise ou organisation.

flèche bleue droite bullePartie 1: Calculer les jours fériés avec une année donnée dans Excel

Avant de vérifier si une date est un jour férié, vous devez lister toutes les congés dans les années données dans Excel. Par conséquent, la préparation d'une table comme suit capture d'écran vous facilitera la tâche.

doc vérifier si jour férié 1

Il y a trois sortes de jours fériés américains:

(1) Le premier type est un jour férié à date fixe, comme le Jour de l'An est le Jan 1st. nous pouvons facilement calculer le jour de l'an avec la formule = DATE (Année donnée, 1,1);

(2) Le deuxième type est un jour férié sur un jour de semaine fixe, tel que le président Day. Nous pouvons facilement calculer le jour du président avec la formule = DATE (Année donnée, 1,1) + 14 + CHOOSE (WEEKDAY (DATE (Année donnée, 1,1)), 1,0,6,5,4,3,2);

(3) Et le dernier type est le Memorial Day, nous pouvons facilement calculer le Memorial Day avec la formule = DATE (Année donnée, 6,1) -WEEKDAY (DATE (Année donnée, 6,6)).

Ici, je liste des formules pour calculer tous les jours fériés dans le tableau suivant. Entrez simplement les formules dans la cellule appropriée et appuyez sur Entrer clé un par un.

Vacances Cellule Formules
Jour du Nouvel An C2 = DATE (C1,1,1)
Journée Martin Luther King Jr. C3 = DATE (C1,1,1) + 14 + CHOISIR (WEEKDAY (DATE (C1,1,1)), 1,0,6,5,4,3,2)
Président Day C4 = DATE (C1,2,1) + 14 + CHOISIR (WEEKDAY (DATE (C1,2,1)), 1,0,6,5,4,3,2)
Jour du Souvenir C5 = DATE (C1,6,1) -WEEKDAY (DATE (C1,6,6))
Jour de l'Indépendance C6 = DATE (C1,7,4)
Fête du travail C7 = DATE (C1,9,1) + CHOISIR (WEEKDAY (DATE (C1,9,1)), 1,0,6,5,4,3,2)
Jour de Christophe Colomb C8 = DATE (C1,10,1) + 7 + CHOISIR (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 + CHOISIR (WEEKDAY (DATE (C1,11,1)), 4,3,2,1,0,6,5)
Jour de Noël C11 = DATE (C1,12,25)

Veuillez noter que : Dans les formules du tableau ci-dessus, C1 est la cellule de référence qui localise l'année donnée. Dans notre exemple, cela signifie Année 2015, et vous pouvez le modifier en fonction de vos besoins.

Avec ces formules, vous pouvez facilement calculer les dates des jours fériés avec des années données. Voir la capture d'écran ci-dessous:

doc vérifier si jour férié 2


Enregistrer une plage en tant que saisie automatique (formats de cellule restants et formules) pour la réutilisation à l'avenir

Il doit être très fastidieux de référencer des cellules et d'appliquer des formules pour calculer chaque congé. Kutools for Excel fournit une solution de contournement mignonne de Insertion automatique utilitaire pour enregistrer la plage comme une entrée Insertion automatique, qui peut rester les formats de cellule et les formules dans la plage. Et vous réutiliserez cette gamme en un seul clic. Le travail devient facile en un seul clic pour insérer cette table et changer l'année dans cette table! Fonctionnalité complète Essai gratuit 30-day!

Vacances auto américaines 1

flèche bleue droite bullePartie 2: Vérifier si une date est un jour férié dans Excel

Après avoir listé toutes les dates de jours fériés d'années spécifiques, nous pouvons facilement vérifier si une date est un jour férié ou non avec des formules dans Excel. Supposons que vous ayez une liste de dates comme la capture d'écran suivante, et je vais vous présenter les moyens de le faire facilement.

Sélectionnez une cellule vide en plus de la liste des dates, dit Cell B18, entrez la formule = SI (NB ($ C $ 2: $ D $ 11, A18), "Vacances", "Non") dans celui-ci, puis faites glisser le handle de remplissage jusqu'à la plage dont vous avez besoin. Voir capture d'écran ci-dessus:

Remarques:

(1) Dans la formule = IF (COUNTIF ($ C $ 2: $ D $ 11, A18), "Holiday", "No"), le $ C $ 2: $ D $ 11 est la plage des jours fériés dans A18 est la cellule avec la date à laquelle vous voulez vérifier si c'est un jour férié, et vous pouvez les changer en fonction de vos besoins. Et cette formule retournera "Vacances" si la date spécifique est un jour férié, et retournera "Non" si ce n'est pas le cas.

(2) Vous pouvez également appliquer cette formule de tableau = SI (OU ($ C $ 2: $ D $ 11 = A18), "Vacances", "NON") pour vérifier si la date correspondante est en vacances ou non.


flèche bleue droite bulle Partie 3: compte jours entre deux jours sauf week-ends et jours fériés dans Excel

Dans la partie 1, nous avons listé toutes les vacances d'une année donnée, et maintenant cette méthode vous guidera pour compter le nombre de jours sauf tous les week-ends et jours fériés dans une période donnée.

Sélectionnez une cellule vide, vous retournerez le nombre de jours et entrez la formule = JOURS RESEAU (E1, E2, B2: B10) dans le, et appuyez sur le Entrer clé.

Note: Dans les cellules ci-dessus, E1 est la date de début de la plage de dates spécifiée, E2 est la date de fin, et B2: B10 est la liste des jours fériés que nous calculons dans la partie 1.

doc vérifier si jour férié 6

Vous obtiendrez désormais le nombre de jours, sauf les week-ends et jours fériés, dans la plage de dates spécifiée.


Kutools for Excel résout la plupart de vos problèmes et augmente votre productivité de 80%

  • Réutilisation: Insérer rapidement formules complexes, graphiques et tout ce que vous avez utilisé auparavant; Crypter les cellules avec mot de passe Créer une liste de diffusion et envoyer des emails ...
  • Super Formula Bar (éditez facilement plusieurs lignes de texte et de formule); Disposition de lecture (facilement lire et éditer un grand nombre de cellules); Coller à la gamme filtrée...
  • Fusionner les cellules / rangées / colonnes sans perdre de données; Contenu des cellules divisées; Combiner les lignes / colonnes en double... Prévenir les cellules en double; Comparer les plages...
  • Sélectionnez Dupliquer ou Unique Des rangées; Sélectionnez les lignes vierges (toutes les cellules sont vides); Super Find et Fuzzy Find dans de nombreux cahiers d'exercices; Sélection aléatoire ...
  • Copie exacte Plusieurs cellules sans changer la référence de la formule; Créer automatiquement des références à plusieurs feuilles; Insérer des balles, Cases à cocher et plus ...
  • Extrait du texte, Ajouter du texte, Supprimer par position, Supprimer l'espace; Créer et imprimer des sous-totaux de pagination; Conversion entre contenu de cellules et commentaires...
  • Super filtre (enregistrer et appliquer des schémas de filtrage à d'autres feuilles); Tri avancé par mois / semaine / jour, fréquence et plus; Filtre spécial en gras, en italique ...
  • Combinaison de classeurs et de feuilles de calcul; Fusionner les tables en fonction des colonnes clés; Fractionner les données en plusieurs feuilles; Conversion par lots xls, xlsx et PDF...
  • Plus que de puissantes fonctionnalités 300. Prend en charge Office / Excel 2007-2019 et 365. Prend en charge toutes les langues. Déploiement facile dans votre entreprise ou organisation. Fonctionnalités complètes Essai gratuit du jour 30.
kte tab 201905

Office Tab apporte une interface à onglets à Office et simplifie grandement votre travail

  • Activer l'édition par onglets et la lecture dans Word, Excel, PowerPoint, Publisher, Access, Visio et Project.
  • Ouvrez et créez plusieurs documents dans de nouveaux onglets de la même fenêtre, plutôt que dans de nouvelles fenêtres.
  • Augmente votre productivité de 50% et réduit le nombre de clics de souris pour vous chaque jour!
fond officetab
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 · 1 years 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 · 11 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 · 1 years 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)