Compter les Week-End et jours fériés entre deux dates

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Calculer les jours spéciaux entre deux dates

Dans ce cas pratique Excel, l'utilisateur définit une date de début puis une date de fin.



Aussitôt, nous devons être en mesure de livrer le nombre de jours spéciaux situés entre ces deux bornes, comme l'illustre l'exemple de la capture. Le nombre de jours ouvrés est un calcul trivial. Connaître le nombre de jours fériés et le nombre de Week-End cumulés est un tout autre défi. Et c'est un raisonnement matriciel qui apporte la solution.

Source et présentation du concept
Pour aboutir cette solution, nous suggérons de récupérer un fichier offrant quelques précieuses données.

• Télécharger le classeur week-end-feries-jours-ouvres.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Les deux dates doivent donc être inscrites respectivement en cellules C7 et D7. Sur l'intervalle qui les sépare, les nombres de jours doivent être calculés en fonction de leurs spécificités en cellules D9, D11 et D13.

Sur la droite de cette feuille, vous notez l'énumération des jours fériés pour l'année de la date de début et l'année suivante. En effet, rien n'empêche l'utilisateur de définir une période à cheval sur deux années. Ces jours fériés sont calculés dynamiquement. La formule décisive est inscrite en cellule G4 :

=ARRONDI(DATE(ANNEE(C7); 4; MOD(234-11*MOD(ANNEE(C7); 19); 30))/7; 0)*7-6

Nous l'avons présentée dans une précédente formation. Elle permet de calculer le Dimanche de Pâques en fonction de l'année spécifiée. De ce calcul, les jours fériés des fêtes religieuses se déduisent naturellement. Grâce à ces données dynamiques, nous allons pouvoir entreprendre les calculs des nombres de jours spéciaux situés entre deux dates.

Pour les jours fériés statiques, c'est une formule classique exploitant la fonction Excel Date qui permet de les livrer : =DATE(G6;1;1).

Les jours ouvrés
Nous l'avons déjà présentée, c'est la fonction Excel Nb.Jours.Ouvres qui permet de compter le nombre de jours ouvrés entre deux dates : =NB.JOURS.OUVRES(Début;Fin;Fériés). En premier et deuxième argument, nous devons lui indiquer respectivement la date de début et la date de fin. En troisième et dernier argument, nous devons lui passer la plage de cellules sur laquelle sont recensés les jours fériés. En considérant les Samedis et Dimanches comme les jours de Week-End, elle livrera le nombre de jours effectivement travaillés sur la période.

• Sélectionner la cellule D9,
• Taper le symbole égal (=) pour initier la formule,
• Inscrire la fonction suivie d'une parenthèse, soit : Nb.Jours.Ouvres(,
• Désigner la date de début en cliquant sur sa cellule C7,
• Taper un point-virgule (;) pour passer dans l'argument suivant de la fonction,
• Désigner la date de fin en cliquant sur sa cellule D7,
• Taper un point-virgule (;) pour passer dans l'argument des jours fériés,
• Sélectionner la plage de cellules G7:I17,

La colonne vide de séparation n'est pas gênante. Elle ne porte aucune valeur donc aucune date a fortiori. Ainsi, nous englobons dans la sélection les jours fériés recensés sur les deux années.

• Fermer la parenthèse de la fonction Nb.Jours.Ouvres,
• Puis, valider le calcul avec la touche Entrée du clavier,

Nous obtenons bien le total du nombre de jours effectivement travaillés sur la période. Les Week-End et jours fériés sont naturellement exclus. Bien sûr, si vous modifiez les bornes de la période, ce total se recalcule automatiquement.

La syntaxe de la formule que nous avons bâtie est la suivante : =NB.JOURS.OUVRES(C7;D7; G7:I17).

Les jours fériés
Pour calculer le nombre de jours fériés entre deux dates, nous devons donc enclencher un raisonnement matriciel. L'astuce consiste à construire une matrice virtuelle représentant toutes les dates situées entre les bornes de la période définie. Nous devons la recouper avec la plage des jours fériés. Si des dates coïncident, nous saurons qu'il s'agit de jours fériés à exclure du décompte. Et pour engager un décompte conditionnel, nous devons exploiter la fonction Excel Nb.Si.

• Sélectionner la cellule D11 et taper le symbole égal (=) pour débuter la formule,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,

Nous le savons, dans sa version classique, cette fonction permet d'additionner les valeurs multipliées sur les lignes respectives des matrices engagées. Mais lorsqu'elle vérifie des conditions, elle les dénombre et les additionne. Il en résultera le nombre de jours fériés entre les deux dates.

• Inscrire la fonction de dénombrement conditionnel suivie d'une parenthèse, soit : Nb.Si(,
• Désigner l'ensemble des jours fériés comme précédemment, soit la plage G7:I17,

Nous spécifions ainsi la plage de cellules sur laquelle nous souhaitons observer la présence de toutes les dates situées entre les bornes de la période définie. Pour cela, en guise de critère à dénombrer, nous devons énoncer toutes ces dates sous forme de matrice virtuelle.

• Taper un point-virgule (;) pour passer dans l'argument du critère de la fonction Nb.Si,
• Inscrire la fonction pour la ligne d'une cellule, suivie d'une parenthèse, soit : Ligne(,

Nous n'allons pas lui passer une cellule mais l'ensemble des lignes potentielles qui auraient servi à énumérer toutes les dates situées dans l'intervalle. Et pour que cette fonction retourne le contenu de ces dernières, nous devons exploiter la fonction Indirect.

• Inscrire la fonction d'interprétation suivie d'une parenthèse, soit : Indirect(,
• Désigner la première date pour le point de départ de la matrice, soit : C7,
• Taper le caractère de concaténation (&) pour annoncer la borne inférieure à suivre,
• Inscrire le symbole deux points entre guillemets, soit : ':',
• Taper de nouveau le caractère de concaténation (&),
• Puis, désigner la date de fin en cliquant sur sa cellule D7,
• Fermer la parenthèse de la fonction Indirect,
• Fermer la parenthèse de la fonction Ligne,
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Enfin, valider la formule avec la touche Entrée du clavier,

Le résultat tombe et sa cohérence est très simple à confirmer en analysant la table des jours fériés recensés dans l'intervalle.

La syntaxe de la formule que nous avons construite est la suivante :

=SOMMEPROD(NB.SI(G7:I17; LIGNE(INDIRECT(C7 & ':' & D7))))

Une fois encore, si vous changez les bornes de la période, le recalcul automatique s'enclenche instantanément pour actualiser le décompte des jours fériés. Par exemple, entre le 1^er Novembre 2020 et le 30 Décembre 2020, 3 jours fériés sont naturellement comptabilisés.

Week-End et jours fériés
Désormais, pour dénombrer les jours de Week-End et les jours fériés entre deux dates, nous devons vérifier que ces données ne se recoupent pas. Un jour férié qui tombe un Week-End ne doit pas être comptabilisé deux fois. Nous devons donc vérifier un critère sur le jour de semaine de chaque date de la matrice virtuelle. Pour cela, nous devons exploiter la fonction Excel JourSem. Dans le même temps, nous devons nous assurer que ces jours de Week-End ne sont pas énoncés dans la liste des jours fériés. Ainsi en ne les comptabilisant pas, nous pourrons ajouter les jours fériés de la période pour obtenir le total cumulé exact.

Pour ne pas avoir à reconstruire la matrice virtuelle des dates, il est préférable de commencer par copier sa syntaxe depuis la précédente formule : LIGNE(INDIRECT(C7 &':' & D7)).

• Sélectionner la cellule D13 et taper le symbole égal (=) pour débuter la formule,
• Inscrire la fonction matricielle suivie d'une parenthèse, soit : SommeProd(,
• Ouvrir une première parenthèse pour accueillir la première matrice conditionnelle,
• Inscrire la fonction pour le numéro de semaine suivie d'une parenthèse, soit : Joursem(,
• En guise de date, lui passer la matrice des dates, soit : LIGNE(INDIRECT(C7 & ':' & D7)),
• Taper un point-virgule (;) pour passer dans le second argument de la fonction JourSem,
• Inscrire le chiffre 2 pour raisonner selon le calendrier français,

Par défaut, cette fonction raisonne en effet sur le calendrier américain. Dans ce contexte, le Dimanche est le premier jour de la semaine et non le septième. Avec le calendrier français, nous allons pouvoir simplifier le double critère.

• Fermer la parenthèse de la fonction JourSem,
• Puis, inscrire la condition suivante : >5,

Comme nous le disions et de fait, nous cherchons à repérer dans le calendrier français toutes les dates pour lesquelles le jour de semaine est supérieur à 5. Il ne reste que les Samedis et Dimanches, soit les jours de Week End.

• Fermer la parenthèse de cette première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer le critère à recouper,
• Ouvrir une parenthèse pour accueillir la nouvelle matrice conditionnelle,
• Inscrire la fonction de dénombrement conditionnel suivie d'une parenthèse, soit : Nb.Si(,
• Désigner l'ensemble des jours fériés recensés, soit la plage de cellules G7:I17,
• Taper un point-virgule (;) pour passer dans l'argument du critère à compter,
• Inscrire de nouveau la matrice virtuelle des dates, soit : LIGNE(INDIRECT(C7 & ':' & D7)),
• Fermer la parenthèse de la fonction Nb.Si,
• Puis, ajouter le critère suivant : =0,

Nous cherchons donc à vérifier que ces dates identifiées comme jours de Week End, ne sont pas dans le même temps des jours fériés. Si tel est le cas, elles ne sont pas comptabilisées. C'est ainsi que nous allons maintenant pouvoir ajouter ces jours fériés recoupés sans risquer de les doubler dans le décompte.

• Fermer la parenthèse de cette deuxième matrice conditionnelle,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Inscrire le symbole plus (+) pour annoncer le critère à combiner,
• Puis, reproduire le calcul précédent du décompte des jours fériés :

SOMMEPROD(NB.SI(G7:I17; LIGNE(INDIRECT(C7 & ':' & D7))))

• Dès lors, valider la formule par la touche Entrée du clavier,
• Puis, attribuer un format Standard à la cellule pour éviter l'interprétation en date,

Nous obtenons bien le décompte des jours remarquables cumulés et non doublés en cas de recoupement.



Comme toujours, si vous modifiez les bornes de la période, vous constatez que les résultats des décomptes s'actualisent parfaitement. La formule que nous avons construite pour compter les Week-End et jours fériés entre deux dates est la suivante :

=SOMMEPROD((JOURSEM(LIGNE(INDIRECT(C7 & ':' & D7)); 2)>5)*(NB.SI(G7:I17; LIGNE(INDIRECT(C7 &':' & D7)))=0)) + SOMMEPROD(NB.SI(G7:I17; LIGNE(INDIRECT(C7 & ':' & D7))))