Expression des besoins

Le besoin est d’avoir une feuille Google Sheet permettant de calculer le nombre de jours ouvrés d’arrêt maladie entre une date de début et une date de fin. Ces nombres de jours doivent être répartis par mois

CAS 1 : Date de début et de fin d’arrêt maladie situé dans le même mois

C’est le cas le plus simple

Premièrement, vérifier que les deux dates sont dans le même mois. La formule EOMONTH(;) permet d’obtenir le dernier jour du mois. On focuse sur le mois avec la fonction MONTH().

il suffit de prendre le nombre de jours ouvrés entre les deux dates.
pour cela, la formule GS  NETWORKDAYS.INTL(;) est toute indiquée

Exemple : NETWORKDAYS.INTL($A2;$B2) ou $A2 et $B2 sont les cellules contenant les informations nécessaires

Explications :

Si le mois de la date de début et de fin (ici, 01) sont dans le même mois, la fonction NETWORKDAYS affiche le nombre de jours ouvrés travaillés

CAS 2 : les dates de début et de fin de maladie ne sont pas dans le mois courant

Ce cas est particulier. En effet, il faut aussi prendre en compte que l’arret maladie peut commencer courant janvier et se finir  courant mars. Soit X jours en Janvier + l’ensemble des jours en Février + X jours en Mars

Astuce : Calculer le nombre de jours par mois

Il suffit de soustraire le premier jour du mois, au dernier. Le calcul du dernier jour du mois est : EOMONTH(CELLULE;0) ou CELLULE contient la date. 

Voici la formule permettant de gérer les divers cas.

=IFERROR(IFS(AND(DATEVALUE(MID($A2;4;7))=DATEVALUE(MID(D$1;4;7));DATEVALUE(MID($B2;4;7))=DATEVALUE(MID(D$1;4;7)));NETWORKDAYS.INTL($A2;$B2);AND(DATEVALUE(MID($A2;4;7))=DATEVALUE(MID(D$1;4;7));DATEVALUE(MID($B2;4;7))>DATEVALUE(MID(D$1;4;7)));NETWORKDAYS.INTL($A2;EOMONTH(D$1;0));AND(DATEVALUE(MID($A2;4;7))<DATEVALUE(MID(D$1;4;7));DATEVALUE(MID($B2;4;7))=DATEVALUE(MID(D$1;4;7)));NETWORKDAYS.INTL(D$1;$B2);AND(DATEVALUE(MID($A2;4;7))<DATEVALUE(MID(D$1;4;7));DATEVALUE(MID($B2;4;7))>DATEVALUE(MID(D$1;4;7)));NETWORKDAYS.INTL(D$1;EOMONTH(D$1;0)));0)

Astuces

La comparaison de date est toujours compliquée. Afin de gagner du temps, j’extrais une chaîne de caractères de chaque date via la fonction STXT (MID), ensuite que je convertis cette chaîne en entier grâce à la fonction DATEVAL (DATEVALUE).

Pour aller plus loin

Il est possible de mettre un entête des mois qui se décale de façon automatique

12 Commentaires

    • Bonjour
      En effet, il s’agit d’un site dédié à la suite Google et non Office.
      La formule proposée ne fonctionnera pas sous excel, car les commandes ne sont pas reconnues d’Excel.
      Deux solutions :
      Soit vous pouvez vous inspirer de la formule en la transcrivant avec les fonctions Excel
      Soit vous passez sous la Google Suite

      Cordialement

  1. vraiment dommage un suepr travail mais si je prends un gsheet propre que je copie votre formule avec le meme tableau cela ne fonctionne pas hélas (en respectant) les colonnes ( je reproduis le resultat final)

  2. excusez moi j’ai precipité un peu ma question je parle de votre deuxieme presentation sur l’imprim ecran nous voyons un emonth(I1:0) alors que vous mettez en surbrillance la colonne D2

    auriez vous par contre un gsheet exemple à partager concernant le cas 3 avec la formule qui permet de gerer l’ensemble des cas ?
    car en reproduisant votre tableau et en recopiant la formule je n’arrive pas à la faire fonctionner ? ( je narrive a appliquer que les 2 premieres conditions mais quand je copie le reste de la formule il y a une erreur)
    ca me serait vraiment utile

    merci infiniment deja d’avoir pris en compte ma remarque

  3. en non calendaire en m’inspirant de vos infos : je me met en colonne C
    =SI(ET(MOIS($A2)=MOIS(FIN.MOIS(C$1;0));MOIS($B2)=MOIS(FIN.MOIS(C$1;0)));datedif($A2;$B2; »D »);SI(ET(MOIS($A2)=MOIS(FIN.MOIS(C$1;0));MOIS($B2)>MOIS(FIN.MOIS(C$1;0)));DATEDIF($A2;FIN.MOIS(C1;0); »D »);SI(ET(MOIS($A2)<MOIS(FIN.MOIS(C$1;0));MOIS($B2)=MOIS(FIN.MOIS(C$1;0)));DATEDIF(C$1;$B2;"D")+1;SI(ET(MOIS($A2)MOIS(FIN.MOIS(C$1;0)));DATEDIF(C$1;FIN.MOIS(C$1;0)+1; »D »);0))))

    • Bonsoir

      Nouvelle formule qui corrige le changement d’année

      =IFERROR(IFS(AND(DATEVALUE(MONTH($A2)& »/ »&YEAR($A2))=DATEVALUE(MONTH(D$1)& »/ »&YEAR(D$1));DATEVALUE(MONTH($B2)& »/ »&YEAR($B2))=DATEVALUE(MONTH(D$1)& »/ »&YEAR(D$1)));NETWORKDAYS.INTL($A2;$B2);AND(DATEVALUE(MONTH($A2)& »/ »&YEAR($A2))=DATEVALUE(MONTH(D$1)& »/ »&YEAR(D$1));DATEVALUE(MONTH($B2)& »/ »&YEAR($B2))>DATEVALUE(MONTH(D$1)& »/ »&YEAR(D$1)));NETWORKDAYS.INTL($A2;EOMONTH(D$1;0));AND(DATEVALUE(MONTH($A2)& »/ »&YEAR($A2))DATEVALUE(MONTH(D$1)& »/ »&YEAR(D$1)));NETWORKDAYS.INTL(D$1;EOMONTH(D$1;0)));0)

      • Bonjour,

        Formule simplifiée

        =IFERROR(IFS(AND(DATEVALUE(MID($A2;4;7))=DATEVALUE(MID(D$1;4;7));DATEVALUE(MID($B2;4;7))=DATEVALUE(MID(D$1;4;7)));NETWORKDAYS.INTL($A2;$B2);AND(DATEVALUE(MID($A2;4;7))=DATEVALUE(MID(D$1;4;7));DATEVALUE(MID($B2;4;7))>DATEVALUE(MID(D$1;4;7)));NETWORKDAYS.INTL($A2;EOMONTH(D$1;0));AND(DATEVALUE(MID($A2;4;7))DATEVALUE(MID(D$1;4;7)));NETWORKDAYS.INTL(D$1;EOMONTH(D$1;0)));0)

      • Bonjour,

        Formule simplifiée

        =IFERROR(IFS(AND(DATEVALUE(MID($A2;4;7))=DATEVALUE(MID(D$1;4;7));DATEVALUE(MID($B2;4;7))=DATEVALUE(MID(D$1;4;7)));NETWORKDAYS.INTL($A2;$B2);AND(DATEVALUE(MID($A2;4;7))=DATEVALUE(MID(D$1;4;7));DATEVALUE(MID($B2;4;7))>DATEVALUE(MID(D$1;4;7)));NETWORKDAYS.INTL($A2;EOMONTH(D$1;0));AND(DATEVALUE(MID($A2;4;7))

LAISSER UN COMMENTAIRE

Please enter your comment!
Please enter your name here