Calcul de primes de déplacement

[Nosfell]

Bonjour,

Je souhaiterais déterminer des montants de primes de déplacement en fonction de différents critères (code prime de la personne en déplacement, période de déplacement…). Il faudrait créer une Macro permettant d’automatiser le calcul.

La structure de base du tableau avec des essais de calculs est dans l’onglet « essai calcul prime ». J’ai aussi crée un cahier des charges colonne par colonne pour expliquer les calculs à réaliser. De plus, j’ai rentré les bases de données nécessaires au calcul.

Les calculs étant assez complexes, je n’ai pas trouvé de formules adaptées.

Merci de votre aide.

Nouvel outil de calcul prime.xlsx (47,2 Ko)

[FFO]

Bonjour

Je crois que l’on se retrouve
Je t’ai déjà fais une proposition concernant le calcul des jours ouvrés sur une plage déterminée par une date de début et une date de fin et ce sur un autre échange pour lequel je n’ai pas eu de réponse te concernant
Pas cool !!!

J’ai longuement étudié ton fichier et surtout un onglet « Cahier des Charges » très copieux

Ci-joint une première ébauche

Onglet « Essai calcul prime » un bouton « Traitement » cellule B2

J’ai laissé tes lignes avec tes formules de la ligne 4 à la ligne 18

J’ai rapporté toutes tes données colonne A à L à partir de la ligne 21

Ainsi le bouton pour l’instant alimente ces nouvelles lignes uniquement à partir de la colonne M à AB selon ton cahier des charges

Je ne traite que ce qui est travaillé n’ayant que des colonnes de ce type
Dans ton cahier des charges pour certaines tu demande en plus de ce type de prévoir celui du non travaillé (Samedi travaillé et Samedi non travaillé) :

« On distinguera les samedis travaillés, des samedis non travaillés: donc prévoir un bouton permettant de préciser cela à la main »

Aucune colonne Samedi non travaillé !!!

Difficile de mettre en place quoi que ce soit dans un tel contexte

Donc mes calcules sont tous pour des jours travaillés seuls ayant une colonne en rapport

Attention à l’écriture de tes données :

Cellule A5 onglet « Essai calcul prime » la donnée "Personne 1 " est pollué par un espace en fin

Attention aussi à la casse :

Impossible pour les traitement d’avoir à gérer 2 écritures différentes :

TAC MONDE
TAC Monde

Ces 2 données en l’état sont pour les procédures totalement différentes donc il faut les harmoniser

Après avoir corrigé ces imperfections le bouton traitement alimente les lignes 21 à 34 colonne M à AB

les colonnes M/N/O/P/Q/R ont les même résultats à l’exception d’une donnée colonne O mise en rouge concernant ta formule car je pense qu’elle n’obéit pas à ton cahier des charges et donc pour moi est erronée alors que l’identique du traitement me semble plus en harmonie avec l’attendu que tu souhaites onglet « Cahier des Charges »

A toi de me dire et m’expliquer

Pour le reste difficile de comparer car beaucoup de tes formules sont en erreur et pour celles qui ne le sont pas le résultat pour une raison qui m’échappe sont différents alors que tes conditions exprimées sont respectées

A toi de voir colonne par colonne et me dire

Pour l’instant c’est une ébauche une fois celle-ci mise au point je ferais en sorte de démarrer le traitement à partir de la ligne 5

Pour la recopie des données de base de la colonne A à L il me faudrait des précisions sur la source (Fichier onglet disposition par colonne etc…)

Déjà regarde cette première approche et merci d’avance pour ta réponse

Attention ce fichier est de type .xlsm nécessaire au fonctionnement des macros
A conserver impérativement

Nouvel outil de calcul prime.xlsm (64,1 Ko)

[Nosfell]

Bonjour et merci de votre réponse !

C’est la première fois que j’échange avec vous.

Pour ce qui est de la distinction entre samedi travaillé et non-travaillé, c’est en effet assez compliqué de mettre en place quoi que ce soit. L’extraction ne fait pas la différence entre samedi travaillé et non travaillé, l’info est donnée de manière annexe.

Pour ce qui est de la cellule mise en rouge, colonne O, le résultat n’est pas cohérent car les horaires étaient faux ( début 13h et fin 11h, le même jour).

Colonne par colonne:

colonne P: le format a utilisé est le format nombre ( pas monétaire ). Par ailleurs le problème ici est que pour le moment, la formule rentrée ne me permets pas de calculer les demi-journées, en fonction de la date de début et de fin de la période. L’heure qui nous fait passée d’une demi-journée à une journée est 12h.

Colonne Q et R : Même problème que pour P ( demi-journées ). Précision colonne R, pour la case que vous avez mise en rouge, le résultat est bien 3 car dans la période il y a bien un jour férié et deux dimanches.

Colonnes S à Z: Les résultats trouvés sur les colonnes suivantes ( colonnes où je n’avais pas trouvé de formules ) sont correctes. Néanmoins ils sont correctes pour le décompte actuel des jours compris dans les différentes périodes ( on ne compte pas encore les demi-samedis, demi jours ouvrés… ).

Colonne AA: Comme précisé dans le cahier des charges, les monteurs et formateurs ne touchent pas de primes métiers donc le résultat dans cette colonne sera de 0 pour eux. Les autres résultats sont correctes.

Colonne AB: Après réflexion, cette colonne est inutile, car elle est identique à la colonne V. Il ne faut pas ici rajouter les jours de récup, ils sont indépendants.

Merci beaucoup pour tout le travail que vous avez effectué sur ce document, jusqu’à maintenant.

je reviens vers vous pour la source.

Bonne journée!

je vous joins le document avec les quelques modifs: Nouvel outil de calcul prime.xlsm (58,6 Ko)

[FFO]

Bonjour

Regarde ce Post :

Formule décompte jours entre 2 dates

Etrange similitude avec ta demande notamment dans le calcul de jours séparant 2 dates

Ta démarche est un véritable clone avec celle qui a été exprimé dans ce fil au point où je pensais que l’interlocuteur avait ouvert un autre échange

« Précision colonne R, pour la case que vous avez mise en rouge, le résultat est bien 3 car dans la période il y a bien un jour férié et deux dimanches. »

Désolé mais là pas d’autre moyen que de lister les jours fériés pour déterminer leur présence ou non
afin de les capitaliser dans cette colonne
Il faudra prévoir dans un onglet d’établir cette liste si tu veux pouvoir les inclure dans les traitements

Tu ne me dis pas si mes résultats sont conformes ou non et si ils ne le sont pas de me donner pour chacun un ou 2 exemples de calcul pour obtenir ceux attendus avec tous les détails

Sans retour sur mes propositions et des explications sur ce qui ne convient pas je ne pourrais guère aller plus loin pour t’aider

Ce dernier document que dois en faire ???

Quelles sont tes attentes

Merci de me dire plus

[Nosfell]

En effet, il s’agit du même document, vous aviez communiqué avec ma collègue.

1. Concernant les jours fériés, ceux-ci sont déjà listés dans l’onglet : " BASE2".

2. J’ai, sur le document Excel, détaillé l’ensemble des résultats attendus. J’ai mis en place un code couleur:
   . rouge = résultat non conforme et résultat attendu: note ajoutée pour chaque case en rouge.
   . vert = résultat conforme
   . orange = résultat faussé par les colonnes précédentes mais conformes par rapport aux attendus. Ainsi, seul le nombre de jours est incorrecte et fausse le calcul.

Mes attentes: Le but de ce tableau de calcul est de faciliter le calcul des primes de déplacement qui prend en compte beaucoup de facteurs comme vous pouvez le voir. Malheureusement, je n’ai pas de formules à vous proposer pour la plupart des calculs où les résultats sont non-conformes.

Ce document serait utilisé chaque mois, ainsi le tableau doit prendre en compte l’évolution du nombre de jours fériés par mois… Nouvel outil de calcul prime 2.xlsm (61,6 Ko)

N’hésitez pas à me recontacter si mes explications ne sont pas assez claires.

Merci

[FFO]

Ci-joint ton dernier fichier avec l’intégration des jours fériés sur la base de la liste onglet « BASE2 » colonne A

Le traitement n’a pas intégralement obtenu tes mêmes valeurs concernant la répartition du nombre de jours ouvrés samedi et dimanche+jours fériés

Tu trouveras à partir de la ligne 46 colonnes P/Q/R les explications quant aux valeurs obtenues par celui-ci qui conforte sa validité

Merci de regarder d’analyser et de me donner un retour quant aux divergences des résultats

Dans l’attente de te lire

Nouvel outil de calcul prime 2.xlsm (69,8 Ko)

[Nosfell]

Les résultats que vous avez trouvé sont plus cohérents concernant les jours fériés. J’avais fais quelques oublis ( j’ai commenté vos encadrés à partir de la ligne 49).

Par ailleurs, les résultats des lignes 27 et 28 sont toujours non-conformes puisqu’ils ne prennent pas en compte les demi-journées. Les résultats sont arrondis à l’unité du dessus ( ici 1 ). Trouver une formule ici est très important et je continue de chercher de mon côté.

D’autre part, je reviens sur les samedis et dimanches non-travaillés. Dans l’extraction, la colonne motif permet de faire la distinction entre samedi/dimanche travaillé et samedi/dimanche non-travaillé.

En effet, lorsque la personne passe un samedi et/ou un dimanche non travaillé, en déplacement, cette période non travaillée aura une ligne lui étant dédiée et un motif particulier.

Exemple : la personne part du 26 au 30 mai mais le samedi 29 et le dimanche 30 sont non-travaillés.
On aura alors deux lignes avec deux périodes. Le motif pour la période du 26 au 28 sera « banal » et à ne pas prendre en compte. Mais pour la période du 29 au 30, le motif : " EXPORT STAYING DAY " nous permettra de dire que ce week-end est non-travaillé -> voir lignes 14 et 19/20 de l’onglet « essai calcul primes ».

Enfin, je tenais à préciser que la base située dans l’onglet « BASE », zone J1:M62, sera alimentée régulièrement avec de nouvelles personnes partant en déplacement.

Merci de votre réponse.
Nouvel outil de calcul prime 2 (2).xlsm (65,2 Ko)

[FFO]

« Ligne 27 : 18/03/2021 est un jeudi non férié inexistant colonne A onglet BASE2 donc 1 jour ouvré : D’accord avec cela mais problème des demi journées »

Désolé de te contredire mais l’heure Début est 08H00 et l’heure de fin 18H00 donc pas de demi journée mais journée entière ou je n’ai absolument rien compris dans la gestion des demis journée en rapport
La il faut m’expliquer dans cette alternative

Idem pour ceci :

« Ligne 28 : 18/03/2021 est un jeudi non férié inexistant colonne A onglet BASE2 donc 1 jour ouvré : D’accord avec cela mais problème des demi journées »

Désolé de te contredire mais l’heure Début est 08H00 et l’heure de fin 13H00 (au delà des 12H00) donc pas de demi journée mais journée entière ou je n’ai absolument rien compris dans la gestion des demis journée en rapport

La aussi il faut m’expliquer dans cette alternative

Petit rappel de ton cahier des charges onglet « Cahier des Charges » colonne D :
Si heure de début < 12h mais heure de fin > 12h, on comptabilisera une journée entière.

C’est exactement le cas de ces 2 lignes

"En effet, lorsque la personne passe un samedi et/ou un dimanche non travaillé, en déplacement, cette période non travaillée aura une ligne lui étant dédiée et un motif particulier.

Exemple : la personne part du 26 au 30 mai mais le samedi 29 et le dimanche 30 sont non-travaillés.
On aura alors deux lignes avec deux périodes. Le motif pour la période du 26 au 28 sera « banal » et à ne pas prendre en compte. Mais pour la période du 29 au 30, le motif : " EXPORT STAYING DAY " nous permettra de dire que ce week-end est non-travaillé -> voir lignes 14 et 19/20 de l’onglet « essai calcul primes » ."

Cela ne me dis pas concrètement l’impact sur le ou les résultat à obtenir
Merci de me dire ce qui change dans un cas ou l’autre étayé d’exemples se serait l’idéal

Merci pour tes explications

[Nosfell]

Petite erreur de ma part, il s’agissait des lignes 29 et 30 ( contiennent des demi-journées ). En effet les lignes 27 et 28 sont bien des périodes sans demi-journées. Désolé.

Pour les jours travaillés et non-travaillés, le détail des montant est dans l’onglet « BASE » dans la zone A4:E70. Ainsi, ligne 8, il est précisé que pour un TAC FRA, un samedi non-travaillé correspond à 53.61€ et 0 jour de récup. Dans l’onglet « essai calcul prime », j’ai entré les résultats à obtenir pour les lignes où le motif est : « EXPORT STAYING DAY » ( lignes 14 et 19/20 ).

Autre exemple : Si motif « EXPORT STAYNIG DAY », code prime métier : TAC FRONTALIER, date et heure de début: samedi 26 Juin 8h et date et heure de fin : dimanche 27 Juin 13h.
Alors: on aura 107.22€ de prime de déplacement, 1 jour de récup et 359.85€ de prime métier pour le mois. Il faudra bien sûr diminuer le montant de prime métier, si la personne est en déplacement sur d’autres périodes dans le mois. Ainsi, si par ailleurs elle part durant 4 jours ouvrés, son montant de prime métier sera de 359.85 - 4*13.53 = 305.73€.

Précision: Je ne sais pas si je l’ai précisé auparavant mais une même personne peut partir sur plusieurs périodes dans un même mois: ainsi on retrouvera une même personne sur plusieurs lignes du tableau.

Merci de me dire si mes explications sont claires, je joins le doc avec les modifications lignes 14 et 19/20.
Nouvel outil de calcul prime 2 (2).xlsm (65,3 Ko)

[FFO]

Bonjour

« Petite erreur de ma part, il s’agissait des lignes 29 et 30 ( contiennent des demi-journées ). En effet les lignes 27 et 28 sont bien des périodes sans demi-journées. Désolé. »

Oui mais ces lignes 29 et 30 ont bien dans leur décompte colonne O la demi journée : 0,5

Qu’est ce qui ne convient pas les concernant ???

« Pour les jours travaillés et non-travaillés, le détail des montant est dans l’onglet « BASE » dans la zone A4:E70. Ainsi, ligne 8, il est précisé que pour un TAC FRA, un samedi non-travaillé correspond à 53.61€ et 0 jour de récup. Dans l’onglet « essai calcul prime », j’ai entré les résultats à obtenir pour les lignes où le motif est : « EXPORT STAYING DAY » ( lignes 14 et 19/20 ). »

J’ai regardé ton dernier classeur et oh stupeur tu m’as rajouté des colonnes !!!
Tous mes traitements sont à revoir de ce fait
Donc avant d’aller plus loin j’ai besoin d’une structure définitive car toute modification de celle-ci met à mal les procédures qui commencent à être très conséquentes
Je te laisse imaginer le travail à réaliser pour me caler sur ce nouveau classeur
Donc en conséquence avant de poursuivre établit moi un classeur stable au niveau des colonnes de chaque onglet
Ainsi de pouvoir m’investir non en pur perte
Dans l’attente de ton fichier stabilisé

Petite suggestion au lieu de colonnes supplémentaires pour distinguer les samedi ou dimanche travaillés ou non travaillés ne pas mettre qu’une seule colonne pour chacun samedi et dimanche de ces 2 jours de semaines dans lequel il y aurait le nombre qui serait ensuite utilisé en fonction de son caractère travaillé ou non travaillé déterminé par la donnée « EXPORT STAYING DAY » et de réaliser tout les calcules qui en découlent en fonction
Ainsi d’éviter d’alourdir mes traitements et l’onglet de colonnes supplémentaires
Qu’en penses tu ?

[FFO]

Je te joint ton dernier fichier élaboré tel ma suggestion

Je retrouve colonne M à AA pour les lignes en noir les mêmes valeurs
Sauf la dernière car pour le Code prime métier « Monteur » je ne trouve pas onglet « Base » « samedi non travaillé »
Les données s’en rapprochant : « samedi non travaillé France »
Il faut les harmoniser tous sinon impossible d’automatiser
Donc mettre impérativement « samedi non travaillé » soit en corrigeant celui qui s’en rapproche soit en rajoutant une ligne identique avec ce libellé
A toi de voir

Pourtant qu’une seule colonne samedi et dimanche

Cette présentation n’altère en rien la qualité des résultats obtenus et allège l’onglet ainsi que mes codes

Regardes et dis moi

Nouvel outil de calcul prime 3.xlsm (70,6 Ko)

[Nosfell]

Je reviens vers vous d’ici la fin de la journée, je dois m’assurer de quelques éléments.

[Nosfell]

Je n’ai pas encore tous les éléments que j’attendais, je reviens vers vous demain.

[Nosfell]

Tout à fait, la colonne O prend bien compte les demi-journées mais les colonnes P, Q et R ne décompte pas les demi-journées, l’arrondi est fait à la journée entière.

Pour les colonnes Q et R, si j’ai bien compris, les jours travaillés et non travaillés sont comptabilisés ? Et par la suite, la différence de montant de prime entre jours travaillés et non travaillés est bien faite.

Je pourrais ainsi renommer les colonnes Q et R : « samedi travaillé et non-travaillé » et " dimanche+ jour férié, travaillé et non-travaillé » ? Si oui, en effet cela serait parfait.

Par ailleurs, le cas des monteurs est quelque peu compliqué, en effet, on distinguera le monteur partant en export et le monteur restant en France car cela a un impact sur certains montants de prime de déplacements. Par ailleurs, les montants changent seulement pour des périodes de déplacement en weekend. (Voir BASE).

Ainsi, si sur une période, le monteur passe un samedi et un dimanche non-travaillés en Belgique, il sera notifié dans la colonne Pays : « BEL » (les trois premières lettres du pays). Le montant d’indemnités de déplacement sera alors de 105.12€ avec 1 jour de récup car il sera en export.

Autre exemple : Si un monteur passe 2 jours travaillés en France, il sera notifié dans la colonne pays : « FRA ». Le montant d’indemnités de déplacement sera de 78.84 avec 3 jours de récup.

Nous pouvons donc imaginer que pour faire la différence entre Monteurs partis en export et Monteurs restés en France, nous prenions en compte cette colonne Pays. La condition serait de type : « si (pays <> FRA ) alors export ».

[Nosfell]

La base quelque peu modifiée dans les énoncés « Monteur » ( pas d’ajout de colonne ) : Nouvel outil de calcul prime 4 (1).xlsm (65,1 Ko)

[FFO]

Bonjour

Tout à fait, la colonne O prend bien compte les demi-journées mais les colonnes P, Q et R ne décompte pas les demi-journées, l’arrondi est fait à la journée entière.

C’est à l’identique de tes résultats exemple ce que tu as fourni en jaune :

Pour les autres explications pour que je comprenne il me faut des exemples précis avec les conditions clairement exprimés les données source à utiliser et l’opération à effectuer
Tes explications sur la base de ton fonctionnement c’est de l’hébreu n’étant pas dans le métier

Donc basiquement il faut partir des données des conditions du traitement à leur appliquer (opérations ou autres) et les cellules cibles à alimenter

Cela pour moi c’est du chinois :

"Ainsi, si sur une période, le monteur passe un samedi et un dimanche non-travaillés en Belgique, il sera notifié dans la colonne Pays : « BEL » (les trois premières lettres du pays). Le montant d’indemnités de déplacement sera alors de 105.12€ avec 1 jour de récup car il sera en export.

Autre exemple : Si un monteur passe 2 jours travaillés en France, il sera notifié dans la colonne pays : « FRA ». Le montant d’indemnités de déplacement sera de 78.84 avec 3 jours de récup.

Nous pouvons donc imaginer que pour faire la différence entre Monteurs partis en export et Monteurs restés en France, nous prenions en compte cette colonne Pays. La condition serait de type : « si (pays <> FRA ) alors export »."

Ceci est plus parlant car faisant référence qu’au seul univers du fichier :

« Pour les jours travaillés et non-travaillés, le détail des montant est dans l’onglet « BASE » dans la zone A4:E70. Ainsi, ligne 8, il est précisé que pour un TAC FRA, un samedi non-travaillé correspond à 53.61€ et 0 jour de récup. Dans l’onglet « essai calcul prime », j’ai entré les résultats à obtenir pour les lignes où le motif est : « EXPORT STAYING DAY » ( lignes 14 et 19/20 ). »

Appuis toi dessus pour les éventuelles évolutions à réaliser

Merci

[Nosfell]

Bonjour,

« C’est à l’identique de tes résultats exemple ce que tu as fourni en jaune » : En effet, mes résultats en jaunes sont inexactes car je n’arrive pas à, comme pour la colonne O, compter seulement les demi-journées lorsque nécessaire.

« Tes explications sur la base de ton fonctionnement c’est de l’hébreu n’étant pas dans le métier » : D’accord je vais essayer de faire plus clair.

Pour les monteurs, le calcul des indemnités de déplacement est plus ardu car on va distinguer deux type de déplacements: « Export et France ». Ainsi, dans l’onglet BASE, dans la zone A21:E36, on trouve tous les scénarios possibles en terme de jours pour les Monteurs, premièrement en déplacement en France ( de la ligne 21 à 28 ) et deuxièmement en Export ( de la ligne 29 à 36 ).

Maintenant comment faire la distinction entre ces deux possibilités? La réponse est dans l’onglet « Essai calcul prime » où il est précisé colonne F, les trois premières lettres du pays de destination. Ainsi, si la destination du monteur, est la Belgique, il sera notifié dans cette colonne : « BEL ».

Si dans la colonne F, il est notifié autre chose que « FRA » (France), cela signifiera que la personne est partie en export.

Exemple:
Si dans l’onglet « Essai calcul prime », il est noté dans la colonne N : « Monteur » et dans la colonne F: " BEL", alors on pourra dire qu’il s’agit d’un déplacement en export et on s’intéressera, dans l’onglet « BASE », aux lignes 29 à 36.

De plus, si la période correspond à un samedi et un dimanche non-travaillés ( précisé par la colonne motif ou il sera notifié : « EXPORT STAYING DAY ») , alors le montant d’indemnités de déplacement sera alors de 105.12€ avec 1 jour de récup ( ligne 31 et 34 de l’onglet « BASE »).

Le données étaient auparavant, dans la colonne F, de l’onglet « Essai calcul prime » : « pays1, pays2… » Je les ai remplacé pour que tu y vois plus clair.

« Nous pouvons donc imaginer que pour faire la différence entre Monteurs partis en export et Monteurs restés en France, nous prenions en compte cette colonne Pays. La condition serait de type : « si (pays <> FRA ) alors export ». » : ici, je voulais donc montrer que si dans colonne F de l’onglet « Essai calcul prime », il n’est pas écrit « FRA », cela signifiera qu’on est en Export et donc que dans l’onglet « BASE », les lignes concernées, vont de 29 à 36.

Le nouveau doc avec la colonne F, modifiée: Nouvel outil de calcul prime 4 (1).xlsm (64,9 Ko)

J’espère avoir été plus clair, je suis désolé si mes explications ne sont pas limpides, je n’ai pas l’habitude des forums.

[FFO]

Bonsoir

« « C’est à l’identique de tes résultats exemple ce que tu as fourni en jaune » : En effet, mes résultats en jaunes sont inexactes car je n’arrive pas à, comme pour la colonne O, compter seulement les demi-journées lorsque nécessaire. »

Donc pour cette ligne jaune quel est le bon résultat ?

« J’espère avoir été plus clair, je suis désolé si mes explications ne sont pas limpides, je n’ai pas l’habitude des forums. »

Tes dernières indications sur le processus à appliquer me sont nettement plus accessibles restant à la stricte source de ton fichier pour appréhender les opérations à effectuer

Je regarde demain ton dernier fichier pour en comprendre toutes les ficelles
J espère simplement que l on ne s acheminera pas vers une usine à gaz car avec tes dernières opérations à realiser cela se complexifie sérieusement

Je te tiens informé et éventuellement de te proposer une nouvelle version

Bonne soirée

[Nosfell]

Bonjour,

Pour cette ligne jaune, le résultat à trouver pour la colonne P est 0.5.

D’accord.

Bonne journée!

[FFO]

Tu trouveras cette nouvelle version qui traite le cas des Code métier « Monteur » de manière spécifique

Je n’ai pas intégré la distinction des demi journées et journées entières
Tout est traité sur la base des journées entières
Concernant les forfaits au vue de la source onglet « BASE » les 2 cas ont le même montant :

A quoi bon les distinguer si au final c’est la même donnée à utiliser ?

Reste les Récupes qui elles sont différentes

Avant d’ajouter le complément adapté merci de me répondre concernant les forfaits car cela va conditionner la manière de le traiter

En attendant testes le traitement des Codes métier « Monteur »

Fais moi un retour

Nouvel outil de calcul prime 4.xlsm (72,8 Ko)