Calculer le nombre de mois consécutifs dans une formule
Bonjour,
Je cherche à automatiser le calcul des jours de congé attribués par année contractuelle, ce qui signifie que cette période peut chevaucher sur 2 années.
Le principe est le suivant : si la personne a presté 12 mois consécutifs, elle a droit à 10 jours de congé supplémentaires, à 5 jours pour une période de 6 mois consécutifs et à rien si moins de 6 mois.
Les données sont disposées sous forme d’un tableau simple dont voici un extrait :
Year Start date end date nbr months Leave 2018 1-Apr-18 31-Dec-18 9 10 2019 1-Jan-19 31-Mar-19 3 0 2020 1-Feb-20 31-Oct-20 9 10 2021 1-Jan-21 31-Dec-21 12 10 2022 1-Jan-22 31-Mar-22 3 0
D’autres données sont calculées dans ce tableau mais sans objet pour le cas présenté.
J’utilise les formules suivantes :
- pour le calcul du nombre de mois pour l’année (fonctionne correctement):
=IF(ISBLANK([@[end date]]),0,DATEDIF(B2,C2,"M") + 1)
- Pour le calcul des congés j'ai développé la formule suivante:
=IF(IF(AND(D2<>0,(DATEDIF(B2,C3,"m")+1)>=12),10),IF(AND(D2<>0,AND((DATEDIF(B2,C3,"m")+1)>=6,(DATEDIF(B2,C3,"m")+1)<12),5),0))
Je dois avoir une erreur de syntaxe quelque part car cela me renvoie FALSE comme résultat…au lieu de la valeur du nombre de jours obtenus.
La formule utilisée dans l’exemple ci-dessus:
=IF(AND([@[nbr months]]<>0,[@[nbr months]]>=6),IF(AND([@[nbr months]]+$D5>=6,[@[nbr months]]+$D5>=12),10,5),0)
fonctionne pour des conditions simples(mois consécutifs), mais n’est pas satisfaisante car donne des résultats erronés, comme dans le cas de l’année 2020, par exemple.
Comme toujours, plus je cherche moins je trouve et, donc, un regard extérieur serait le bienvenu.
Un autre challenge est la vérification de la « consécutivité » des mois prestés… !
En effet, comme dans l’exemple ci-dessus, une personne ayant presté dans le cadre d’un contrat d’un an, presté 9 mois une année puis 3 autres mois l’année suivante ne pourrait prétendre qu’a 5 jours supplémentaires pour la première année (9 mois prestés) et a rien pour la seconde (3 mois prestés), alors qu’en réalité, elle a droit à 10 jours pour les 12 mois prestés.
J’ai donc contourné le problème en utilisant dans la formule la date de début de période d’une année et la date de fin de l’année suivante, mais je ne contrôle pas qu’il n’y a pas d’interruption et cela pourrait donc conduire à une allocation erronée.
Je ne suis pas sûr que ce soit la meilleure façon de procéder…
Je suis donc ouvert à toute proposition de solution.
Je précise que les macros ne sont (malheureusement) pas autorisées dans mon environnement et qu’il faut donc tout faire avec de « simples » formules.
J’espère avoir été suffisamment clair dans mon explication pour que quelqu’un puisse me venir en aide.
Merci d’avance.
Yves
Répondre avec citation
et si celle-ci est pertinente pensez à voter 
Envoyé par Philippe Tulliez
Partager