XL 2010 [Resolu] Calculer le nombre de mois consecutifs dans une formule

Droopyougo

XLDnaute Nouveau
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
 

Droopyougo

XLDnaute Nouveau
Bonjour JHA et merci pour ton aide.
J'ai regarde tes solutions et... cela fonctionne a condition de "tricher', c'est a dire d'inscrire la date de fin de periode dans l'annee courante, meme si elle est l'annee d'apres.
Ca fonctionne, mais cela ne repond pas exactement a mon besoin de pouvoir verifier la continuite de la periode.
En fait, il est possible que le tableau, a terme, n'existe pas et qu'il soit uniquement fait reference a des cellules de feuilles differentes (une par annee)... Dans ce cas, il sera beaucoup plus complique de "tricher". De plus, la periode contractuelle pourrait evoluer au cours de l'annee selon les besoins et, donc, il faut que le calcul s'adapte en consequence.
Je continue a creuser.
J'ai pense que cela pourrait peut etre fonctionner en incluant dans la formule un test permettant de verifier que le dernier jour de la periode d'une anne et le premier de la periode de l'annee suivante se suivent. Cela peut se faire facilement en faisant un calcul du style: Y+1(Start date) - Y(End date) = 1 et, dans le cas ou c'est verifie, on peut continuer le compte des mois sur les 2 annees.
Encore faut-il trouver le bon endroit pour l'inserer dans la formule (complexe) avec ses SI imbriques et ses ET.
Une fois qu'elle fonctionnera, il faudra encore la simplifier, si possible.
 

Droopyougo

XLDnaute Nouveau
Bonjour,
J'ai obtenu par ailleurs une solution qui fonctionne Presque comme voulu:

=INT(DATEDIF(B2,IF(MONTH(B3)=MONTH(C2+1),C3+1,C2+1),"m")/6)*5

Voici les resultats obtenus en utilisant cette formule:

capture.png


Ainsi, dans l'exemple ci-dessus, les annees 2020-2021-2022 forment une periode ininterrompue de 18 mois, ce qui devrait ouvrir le droit a 15 jours de conge. Or, la formule renvoi 10 jours en 2020 (1 + 12 = 13 > 12) et 10 autres jours en 2021 (12 mois).
La meme situation va se retrouver si on a plusieurs annees completes qui se suivent après une periode de moins de 6 mois.
Il faudrait donc sans doute ajouter une verification pour eviter ce type de situation, mais je suis encore en train de me triturer les meninges pour savoir comment...
Si tu as une idee...

Sinon, l'approche de cette formule est interessante: simple et efficace!
 

Discussions similaires