Formules multiple

Bonjour à tous,

Le fichier est le suivant : http://www.k-upload.fr/afficher-fichier-2018-07-18-15071b2adcalculdroitc.xlsx.html

Je rencontre des difficultés quant aux formules à appliquer dans mon fichier Excel pour les cellules suivantes :

  1. G119 (CIF) : Je souhaiterai que la formule prenne en compte la date de fin de congés renseignée en D119 pour la comparer avec la date du jour J afin de déterminer le nombre de mois écoulé entre ces dates pour y appliquer le délai de franchise (exprimé en mois) égal à 1/12 de la durée, exprimée en heures, du CIF précédent (renseigné en E119). Ce délai ne peut toutefois ni être inférieur à 6 mois ni être supérieur à 6 ans (c. trav. art. L. 6322-11 et R. 6322-10).
    Ainsi si le délai de franchise décrit précédemment est dépassé le résultat en G119 sera de 365 jours, dans le cas contraire il sera de 0.

  2. G120 (CPF) : Je souhaiterai que la formule prenne constamment en compte le solde d’heure acquis en C120 auquel on soustrait les heures déjà utilisées en E120. Sur le résultat obtenu il doit être fait application de l’acquisition de :

  • 24 h supplémentaire par année de travail (D18) à compter de la date de fin du congé renseignée en D120, et ceux, jusqu’à l’acquisition d’un capital de 120 h, puis ;
  • 12 h par année de travail (D18) jusqu’à l’atteinte du plafond des 150 h maximum.
    Sachant que le salarié peut utiliser les heures CPF (résultat G120) à tout moment, le cas échéant les heures utilisées seront portées à l’addition de celles déjà présentes en E120.

La règle de calcul globale renseignée en G120 doit intégrer que lorsque le salarié n’a pas effectué une durée de travail à temps complet sur l’ensemble de l’année (ex. : temps partiel, embauche en cours d’année), l’alimentation du compte est calculée à due proportion, (quelque soit la règle d’acquisition applicable (24 H ou 12 H par an) et qui est déterminée par le seuil disponible de 120H en G120 avec prise en compte du solde précédent en C120 auquel il est préalablement déduit le temp utilisé en E120), du temps de travail effectué (le temps de travail sera renseigné en C18) et la durée à prendre en compte sera mentionnée en D18 seulement pour la tranche inférieure à 12 mois (caractérisant une durée de travail à temps incomplet sur l’ensemble de l’année).

L’alimentation du compte s’effectue donc au prorata entre le nombre d’heures effectuées et la durée annuelle de travail de référence (1607 h)/ an). Lorsque ce calcul aboutit à un nombre d’heures de formation comportant une décimale, ce chiffre est arrondi au nombre entier immédiatement supérieur (c. trav. art. R. 6323-1).

  1. G118 (Congé de formation de cadres et d’animateurs pour la jeunesse) je recherche la formule capable de prendre en compte la date de fin du congé précédent renseignée en D118 afin d’établir si ce dernier a eu lieu l’année précédente (31 décembre). Si tel et le cas le chiffre 6 apparaitra, dans le cas contraire c’est celui de 0 qui sera en G118.

  2. G117 (Formation jeune) je recherche la formule capable de prendre en compte la date de fin du congé précédent renseignée en D117 ainsi que la durée du congé précédent renseigné en E117 afin d’établir, respectivement, si ce dernier a eu lieu l’année précédente (31 décembre) et déduire, le cas échéant, les heures déjà comptabilisées de l’année en cours du reliquat du droit au congé à hauteur de 200h par an seulement dans l’hypothèse où la cellule D18 fait apparaitre une ancienneté égale ou supérieure à 3 mois (Si inférieure à 3 mois le résultat sera de 0). D’autre part la mention « Oui » en cellule C72 permet au salarié de cumuler le solde de l’année précédente renseigné en cellule C117 avec le résultat obtenu en G117. (Exemple : 100h renseigner en E117 pour un congé se terminant l’année précédente. Un salarié de + de 3 mois d’ancienneté. La cellule C72 = « Non ». Dans ce cas le résultat en G117 sera de 200h car le reliquat de 200h est renouvelé chaque année. En revanche si la date de fin de congés renseignée en D117 intervient dans l’année en cours le résultat sera de 200 h - D117)

  3. G114 (Formation des membres du comité d’entreprise) : je recherche la formule capable de prendre en compte la date de fin du congé précédent renseignée en D114 ainsi que la durée du congé précédent renseignée en E114 afin d’établir, respectivement, si ce dernier a eu lieu à une date d’une ancienneté supérieure à 4 ans et déduire, le cas échéant, les heures déjà comptabilisées dans les 4 années en cours du reliquat du droit au congé à hauteur de 5 jours tous les 4 ans. (Exemple : 1 jour renseigné en E114 pour un congé se terminant l’année précédente. Dans ce cas le résultat en G114 sera de 4 jours car le reliquat de 5 jours est renouvelé tous les 4 ans.

  4. G115 (Formation des membres du CHSCT) : je recherche la formule capable de prendre en compte la date de fin du congé précédent renseignée en D115 ainsi que la durée du congé précédent renseignée en E115 afin d’établir, respectivement, si ce dernier a eu lieu à une date d’une ancienneté supérieure à 4 ans et déduire, le cas échéant, les heures déjà comptabilisées dans les 4 année en cours du reliquat du droit au congé à hauteur de :

• 5 jours tous les 4 ans pour les entreprises de + de 300 salarié (C25 = « Oui »).
• 3 jours tous les 4 ans pour les entreprises de - de 300 salarié (C25 = « non »).
(Exemple : 1 jour renseigné en E115 pour un congé se terminant l’année précédente. Dans une entreprise de – de 300 salariés. Dans ce cas le résultat en G115 sera de 2 jours car le reliquat de 3 jours est renouvelé tous les 4 ans.

  1. G116 (Formation des membres du CSE) : je recherche la formule capable de prendre en compte la date de fin du congé précédent renseignée en D116 ainsi que la durée du congé précédent renseignée en E116 afin d’établir, respectivement, si ce dernier a eu lieu à une date d’une ancienneté supérieure à 4 ans et déduire, le cas échéant, les heures déjà comptabilisées dans les 4 années en cours du reliquat du droit au congé à hauteur de 8 jours tous les 4 ans (10 jours tous les 4 ans pour les entreprises de + de 300 salariés ((Exemple : 1 jour renseigné en E116 pour un congé se terminant l’année précédente. L’entreprise comporte + de 300 salariés (C25 = « Oui »). Dans ce cas le résultat en G116 sera de 9 jours car le reliquat de 10 jours est renouvelé tous les 4 ans.

  2. G121 (Formation économique, sociale et syndicale) : je recherche la formule capable de prendre dans un premier temps l’effectif de l’entreprise renseigné en E18 pour en déterminer la durée maximum de congés telle que renseignée dans le tableau N21. A cela il convient de prendre en compte la particularité suivante : Si l’effectif demeure inférieur à 24 et que la cellule C68 est renseignée « Oui » le résultat provisoire (début de formule) sera de 18 et non de 12.
    Ensuite il convient de soustraire à ce résultat l’ensemble des chiffres suivants :
    • Le chiffre ou somme ayant permis d’atteindre un résultat inférieur à 3 jours (5 jours pour les entreprises de + de 300 salariés : C25 « oui ») en G115. Exemple : L’entreprise comporte 350 salarié. La référence de limitation est donc de 180 jours. Le solde de congés disponible en G115 est de 3 jours. Le résultat en G121 sera donc de 180 – 2 (car le nombre de 3 jours en G115 atteste que 2 jours sur les 5 habituellement disponibles dans les entreprises de + de 300 salariés ont d’ores et déjà été pris)
    • Le chiffre ou somme ayant permis d’atteindre un résultat inférieur à 5 jours en G114. Exemple : L’entreprise comporte 350 salarié. La référence de limitation est donc de 180 jours. 2 jours au titre de la formation en G115 ont été pris (voir exemple plus haut). Le solde de congés disponible en G114 est de 3 jours. Le résultat en G121 sera donc de 180 – (2+2) (car le nombre de 3 jours en G114 atteste que 2 jours sur les 5 habituellement disponibles ont d’ores et déjà été pris).
    • Le chiffre ou somme ayant permis d’atteindre un résultat inférieur à 8 jours (10 jours pour les entreprises de + de 300 salariés : C25 = « oui ».) en G116. Exemple : L’entreprise comporte 350 salarié. La référence de limitation est donc de 180 jours. 4 jours au titre de la formation en G115 et G114 ont été pris (voir exemples précédents). Le solde de congés disponible en G116 est de 1 jours. Le résultat en G121 sera donc de 180 – (2+2+9) (car le nombre de 1 jours en G116 atteste que 9 jours sur les 10 habituellement disponibles ont d’ores et déjà été pris).
    • Le chiffre ou somme ayant permis d’atteindre un résultat inférieur à 6 jours (Seulement sous réserve que C70 = « Oui ») en G118. Exemple : L’entreprise comporte 350 salarié. La référence de limitation est donc de 180 jours. 13 jours au titre de la formation en G115, G114 et G116 ont été pris (voir exemples précédents). Le solde de congés disponible en G118 est de 6 jours. La cellule en C70 = « Oui ». Le résultat en G121 sera donc de 180 – (2+2+9+0) (car le nombre de 6 jours en G118 atteste que 0 jours sur les 6 habituellement disponibles ont été pris).

Merci bien pour votre aide

Cordialement

Bonjour,

C’est tout un programme! Je serai ravi de vous aider mais déjà dès le premier paragraphe, je m’y perds!
Dans l’idée si vous pouviez simplifier avec un autre fichier et quelques exemples, je pense que ca aiderait :wink:

Citation
G119 (CIF) : Je souhaiterai que la formule prenne en compte la date de fin de congés renseignée en D119 pour la comparer avec la date du jour J afin de déterminer le nombre de mois écoulé entre ces dates

Si vous pouviez réduire votre exemple aux cellules entre A1 et G10, votre excel est très grand, on s’y noie :frowning:
Sinon, ça c’est facile =DATEDIF(D119;AUJOURDHUI();“m”)

Citation
pour y appliquer le délai de franchise (exprimé en mois) égal à 1/12 de la durée, exprimée en heures, du CIF précédent (renseigné en E119).

Donc c’est 1/12 de 5h00 ? Soit 25 min ?

Si vous pouviez m’éclaircir je continuerai :slight_smile:

Bonne soirée!

Bonjour et merci bien pour votre aide.

“(…)pour y appliquer le délai de franchise (exprimé en mois) égal à 1/12 de la durée, exprimée en heures, du CIF précédent (renseigné en E119)(…)” : sur une formation d’une durée total 5h00, le délai de franchise est égale à 5/12 = 0,416 heures soit 25 minutes exprimée en mois. Donc 25 mois.

Une nouvelle foi merci infiniment.

Julien

Super!

Donc si j’ai bien compris (mais c’est pas simple votre histoire :wink: )
M X a fini ses congés 01/01/2018

  • On veut calculer l’écart en mois par rapport à une date D (j’ai pris 01/06/2018)
    La formule est : =DATEDIF(A1;B1;“m”) (à changer avec les bonnes cellules) soit 5 mois
  • On veut y appliquer le délai de franchise soit 1/12 de la durée du congés précédent (120 dans votre tableau)
    La formule serait : =1/12*E1 soit 10 mois
  • Et on vérifie si ce délais n’est pas inférieur à 6 mois ni supérieur à 365 jours sinon c’est 0.
    =SI(ET(J1<6;J1>12);0;365)

En une seule fois:
=SI(ET((1/12(DATEDIF(D8;H8;“m”)))<6;(1/12(DATEDIF(D8;H8;“m”)))>12);0;365)**

C’est ça ?

On va y arriver :wink:

EDIT:
Du coup, je regarde le point 2:

Je suis désolé mais là encore je butte.
24H supplémentaires de 25 années de travail, (soit 600 ?!) ca c’est bon mais “a compter de la date de fin congés” là c’est obscur.

Vous pourriez mettre des exemples ?
On va partir sur M. Marcel RAYMON, 25 années de travail, à temps partiel qui a pris des congés entre le 15/12/2017 et 03/01/2018. Si ca vous va, je viens bien qu’il illustre nos propos :slight_smile:

Point 3:

=SI(ANNEE(AUJOURDHUI())-1=ANNEE(D118);6;0)

Je bosse sur la suite

Bonsoir Nono,

pardon pour le retard dans ma réponse et merci bien pour ton aide.

Je ne serais en mesure de confirmer que la formule remplie correctement son rôle que lorsqu’elle sera entièrement finalisée.

J’ai essayé la formule proposée en y incluant les bonnes cellules ainsi que la donnée “aujourd’hui” de sorte que cela donne : =SI(ET((1/12(DATEDIF(D119;AUJOURDHUI();“m”)))<6;(1/12(DATEDIF(D119;AUJOURDHUI();“m”)))>12);0;365)**

Mais excel m’informe d’une erreur et refuse de l’enregistrer :frowning:

En outre le délai de franchise en deux CIF ne peut pas être inférieur à 6 mois ni supérieur à 6 ans (dans ta formule tu écris 12 mois, dois je remplacer >12 par >72?).

De plus j’aurais voulu ajouter la formule suivante pour qu’elle prenne en compte certains critères : =SI(ET(D18>=12;E119=0;C27=“Non”);365;SI(ET(D18>=36;E119=0;C27=“Oui”);365;SI(ET(E119=0;C29=“Oui”);365;SI(ET(E119>0;(E119/12)<6;D18>=12;C27=“Non”);0;SI(ET(E119>0;(E119/12)<6;D18>=36;C27=“Oui”);0;SI(ET(E119>0;(E119/12)>72;D18>=36;C27=“Oui”);365;0))))))

C’est moi qui suis désolé par cette galère juridique et du temps que tu dois passer…

Je te communique le fichier excel sur lequel je travail afin de t’apporter mon concours ce qui nous permettra de travailler sur les mêmes cellules.

point 2: 24H supplémentaires de 25 années de travail, (soit 600 ?!) : Oui sauf que le plafond de calcul d’acquisition du droit a ce congé s’élève à 150 heures àCalcul droit congés de formation.xlsx (119,7 Ko)
compter de la date de fin congés” renseigné en D120 dans l’hypothèse où cette cellule est renseignée. Dans l’hypothèse où la cellule D120 est vierge c’est uniquement à la cellule D18 qu’il convient de prendre en compte pour acquisition des heures de congés dont le calcul s’effectue en 2 étapes :
-24 h par année de travail à temps complet jusqu’à l’acquisition d’un capital de 120 h ;
-puis 12 h par année de travail à temps complet jusqu’à l’atteinte du plafond des 150 h.

150 heures étant le plafond maximum qu’elle que soit l’ancienneté du salarié :slight_smile:

exemples :
M. Marcel RAYMON, 25 années de travail, à temps partiel (17,5 heures de temps de travail par mois) qui a pris des congés entre le 15/12/2017 et 03/01/2018 pour 10 heures de formation au total.

Donc M. RAYMON à acquis son droit au CPF de la manière suivante :
-12 h (17,5 heures / 35 heures = 2. donc 24 heures (salarié à temps complet) par ans/2 = 12 heures par ans) par année de travail à 17,5 heures/mois jusqu’à l’acquisition d’un capital de 120 h ;
-puis 6 h (17,5 heures / 35 heures = 2. donc 12 heures (salarié à temps complet) par ans/2 = 6 heures par ans) par année de travail à temps complet jusqu’à l’atteinte du plafond des 150 h.

Donc M. RAYMON avait donc largement atteint le plafond de 150h de formation au 15/12/2017.

Il est parti en formation à cette date en utilisant 10h de formation, donc au 03/01/2018 il lui restait 140h de formation disponible.

Je vais de ce pas tester la formule du point 3 en y incluant mes critères que je n’ai volontairement pas exposé dans le problème pour ne pas ajouter une perte de temps inutile aux internautes, et je reviens vers toi pour te confirmer la bonne formule du point 3 plus tard.

A nouveau je te remercie infiniment pour l’aide inestimable que tu m’as déjà apporté.

Amicalement

Julien

Bonjour Nono.

Je viens de travailler sur la formule du point 3: G118 (Congé de formation de cadres et d’animateurs pour la jeunesse) je recherche la formule capable de prendre en compte la date de fin du congé précédent renseignée en D118 afin d’établir si ce dernier a eu lieu l’année précédente (31 décembre). Si tel et le cas le chiffre 6 apparaitra, dans le cas contraire c’est celui de 0 qui sera en G118.

J’y ai ajoute la condition du respect de l’age maximum renseigner en C70 ainsi que les conséquences d’une absence de prise de congé précédant et l’opération de déduction du temps déjà utilise sur l’année en cour, ce qui donne :
=SI(ET(ANNEE(AUJOURDHUI())-1=ANNEE(D118);C70=“Oui”);6;SI(ET(D118="";C70=“Oui”);6;SI(ET(ANNEE(AUJOURDHUI())=ANNEE(D118);C70=“Oui”);6-E118;0)))

Cela fonctionne parfaitement !

Pour info, les fondements juridiques qui détermine les critères a appliqué pour chaque congés sont présent en colonne N avec une seconde parti en N319.

Amicalement.

Julien

Slt Nono,

j’ai enchaîné avec le point N°4 (G117), grâce à tes conseils j’ai appliqué la formule de base communiquée au titre du point N°3 pour le transposer ce cette formule (Conditions et mode législatif de calcul similaire)…

Bref, j’ai donc réglé le point N°4 qui fonctionne parfaitement lui aussi.

La formule du point 4 est donc la suivante: =SI(ET(ANNEE(AUJOURDHUI())-1=ANNEE(D117);C70=“Oui”;D19>=3);200;SI(ET(D117="";C70=“Oui”;D19>=3);200;SI(ET(ANNEE(AUJOURDHUI())=ANNEE(D117);C70=“Oui”;D19>=3);200-E117;SI(ET(ANNEE(AUJOURDHUI())-1=ANNEE(D117);C70=“Oui”;C72=“Oui”;D19>=3);200+D72;SI(ET(D117="";C70=“Oui”;C72=“Oui”;D19>=3);200+D72;SI(ET(ANNEE(AUJOURDHUI())=ANNEE(D117);C70=“Oui”;C72=“Oui”;D19>=3);200+D72-E117;0))))))

J’ai également modifié la structure du tableau pour y intégrer une formule capable de calculer l’ancienneté (en nombre de mois) du salarié en cellule D19 a partir de la date d’entrée dans les effectifs.

Voici le fichier en l’état à cette heure sur lequel tu peux te reposer pour me fournir ton aide si tu le souhaite.Calcul droit congés de formation.xlsx (121,0 Ko)

Bien à toi.

Julien

1 J'aime

Super!!

Tu avances beaucoup plus vite que moi pour le coup :wink:
Je vais tenter de m’y pencher demain, je télécharge de suite la dernière version :slight_smile:

Aller zou, c’est parti!

Bonjour Nono,

Je reste a ta disposition pour la suite des formules.

Avec tous mes remerciements.

Cordialement

Salut Julien,

Faut que je m’y remette, je suis en vacances, je devrai pouvoir trouver un peu de temps :wink:

A très vite !

Salut Nono,

Nan passe tranquillement tes vacances… ce n’est pas urgent.

Bonne vacances et surtout bon repos.

Salut Julien :slight_smile:

Très rapidement ce soir, j’ai regardé la première formule:

C’est normal qu’elle ne fonctionne pas les “*” et les “” sont interprétés différemment par le forum et excel du coup, il faudra que tu changes les “” par les guillemets d’excel que tu supprimes les espaces de la formule.
De plus, je crois que je me suis grave loupé dans la formule: du coup c’était pas ET mais OU et j’avais zappé E119 :sweat_smile: C’est ça de bosser sur la logique avec des exemples et de bêtement l’appliquer après :face_with_raised_eyebrow:

Du coup la formule en G119:

=SI(OU(((E119 * 1/12) * (DATEDIF(D119;AUJOURDHUI();“m”)))<6;((E119 * 1/12) * (DATEDIF(D119;AUJOURDHUI();“m”)))>72);365;0)

Avec tes conditions à toi:
(j’ai volontairement rajouter un OU par y voir plus clair dans les conditions)

=SI(OU(
OU(((E119 * 1/12) * (DATEDIF(D119;AUJOURDHUI();“m”)))<6;((E119 * 1/12) * (DATEDIF(D119;AUJOURDHUI();“m”)))>72);
ET(D18>=12;E119=0;C27=“Non”);
ET(D18>=36;E119=0;C27=“Oui”);
ET(E119=0;C29=“Oui”);
ET(E119>0;(E119/12)>72;D18>=36;C27=“Oui”));365;
SI(OU(
ET(E119>0;(E119/12)<6;D18>=12;C27=“Non”);
ET(E119>0;(E119/12)<6;D18>=36;C27=“Oui”));0;“Autre cas”))

Je n’ai pas du tout vérifier l’ordre des conditions, mais il très important. Je te laisse vérifier si ça rocks! :laughing:

Je (re)pars sur la suite :wink:

Salut Nono et merci pour ta nouvelle intervention;

J’ai modifié la formule de la manière suivante afin qu’elle prenne en compte que la période de franchise entre 2 CIF ne peut être inférieure à 6 mois : =SI(OU(
OU(((E1191/12)(DATEDIF(D119;AUJOURDHUI();“m”)))>6;((E1191/12)(DATEDIF(D119;AUJOURDHUI();“m”)))>72);
ET(D19>=12;E119=0;C27=“Non”);
ET(D19>=36;E119=0;C27=“Oui”);
ET(E119=0;C29=“Oui”);
ET(E119>0;(E119/12)>72;D19>=36;C27=“Oui”));365;
SI(OU(
ET(E119>0;(E119/12)<6;D19>=12;C27=“Non”);
ET(E119>0;(E119/12)<6;D19>=36;C27=“Oui”));0;“Autre cas”))

En outre j’ai fait une évaluation de la parcelle de la formule qui semble poser problème et j’ai trouvé ceci :
SI(OU(755>6;((E1191/12)*(DATEDIF(D119;AUJOURDHUI();“m”)))>72);365;0).
Il semblerait qu’Excel multiplie le résultat de 1/12ième par le nombre de mois écoulé depuis la fin du congé précédent.
Or, Excel doit simplement comparer le résultat du 1/12ième (résultat qui est exact et qui correspond bien à 75 mois dans l’exemple que j’ai pris pour faire mes tests) pour voir si celui-ci n’est pas inférieur à 6 mois ni supérieur à 72 mois.
J’ai oublié de préciser que si cette comparaison est inférieure à 6 mois alors le résultat est 0 ;
S’il est supérieur à 72 mois il sera de 365.
Sur quelle partie de la formule est-il conseillé de respecter l’ordre des critères dont tu fais référence ?
Je continus de bosser de mon coté pour voir si je trouve une solution avant toi :wink:

Amicalement

Julien.

Voici la formule avec les * :
=SI(OU(
OU(((E119 * 1/12) * (DATEDIF(D119;AUJOURDHUI();“m”)))>6;((E119 * 1/12) * (DATEDIF(D119;AUJOURDHUI();“m”)))>72);
ET(D19>=12;E119=0;C27=“Non”);
ET(D19>=36;E119=0;C27=“Oui”);
ET(E119=0;C29=“Oui”);
ET(E119>0;(E119/12)>72;D19>=36;C27=“Oui”));365;
SI(OU(
ET(E119>0;(E119/12)<6;D19>=12;C27=“Non”);
ET(E119>0;(E119/12)<6;D19>=36;C27=“Oui”));0;“Autre cas”))

En outre j’ai fait une évaluation de la parcelle de la formule qui semble poser problème et j’ai trouvé ceci :
SI(OU(75 * 5>6;((E119 * 1/12) * (DATEDIF(D119;AUJOURDHUI();“m”)))>72);365;0).

Ce sujet a été automatiquement fermé après 30 jours. Aucune réponse n’est permise dorénavant.