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 60-day!

formules exactes de copie 3


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)

Remarque: 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 60-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.



Outils de productivité recommandés

Office Tab

étoile d'or1 Apportez des onglets pratiques à Excel et à d'autres logiciels Office, tout comme Chrome, Firefox et Internet Explorer.

Kutools for Excel

étoile d'or1 Incroyable! Augmentez votre productivité dans les minutes 5. Ne nécessite pas de compétences particulières, économisez deux heures par jour!

étoile d'or1 300 Nouvelles fonctionnalités pour Excel, rendent Excel facile et puissant:

  • Fusionner des cellules / lignes / colonnes sans perdre de données.
  • Combiner et consolider plusieurs feuilles et classeurs.
  • Comparez les gammes, copiez plusieurs gammes, convertissez le texte en date, l'unité et la conversion de devise.
  • Compter par couleurs, sous-totaux de recherche, tri avancé et super filtre,
  • Plus Sélectionner / Insérer / Supprimer / Texte / Format / Lier / Commenter / Classeurs / Feuilles de calcul Outils ...

Capture d'écran de Kutools pour Excel

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 · 7 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 · 4 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 · 1 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 · 6 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 · 2 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)