Problème de formule (en fin je pense)

J'ai déjà posé quelques questions pour cette feuille Excel mais là c'est un autre problème.

Un chiffre, qui sert a un calcul, change à un moment donné mais ce chiffre n'est pas utilisé pour la bonne période

C'est pas clair hein

Dans le fichier joint, il y a une autre explication qui me semble mieux.

Si vous avez besoin de plus de renseignement, n'hésitez pas.

Merci

40stagiaire-test.zip (169.79 Ko)

Bonjour,

Il me semble que tu simplifies quelque peu le problème ! Les droits s'apprécient sur la période d'un an antérieure au congé.

Ils peuvent évoluer en cours de congé, les jours sortant de la période d'un an s'éliminent et l'appréciation plein ou demi peut varier durant le congé, sans oublier la variation de droits pouvant intervenir durant le congé (obligeant à le scinder)...

Je ne vais pas te reconstituer un calcul complet à froid mais je dois avoir ça dans mes archives, établi sur des droits de 0, 30, 60 ou 90 selon ancienneté (tant à plein qu'à demi). Et il y a besoin d'un peu plus de colonnes pour finaliser les calculs, en décomptant séparément plein et demi...

Je veux bien te retrouver mon tableau et l'adapter s'il y a lieu. Il faudrait que tu précises de façon exhaustive quelles sont toutes les règles à appliquer dans ton cas.

Cordialement.

Bonjour,

Ils peuvent évoluer en cours de congé, les jours sortant de la période d'un an s'éliminent et l'appréciation plein ou demi peut varier durant le congé, sans oublier la variation de droits pouvant intervenir durant le congé (obligeant à le scinder)...

C'est exactement ce que vient de m'expliquer mon épouse pour qui je travaille sur ce tableau.

D'un petit tableau avec des + et des - c'est devenu une usine à gaz

Bien sur, si tu as déjà une ébauche pour ce genre de calcul je suis preneur. Quant a t'expliquer ce que je veux, tu l'as très bien décrit dans ton message.

J'avais déjà bossé un peu avant d'avoir la totalité des besoins voir PJ

Cordialement.

24m-toto.zip (168.38 Ko)

Re,

C'est samedi et je ne vais pas me lancer dans un travail de ce genre pour le weekend !

Je vais donc te faire passer le fichier en ma possession de façon que tu puisses l'examiner. Je veux bien par la suite intervenir pour essayer de l'adapter au cas qui te concerne...

En effet, les règles en la matière sont conventionnelles et peuvent varier d'une entreprise à l'autre...

Ce qu'il y a lieu de savoir sur l'origine de ce fichier : j'avais d'abord travaillé partiellement sur ce problème en 2011 (dans un autre forum) et produit un petit fichier de calcul (ne répondant d'ailleurs pas complètement à la problématique de gestion des CM). En 2014, le reprise de ce fichier par un demandeur qui souhaitait l'adapter dans des conditions présentant un certain nombre de différence, m'a amené à le repenser entièrement pour arriver au fichier actuel.

Quelques remarques sont nécessaires :

  • les jours décomptés le sont sur une base calendaire ; tous les jours de la date de début à la date de fin incluses sont comptabilisés comme CM ; bien sûr, les weekends n'interviennent pas, ce qui est une règle générale, mais on ne distingue pas une nombre de jours "réels" d'un nombre de jours "comptables" (ce qui ce faisait à mon époque, dans l'administration, par égalisation des mois à 30 jours....; j'ignore d'ailleurs ce qu'il en est aujourd'hui) ;
  • l'évaluation des droits après un congé n'a aucune importance, cela n'infère en aucune façon des droits qui seront à prendre en considération lors du congé suivant ;
  • l'évaluation des droits se fait en remontant d'un an à partir de la date de fin de congé (si les dates du congé recoupait un congé antérieur d'un an, l'évaluation glissante fait que chaque jour entrant est compensé par un jour sortant, ce qui n'impacte donc pas le calcul des droit), mais si cette date antériorisée tombe à l'intérieur d'un congé antérieur, le nombre de jours entre cette date et la fin de ce congé antérieur est à prendre en compte, selon le cas à 100% ou 50% ;
  • car en effet, le cumul des congés pris et des droits est à faire distinctement selon qu'ils sont subrogés (ou pris en charge) à 100% ou à 50% ;
  • le dernier élément intervenant dans le calcul des droits est l'ancienneté qui modifie le nombre de jours pris en charge : si la modification due à l'ancienneté intervient en cours de congé, il convient de scinder le congé, une première du début à la veille du changement de droits, la seconde à partir de la date du changement de droits, pour pouvoir évaluer exactement dans chacun des cas.

Le fichier présente donc :

  • dans l'en-tête un tableau des droits liés à l'ancienneté, à partir de la date d'entrée en service (on ira chercher les droits dans ce tableau à partir de formulation en RECHERCHEV...) ;
  • dans le tableau de gestion, les 3 premières colonnes (A à C) indiquent le congé : début, fin, nb de jours ; pas de difficultés, mais deux MFC d'alerte interviennent en colorant la cellule en B en orange si il y a une erreur manifeste de date (par exemple date de fin inférieure à date de début...) et en rouge si une date de changements de droits se trouve incluse dans le congé, il convient dans ce cas de le scinder en deux comme indiqué plus haut ;
  • la colonne D identifie les droits selon ancienneté par un code ;
  • les 3 colonnes suivantes (E à F) calculent le cumul de congés pris sur année glissante : en E, la date de référence pour l'évaluation, en F et G le calcul pour respectivement 100% et 50% ; ces formules de calculs sont matricielles ;
  • un correctif étant à apporter si la date de référence tombe dans un congé antérieur, des calculs intermédiaires sont effectués dans 3 colonnes masquées (M à O) : le nombre de jours brut à ajouter est calculé en M (nb de jours, déduction faite de ceux éventuellement non pris en charge, soit à 0%), N et O reprennent le calcul des jours à 50% et 100% du congé concerné afin de pouvoir déterminer où ajouter ces jours (50% ou 100% ou partage éventuel); ces formules sont également matricielles ;
  • les droits sont calculés en H et I, et les congés récapitulés de J à L (100%, 50%, 0%) ; pas de difficulté dans ces calculs.

Je te laisse étudier ça.

Bon weekend !

86gestioncm.zip (17.74 Ko)

OK merci et bon Week-end

Bonsoir,

Beau travail

J'ai fini pas décrypter un peu tout, seuls quelques mystères sont encore présents.

La MFC sensée mettre en rouge certaines dates (je suppose) ne s'est jamais manifestée, pas faute d'avoir essayé

Lorsque qu'un début et une fin de congés se suivent, la police change de couleur, quelle est son utilité?

Et le plus important, dans les colonnes P et Q, il y a des dates qui correspondent aux différentes prises en charge d'un congé maladie. Est il possible d'automatiser cela ??

Ex: pendant l'arrêt du 24/01/2011 au 03/02/2011, le "malade" est pris en charge sur deux régimes à 100% du 26/01 au 29/01 et à 50% les 24/01 au 25/01 et du 30/01 au 03/02.

Voilà c'est tout mais c'est un beau boulot, qui je pense à du demander un certain temps.

Bon week-end.

139gestioncm-fonc.zip (25.16 Ko)

Bonjour,

Pb de vue ? J'ai dû rétablir tous les éléments de la feuille à leur valeur par défaut pour parvenir à lire correctement... Je ne travaille pas sur un 26 pouces !

qu.1- Dans ton fichier, le dernier changement de droits lié à l'ancienneté est le 24/05/1993. Le tableau de gestion débute au 26/01/2010 : aucune chance que la MFC rouge puisse intervenir, il aurait fallu pour cela que tu fasse débuter la gestion 18 ans plus tôt et que le 24/05/1993 se situe dans un congé.

qu.2- Lorsqu'un début et une fin de congés se suivent, il s'agit légalement du même congé, la mise en grisé des dates qui se suivent le signale. Dans le cas visé à la qu.1, la scission en deux parties du congé est rendue obligatoire parce que les droits de prise en charge attachés à chacune des parties du congés diffèrent.

Si donc, tu avais noté un congé du 16/05 au 28/05/1993 : tu aurais eu d'une part la coloration rouge de la date du 28/05 t'indiquant la nécessité de scinder en un congé du 16/05 au 23/05/1993 (veille de la date figurant au tableau des changements de droits à l'ancienneté) et un du 24/05 au 28/05/1993.

Hormis ce cas, si une date de début suit la date de fin précédente, c'est qu'il s'agit d'une prolongation...

Note que ta simulation d'un congé de maladie ordinaire comportant 7 prolongations, je n'ai jamais vu dans la réalité !

qu.3- Les colonnes P et Q que tu as ajoutées pour les dates selon droits de prise en charge à 100% ou à 50% n'a d'intérêt que si le congé se partage entre jours à 100% et jours à 50% (extraire les dates correspondant aux 100%) ou jours à 50% et jours à 0% (extraire les jours à 50%). Je ne m'étais pas posé la question jusqu'à présent et on ne me l'avais jamais demandé !

Est-ce demandé par l'employeur ou la sécu. ?

Dans les 2 cas simulés, les dates correspondent les dates correspondent aux dates d'un congé antérieur qui décalées d'un an sont totalement incluses dans le congé actuel. Plus facile à voir en remontant dans le tableau que le calculer. Mais il peut y avoir des cas où cela est beaucoup moins visible.

J'ai quelque doute d'obtenir facilement le résultat avec une formule (dans la mesure où pour des calculs de dates anniversaires, plus simples, j'avais déjà trouvé judicieux de passer par des fonctions personnalisées) : là il faut définir une date de début et une date de fin, si tous les jours à 100% sont contigus, mais il peuvent être fractionnés en plusieurs périodes...

Je note la question pour m'y pencher, mais je ne promets rien dans l'immédiat... Me relancer si cela tarde trop.

Cordialement.

Bonjour,

Hormis ce cas, si une date de début suit la date de fin précédente, c'est qu'il s'agit d'une prolongation...

Note que ta simulation d'un congé de maladie ordinaire comportant 7 prolongations, je n'ai jamais vu dans la réalité !

ET pourtant ce sont des dates réelles de CM

Je ne m'étais pas posé la question jusqu'à présent et on ne me l'avais jamais demandé !

Est-ce demandé par l'employeur ou la sécu. ?

D'après mon épouse (que je n'oserais pas contredire ( aie pas la tête) c'est demandé par le service Paie et la mutuelle

Ok pour tout le reste

Bon 1er Mai et merci

OK ! Accorde-moi une semaine, je vois ça selon disponibilités.

OK, aucun problème

Petit rebonjour,

J'ai un peu regardé, sur mon fichier initial, je n'ai que des cas simples (les jours restent dans l'ordre...) mais en regardant de plus près, les glissements ne sont susceptibles d'intervenir que lorsque un congé antérieur d'un an chavauche ou est inclus (à un an d'intervalle) dans le congé traité. Si lors de l'ancien congé le statut des jours est homogène, on peut déduire ce qu'il en résulte pour le congé en cours. Mais dans le cas contraire, il faut savoir ce qu'il en est du statut (100% ou 50%) des jours de l'ancien congé pour voir l'impact sur le congé actuel et cela est à évaluer pratiquement jour par jour...

En réfléchissant, il faut que je vois la possibilité de faire une simulation à partir d'une période où les droits sont entiers, calculer le statut des jours de congé sur période qui pourrait couvrir en moyenne 700 à 800 jours et suivre le décompte à chaque jour du congé actuel (sur l'année qui précède).

Ceci dit si les jours sont systématiques datés par statut dès le départ, on peut peut-être remonter plus directement avec les dates calculées antérieurement.

Il me reste de toute façon à trouver la bonne méthode pour affecter à chaque date de CM un statut de 100% ou 50%, et examiner le décompte pour chaque jour du congé à traiter afin de définir son statut.

Ou bien, autre possibilité, si on saisit dans le tableau, chaque jour séparément, le calcul va indiquer si ce jour sera à 100% ou 50% (ou 0%). Il faudrait donc que je puisse simuler l'évaluation du résultat de la formule ad-hoc pour chaque jour et mémoriser le résultat. C'est une piste à explorer.

Faut que je soie bien réveillé et en forme pour me lancer là-dedans !

A+

Rebonjour,

Je suis donc venu à bout d'une fonction de calcul des détails d'un CM...

La fonction se nomme DETAILCM, elle accepte 2 arguments : la date de début du congé, et 1 ou 2 (1 si le calcul concerne les prises en charge à 100%, 2 si calcul des prises en charge à 50%).

La fonction ne se recalcule pas lors du recalcul automatique. Il faut donc la relancer (éditer et valider à nouveau) si l'on a besoin d'un nouveau calcul.

Elle est très personnalisée en ce sens qu'elle est construite sur la structure de la feuille (ce pourquoi le nombre d'arguments à fournir est si limité).

En particulier, elle utilise dans les calculs : la zone H4:J7 pour la définition des droits ;

Les colonnes A et B pour les dates de congés ;

Les colonnes J et K pour la comptabilisation des congés antérieurs (hors chevauchement à un an d'intervalle) ;

Les colonnes P et Q pour extraction plus précise des jours si chevauchement à un an.

En partant de la ligne du congé à définir, le recueil des données utiles au calcul se fait en remontant (sans aller au-delà de la ligne 12) : tant que le congé antérieur est postérieur aux dates du congé actuel antériorisées d'un an, les jour sont cumulés pour décompte des droits restant ; si le congé antérieur chevauche... on récupère les dates correspondant aux 100% et 50% de ce congé pour pouvoir tenir compte du glissement au jour le jour de la partie incluse dans les dates antériorisées du congé actuel.

Si donc on déplace les éléments visés ci-dessus, la fonction ne pourra plus fonctionner. Par ailleurs, elle ne peut calculer juste que si le même calcul a été fait antérieurement (période d'au moins un an !) et a produit les informations sous les mêmes formes : soit une chaîne de type jj/mm>>jj/mm et si plusieurs chaînes de ce type pour définir le congé, séparées par un point-virgule.

Dans la mesure où que l'on calcule les 100% ou les 50%, on est contraint de calculer l'ensemble des deux, je voulais parvenir à n'utiliser qu'une seule fois la fonction pour fournir les 2 résultats [mais Excel pense autrement ! ].

Il est cependant possible de la modifier légèrement pour une utilisation matricielle en sélectionnant les 2 cellules (même système qu'on a vu sur l'ancienneté...). Si cette solution est souhaitée, je ferai la modification...

Le code de la fonction est dans Module1 : la fonction proprement dite est assortie d'un tableau déclaré au niveau module, d'une Sub auxiliaire (qui intervient pour le recueil des éléments glissants) et d'une fonction auxiliaire qui lui compose la chaîne à renvoyer.

Cordialement.

64gestioncm-fonc.zip (37.09 Ko)

Bonjour MFerrand ,

Que dire de plus, si ce n'est que tu viens de faire un superbe boulot

Un petit test devrait valider la solution.

Les colonnes M, N et O sont masquées, ont elles une utilité dans les calculs vu que tu n'en parle pas dans les explications que tu me donnes ??? Et il y a un 90, tout seul en E5, sert il a quelque chose ????

Je te tiens au courant mais je penses qu'il n'y aura pas de réclamation

Bonne fin d'après-midi.

Les colonnes masquées M, N, O sont utilisées par les formules, il faut donc ne pas y toucher et laisser faire le boulot.

E5 par contre ? Ce doit être une vérification ponctuelle faite à un moment donné et qui n'a pas été supprimée. Tu peux le faire sans risque.

Bonne soirée.

Petite question: pour les test, j'ai rajouté un arrêt en fin de tableau (A et B) et si on tire la formule en P et Q après avoir rentré les dates aucun problème par contre si on tire les formules avant, on a #VALEUR! au lieu du résultat

Pour les colonnes M, N et O, ce qui est bizarre c'est que, à partir de la ligne 50 les formules ne sont pas tirées et cela marche quand même

sans titre

Bonne soirée aussi

Pour M N O c'est le plus souvent 0, donc sans impact en principe, mais ces valeurs sont utilisées par les formules des colonnes F et G, mieux vaudrait donc les tirer... !

Pour la formule, ainsi que je l'ai dit, elle ne se recalcule pas, il faut donc la relancer après avoir mis les dates, ou ne mettre la formule qu'après les dates...

C'était une mesure de prudence ! On peut toujours voir si on peut la rendre volatile sans dommage.

En fait le seul risque est de mettre tous les résultats de la colonne en erreur (vu qu'elle est interdépendante avec elle-même) .

Bonjour Mferrand,

Bon, les tests se passent bien

Le seul problème qui reste, c'est le rafraichissement des colonnes P et Q.

Je me suis aperçu quand glissant les formules des colonnes F à Q et en remplissant la cellule B avant la A et en faisant "Entrée" le calcul et l'affichage se faisait bien dans P et Q.

C'est un peu capilotracté comme solution mais c'est étonnant

Bonne journée

Bonjour,

Réveil tardif aujourd'hui... ! Pas le temps de voir ça tout de suite, mais je vais faire la modification pour une utilisation matricielle (calcul des deux cellules groupées), ce qui divisera le nombre de calculs par deux et devrait permettre de la rétablir en volatile sans risque.

Il restera toujours la dépendance avec les résultats de l'année antérieure, mais ça c'est inévitable, par définition.

A+

Bonjour,

Dans Function AttribCM , il y avait une ligne: Application.Volatile False que j'ai passé en True et à première vue ça fonctionne bien.

Je te tiens au courant si problème.

Voilà la fonction réaménagée en fonction matricielle et volatile...

79gestioncm-fonc.zip (42.37 Ko)
Rechercher des sujets similaires à "probleme formule fin pense"