Création d'un simulateur de calcul

Bonjour à tous,

je suis assistante en ressources humaines et notre logiciel de paie ne calcule pas le passage à demi traitement (salaire) des agents. Simplement parce que ma collectivité n'a pas acheté le module RH (assez coûteux pour une petite commune). Pour chaque arrêt maladie je dois donc calculer manuellement le ou les jour(s) où l'agent perçois du plein traitement ou demi traitement.

Si quelqu'un pouvait m'aider à créer un simulateur, cela m'aiderait beaucoup.

Les règles dans la fonction publique sont les suivantes :

  • droit à l'arrêt maladie pendant 1 an
  • les 90 premiers jours sont payés à plein traitement
  • les 270 jours suivants sont payés à demi-traitement
  • le calcul se fait sur année glissante donc on doit calculer le traitement chaque jour d'arrêt.

Les mois de paye dans la fpt sont des mois indivisibles de 30 jours. Exemple : la paie du mois de février, l'agent est payé 30/30ème, pareil pour les mois de 31 jours.

Par conséquent, si un agent a un arrêt maladie du 1er au 29 février : il aura 29 jours de maladie de décomptés mais en paye il aura 30/30ème de salaire.

Si l'arrêt est du 1er au 31 janvier : il aura 31 jours de maladie décomptés mais en paye 30 jours de payés

Pour le calcul du passage à demi traitement, on se base à l'année de référence mobile :

exemple : arrêt maladie du 16/11/2016 au 15/12/2016 :

on calcule pour chaque jour d'arrêt de cette période (le 16/11, le 17/11, le 18/11 ....) si l'agent a obtenu 90 jours de plein traitement ou non dans l'année précédent ce jour d'arrêt (du 16/11/2015 au 15/11/2016 pour le calcul du 16/11/2016, du 17/11/2015 au 16/11/2016 pour calculer le jour du 17/11/2016 etc).

on ne tient pas compte des jours fériés, de la durée de travail de l'agent, des congés annuels.

Voilà, j'ai quelques notions en tableur mais vraiment minimes, je suis assez novice avec excel surtout avec des formules.

Merci pour votre attention et votre aide.

Bonsoir,

Je n'aurais guère le temps de travailler sur ce type de calculs en ce moment... Mais je te livre le dernier fichier, résultant du dernier travail sur le sujet : cela devrait pouvoir t'aider...

Les seules colonnes à servir sont A et B début et fin de CM, ainsi que C2 (date d'entrée en service, si l'acquisition de droits se fait progressivement, selon tableau G4:K7 qui peut le cas échéant être modifié).

Pour initialiser, seules les colonnes A et B du tableau principal (et C2) sont à effacer (toutes les autres colonnes contiennent des formules, y compris des colonnes masquées).

Les dates se mettant en grisé indiquent des prolongations de CM (MFC qui opère cette mise en forme).

Des alertes (par MFC) peuvent apparaître : cellule se colorant en rouge si un CM est à cheval sur un période de changement de droits ; il convient alors de le scinder sur la date de changement pour qu'il soit correctement comptabilisé. Cellule se colorant en orange si on a fait une erreur manifeste de saisie de date.

La ligne 11 doit rester vide...

Le dernier ajout à ce fichier réside dans les colonnes P et Q : calcul pour chaque congé des jours pris en charge à 100% ou à 50%. Ce calcul est fait au moyen d'une fonction personnalisée matricielle (j'avoue qu'elle m'a fait un peu transpirer ! ).

La gestion des congés maladie étant dépendante de la situation antérieure (à un an), il importe de ne pas modifier le point de départ (ligne 12) et de ne pas sauter de lignes dans l'inscriptions des CM successifs...

A toi de voir !

Cordialement

7'461gestioncm-fonc.zip (40.03 Ko)

merci beaucoup MFerrand pour ce classeur.

En effet, il m'aide beaucoup.

J'ai mis les arrêts successifs d'un agent et je trouve un résultat différent avec ton classeur que ce qu'on a payé à l'agent.

Il semble que le classeur ne prend pas en compte les bonnes dates de référence.

Pour un arrêt du 16/11/2016 au 15/12/2016, l'année de référence mobile est du 16/11/2015 au 15/11/2016 (1 an juste avant le premier jour d'arrêt).

Il se trouve que mon agent a été en cm du 30/11/2015 au 29/02/2016 au cours duquel il a déjà obtenu 90 jours de PT, il devrait donc être en DT du 16/11 au 30/11/2016.

Bien sûr que non !

Le calcul est glissant au jour le jour.

A partir du 30/11 il repasse à plein traitement.

Par contre, la fonction d'identification des jours ne donne pas le bon détail... Là il faudra que je m'y repenche.

un exemple de calcul des arrêts que je vous joins pour mieux me faire comprendre.

Bien lire les quelques lignes sous le tableau car c'est de là qu'en découlent les bons calculs.

Merci encore.

4'558calcul-arrets-mo.pdf (8.49 Ko)

Je regarderais en détail plus tard (il est 4h30 cheez moi !), les règles de calcul indiquées en bas, au jour le jour, correspondent bien à ce que j'ai dit.

L'exemple que tu as donné est simple :du 30/11/2015 au 27/02/2016, ton agent à eu 90 jours à PT.

Du 16/11/2016 au 29/11/2016, il y a toujours 90 jours. A partir du 30, il n'y en a plus que 89, le 30 devient le 90e, et ainsi de suite par glissement jusqu'à la fin de ce congé.

oups désolé je ne savais pas pour l'heure. Tu es de quel pays ?

Vu l'heure chez moi aussi, peut être que la fatigue m'embrouille, je regarderai demain.

Merci de ton aide.

Je réside à La Réunion : +3h avec la métropole en ce moment.

Je suis volontiers nocturne mais il m'arrive de dormir (le problème c'est que dormir le jour m'ampute sérieusement d'autres activités...)

J'ai finalement mis tes exemples dans mon tableau : le premier donne rigoureusement les mêmes résultats, le second fait apparaître 2 petites erreurs de ton document :

  • congé du 05/09/2002 au 30/06/2002 ! sans conséquence car rectifié en dessous dans le détail (mais j'ai tapé bêtement et ma cellule s'est colorée en orange, j'ai donc rectifié en 12/11...)
  • congé du 03/01/2003 au 16/03/2003 : ton document compte globalement 74 jours alors qu'il n'y en a que 73. Quelqu'un a dû compter un 29 février alors que pour 2003, pas possible. Tu as donc un jour de plus dans les DT mais le décompte des PT est identique.

On applique donc bien les mêmes règles.

1'932gestioncm-foncex1.pdf (281.91 Ko)
2'500gestioncm-foncex2.pdf (281.58 Ko)

Bonjour MFerrand

Ton fichier est génial et ça fonctionne. On a vraiment tous les éléments, le nombre de jours décomptés en maladie et le nombre de jours pour la paye, les périodes avec le % de traitement, tout quoi. Je te remercie vraiment.

Juste une question pourquoi avoir mis la date du 24/05/1990 ? J'ai rentré la date de l'agent en C2 mais ça ne change rien vu que l'agent a 14 ans d'ancienneté.

Il me reste maintenant à essayer de créer un fichier de calcul de paie pour agent CNRACL mais ça c'est à titre perso, pas pour le boulot.

Excel c'est génial quand on sait s'en servir, si j'avais plus de temps pour apprendre, ça me dirait bien.

Merci pour tout et bonne journée.

Bonjour,

S'il y a des paliers d'ancienneté pour l'acquisition des droits (durée à PT), tu peux adapter l'échelle... En tous cas, l'adapter pour que ça colle avec ta situation (sans supprimer des paliers [il faut faire en sorte qu'ils n'aient pas d'effet s'ils ne doivent pas en avoir], car certaines formules risquent de ne pas aimer... !) Le premier original remonte à plusieurs années, je l'ai repris au moins 3 fois pour le compléter selon les demandes faites. Il faudrait que je fasse une vérification pour le rendre adaptable facilement à toutes situations... mais ça attendra un peu. En C2, il faut une date pour que le reste fonctionne : si la date réelle ne permet pas de cadrer la situation de droits, tu peux toujours en mettre une fictive qui fera cadrer...

Dans l'immédiat, tu m'as fait mettre le doigt sur une insuffisance de la fonction de calcul des jours pour les répartir en PT et DT.

J'ai découvert une petite anomalie réparée mais dont la réparation ne corrige pas l'erreur sur ton premier exemple. En fouillant j'ai découvert que tous les cas de chevauchements n'étaient pas pris en compte (le hasard a fait que dans tous les tests précédents ces cas n'étaient pas apparus...). J'ai donc reventilé les situations en 6 cas. Je pensais pouvoir apporter un correctif rapide, sans toucher à la structure mais 2 cas ne me permettent d'affecter des jours pris dans la période d'une année écoulée sans remonter aux dates de répartition concernée. Dans ces conditions il me faut modifier légèrement la structure du calcul pour tout aligner à partir de ces dates (pour que l'ensemble reste cohérent), ce qui va demander un peu plus de temps et que je ne pourrais y consacrer aujourd'hui.

Je reviendrai donc vers toi avec le correctif, une fois fait.

Cordialement.

j'ai regardé à nouveau le fichier et j'ai vu quelque chose qui ne va pas.

Dans les colonnes CM subrogés, il ne peut pas y avoir plus de 30 jours de payés, que ce soit à 100 % ou à 50 %.

En paye, la règle du 1/30ème indivisible s'applique.

Les jours de congés de maladie sont décomptés jour après jour en fonction du nombre de jours dans le mois, sans distinction

des jours fériés, ouvrés ou ouvrables.

En conséquence, si le décompte des jours de maladie se fait suivant la durée calendaire du mois, le calcul de la rémunération

afférente à ces congés se fait quant à lui suivant la règle du 1/30ème indivisible.

Exemple : un agent, en congé de maladie, qui aurait épuisé le plein traitement le 31 janvier percevrait, pour une

prolongation de congé du 1er au 28 février le demi-traitement sur la base de 30/30ème, étant précisé que ces jours de maladie

seraient décomptés à raison de 28 jours.

En vérifiant, avec ce que j'avais calculé manuellement j'ai vu cette erreur sur les colonnes J et K.

Evidemment, je ne sais pas corriger cela donc si tu as encore un peu de ton temps pour le corriger ?

Merci.

Bonsoir,

Pas d'erreur ! Ce fichier ne calcule que les congés de maladie et ne s'occupe pas de la paie. Les Cm ne sont d'ailleurs pas ventilés par mois. On peut l'introduire mais il faut me dire quel calcul est fait.

Ex. en avril ou juin pas de problème : 3 jours à DT => 27 j à PT et 3 j à DT pour le mois.

Qu'en est-il pour janvier ou juillet ? est-ce qu'on fait aussi 27 j PT/3j DT ou bien 31j-3j= 28j PT et 2j DT ?

Et pour le mois de février : 27j à PT et 3j à DT ?

NB- A mes débuts, (il y a for logntemps !) le problème ne se posait pas car les CM était décomptés sur la base de mois de 30 j (360j par an). Il y avait le nombre de jours réels et le nombre de jours comptable de CM (on gagnait 1 jour en chavauchant un31 et on en perdait 1 ou 2 en passant de février à mars !)...

On pourra faire une récap pour les mois dont la paie est impactée.

A+

bonjour,

d'accord, du coup les périodes indiquées à PT ou DT sont pas forcément justes pour la paye.

Je ne comprends pas tes questions

Ma question est simple : supposons que 3 jours ressortent à demi-traitement dans un mois :

Est-ce que cela va donner 27 trentièmes PT et 3 trentièmes DT quel que soit le mois ?

Ou bien le résultat sera-t-il différent selon qu'il s'agit d'un mois à 31 jours, 30 jours, ou février ?

Cordialement.

Bonjour,

Voilà déjà un classeur avec la fonction de répartition des jours recomposée...

La structure de détermination est la même qu'auparavant : on établit un tableau listant les jours du CM, pour chaque jour, on calcule les droits utilisés à 100% et à 50%, si le quota 100% n'est pas épuisé on y affecte le jour, sinon on l'affecte à 50% si le quota 50% n'est pas épuisé, sinon on ne l'affecte pas (il n'est pas pris en charge !)

Ce qui a changé, ce sont les calculs intermédiaires de détermination des droits en début de période et de repérage des jours sur lesquels vont s'appliquer des glissements... Tous les cas n'étaient pas expressément prévus dans l'ancien calcul, j'ai donc revu le balayage des cas et modifié les modalités de calcul dans les cas où les droits ne pouvaient être comptabilisés globalement mais exigeaient qu'on remonte à la répartition des jours de CM antérieurs en 100% ou 50%.

J'ai un peu transpiré encore là-dessus , je m'y attendais ! mais il m'a fallu quelques batteries de tests pour m'assurer des conversions de dates... Il faut dire que ton petit exemple du début sur la lequel la fonction avait buté, cumulait des difficultés potentielles : un chevauchement large sur 2 années et une fin de congé un 29 février (il y a un 29 février tous les 1461 jours, et les probabilités qu'un congé débute ou finisse à cette date ne sont pas très élevée, à plus forte raison que ce congé débute l'année précédente... )

J'attends maintenant tes précisions sur les modalités de calcul des incidences pour la paie...

Cordialement.

634gestioncm-nfonc2.zip (41.03 Ko)

Bonjour,

Je viens de tomber sur votre discussion concernant la création d'un simulateur de calcul. J'ai regardé votre tableau excel qui est très bien fait et je suis très intéressé par ce simulateur

J'ai fait un essai avec un congé maladie > 90 jours.

Normalement, il faudrait que le calcul se fasse en 30ème sur tous les mois (y compris février) et non en 31ème pour le maintien plein traitement 30j / 60j / 90j et demi traitement et sans traitement

Dans l'exemple en pièce jointe, les mois de 11/2015 et 01/2016 doivent être décomptés en 30 jours (donc passage à demi traitement au 20/01/2016)

Pour le mois de février, il doit aussi être décompté en 30 jours.

Merci d'avance si vous avez la possibilité de voir comment solutionner ce problème de 30ème

Patrick

Bonsoir,

Ce fichier est la dernière version d'un travail entamé il y a déjà un certain nombre d'années, et il a subi diverses évolutions du fait des origines diverses de demandeurs appliquant des conventions qui n'étaient pas identiques.

Au départ, j'avais commencé à établir le calcul sur les règles en vigueur à mon époque où l'on distinguait durée réelle et durée comptable des CM, la durée comptable étant établie sur une base 30/360...

Les demandes faisant état de façon générale de détermination en nombre de jours réels, j'ai donc repris le calcul selon les règles indiquées, ce qui a conduit à distinguer aussi plusieurs périodes de génération de droits selon l'ancienneté, ce qui explique qu'il en apparaît 4 dans le fichier, ce qui est loin d'être le cas partout (mais là c'est facilement adaptable pour ceux qui en ont moins).

Sur cette dernière discussion concernant ce calcul, j'ai pu vérifier que le mode de calcul correspondait bien à ce qui était demandé, à partir des documents du demandeur. On en était à la distinction entre décompte des jours et traduction sur la paie (fonctionnant elle par 30e). J'attendais des précisions règlementaires lorsque la discussion s'est interrompue... !

Si je comprends bien, en ce qui te concerne tu reviens sur le calcul de décompte lui-même.

Tout est possible... mais :

1) il faut que j'aie du temps pour ça,

2) il faut me fournir des textes de références complets des règles à appliquer, sans quoi je sais qu'on passe beaucoup de temps, beaucoup trop, en raison d'imprécisions dans les indications fournies.

Avec un texte règlementaire, je peux le soumettre à une analyse critique et le traduire en table de décision pour vérifier que tous les cas susceptibles de se produire sont bien prévus...

Cordialement.

Bonjour,

C'est avec un peu de retard que je vous réponds car très occupés en ce moment.

Merci pour votre réponse.

Je vais voir pour récupérer le texte concernant le décompte du plein traitement / demi traitement afin de vous le faire parvenir

Cordialement

Patrick

Bonjour,

Je suis très intéressée par ce simulateur.

J'ai commencé à l'utiliser pour la gestion des arrêts maladie de mon établissement.

Lorsque je rentre les dates d'arrêt, les colonnes P et Q qui comptabilisent les jours à PT et DT, ne 'alimentent pas : #NOM?

Comment puis-je faire fonctionner la formule?

Merci pour votre aide et encore bravo pour ce simulateur

Cécé33

Bonsoir,

Cette erreur indique qu'il te manque la fonction ?

Si tu as enregistré le fichier en xlsx par exemple, tu as du même coup supprimé le code.

Rechercher des sujets similaires à "creation simulateur calcul"