Ecart entre deux dates sans les jours fériés

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


Sujets que vous pourriez aussi aimer :


Nombre de jours entre deux dates

Dans cette petite mise en pratique, nous allons démontrer comment comptabiliser les jours situés entre une date de départ et une date de fin variables.



Mais comme le montre l'exemple finalisé illustré par la capture ci-dessus, ce n'est pas tout. Nous allons aussi apprendre à déduire les jours fériés du décompte ainsi que les jours de Week End.

Source et présentation
Pour la mise en place des formules, nous suggérons de récupérer une trame existante.

• Télécharger le classeur calculer-le-nombre-de-jours.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,
• En bas de la fenêtre Excel, cliquer sur l'onglet Jour_feries de ce classeur à deux feuilles,

Nous affichons ainsi le contenu de cette seconde feuille.



Elle offre un tableau archivant les jours fériés sur trois années : 2020, 2021 et 2022. En sélectionnant toutes ses dates et en consultant la zone Nom, en haut à gauche de la feuille Excel, vous remarquez que cette plage est identifiée par un nom. Nous exploiterons ce nom pour faciliter la construction de la formule impliquée.

• En bas de la fenêtre Excel, cliquer sur l'onglet nbJours pour revenir sur la première feuille,

C'est elle qui doit accueillir nos formules. Tout d'abord, une date de début et une date de fin doivent être renseignées respectivement en cellule B6 et en cellule B9.

• En cellule B6, inscrire par exemple la date suivante : 29/03/2020,
• En cellule B9, inscrire par exemple la date suivante : 16/06/2020,

Le nombre de jours séparant ces deux dates doit premièrement être calculé en cellule D6. Puis, ce même calcul en excluant les jours fériés recensés sur la période, doit être formulé en cellule F6. Enfin, il est aussi question d'exclure les jours de Week End du décompte en cellule H6. Nous obtiendrons donc le nombre de jours ouvrés sur la période délimitée par ces deux dates.

Nombre de jours entre deux dates
Ce premier calcul est trivial grâce à la souplesse d'Excel.

• En cellule D6, écrire et valider la formule suivante : =B9-B6+1,

Nous réalisons simplement la différence entre la date de début (B6) et la date de fin (B9). Bien entendu, pour comptabiliser tous les jours, y compris ceux des dates désignées, nous ajoutons une unité. Ainsi, nous réintégrons la date de départ dans le décompte.



Nous obtenons un résultat de 80 jours séparant les deux dates mentionnées.

Exclure les jours fériés du décompte
Désormais, nous devons compter le nombre de jours séparant ces deux dates, sans inclure les jours fériés. Aucune fonction ne prévoit ce type d'exclusion. C'est un petit raisonnement matriciel engagé sur le tableau des jours fériés qui permet de réaliser le décompte, en fonction de deux critères à recouper. Il s'agit de vérifier pour chaque jour férié recensé s'il est à la fois supérieur ou égal à la date de début et inférieur ou égal à la date de fin. En d'autres termes, nous devons vérifier qu'il est bien compris entre ces deux bornes.

• Sélectionner la cellule F6,
• Taper le symbole égal (=) pour initier la formule matricielle,
• Répliquer le calcul précédent pour le total du nombre de jours, soit : B9-B6+1,
• Taper le symbole moins (-) pour annoncer les jours fériés à exclure,
• Inscrire la fonction SommeProd suivie d'une parenthèse, soit : SommeProd(,

Nous la connaissons bien désormais. Dans sa version classique, elle réalise la somme des valeurs respectives multipliées entre deux matrices. Mais lorsque nous lui passons des matrices recoupées et soumises à conditions, elle répond par des indicateurs booléens sous forme de chiffre, 1 pour Oui. En d'autres termes, dès qu'un jour férié sera repéré comme concordant avec la période, il sera comptabilisé et sommé avec les autres. Nous réaliserons ainsi la soustraction des jours fériés concernés à exclure du décompte.

• Ouvrir une parenthèse pour accueillir la première matrice conditionnelle,
• Désigner la plage des jours fériés par son nom, soit : Feries,
• Taper l'inégalité suivante pour le premier critère : >=B6,
• Fermer la parenthèse de la première matrice conditionnelle,
• Taper le symbole de l'étoile (*) pour annoncer la condition à recouper,
• Ouvrir une nouvelle parenthèse pour accueillir la seconde matrice conditionnelle,
• Désigner de nouveau le tableau des jours fériés par son nom, soit : Feries,
• Puis, écrire le critère suivant : <=B9,

Par ce recoupement, nous cherchons bien à trouver les jours fériés qui sont compris entre la date de début (B6) et la date de fin (B9).

• Fermer la parenthèse de la seconde matrice conditionnelle,
• Puis, fermer la parenthèse de la fonction SommeProd,
• Enfin, valider la formule par la touche Entrée du clavier par exemple,

Nous obtenons un décompte inférieur de cinq jours par rapport au calcul précédent. Ce résultat est parfaitement logique. Si vous consultez le tableau des jours fériés, trois sont recensés en Mai, un en Juin et un en Avril. La syntaxe de la formule matricielle que nous avons construite est la suivante :

=B9-B6+1 - SOMMEPROD((Feries>=B6)*(Feries<=B9))

Exclure les jours de Week-End du décompte
Pour enlever les jours de Week-End du dénombrement dans l'intervalle défini, nous pourrions très bien bâtir une formule matricielle. Celle-ci devrait s'assurer que les jours fériés et jours de Week-End ne sont pas communs pour ne pas les soustraire deux fois. Sa syntaxe serait la suivante :

=B9-B6+1 - SOMMEPROD((Feries >= B6)*(Feries <= B9)*(JOURSEM(Feries) <> 1)*(JOURSEM(Feries) <> 7)) - NB.JOURS.OUVRES.INTL(B6;B9; '1111100')

Mais dans la mesure où Excel offre une fonction dédiée, il est préférable de l'utiliser. Il s'agit de la fonction NB.JOURS.OUVRES.INTL. Sa construction est beaucoup plus simple. Il suffit de lui indiquer la date de début, puis la date de fin, puis les jours de Week-End et enfin les jours fériés.

• Taper le symbole égal (=) en cellule H6 pour initier la formule
• Inscrire la fonction mentionnée suivie d'une parenthèse, soit : NB.JOURS.OUVRES.INTL(,
• Cliquer sur la cellule B6 pour désigner la date de départ,
• Taper un point-virgule (;) pour passer dans l'argument suivant de la fonction,
• Cliquer sur la cellule B9 pour définir la date d'arrivée,
• Taper un point-virgule (;) pour passer dans l'argument des jours de Week-End,

Une liste de suggestions apparaît. Les jours de Week-End peuvent varier selon la profession.

• Cliquer sur le chiffre 1 ou l'inscrire au clavier pour exclure les Samedis et Dimanches,
• Taper un point-virgule (;) pour passer dans l'argument des jours fériés,
• Désigner la table les archivant par son nom, soit : Feries,
• Fermer la parenthèse de la fonction et valider le calcul par la touche Entrée du clavier,

Le résultat est fort logiquement bien inférieur aux décomptes précédents. Tous les jours de Week-End ont été exclus du décompte en ne doublant pas les jours fériés les recoupant.