XL 2010 Détecter les périodes de <44 h. libres pendant 7 jours

[Aloha]

Bonjour,
Le titre est un peu compliqué; je l'explique.
Le contexte: des salariés ont droit à 1 jour de congé supplémentaire lorsque pendant 7 semaines consécutives ou non ils ne sont pas libres pendant 44 h. de suite par semaine.

Ils remplissent chacun une fiche mensuelle où ils saisissent toutes les heures en relation avec le contrat de travail, heure par heure, en inscrivant dans les cellules le type d'heure : travail ("X"), congé ("C"), maladie ("M"), heures supplémentaires (6 sortes, de "S1" à "S6"), réunions ("R"), etc. Ces fiches sont regroupées dans un classeur dont le nom se compose du nom du service, du mois et de l'année ([Mois] [Année] [SERVICEX].XLSX)

Admettons que je sois arrivé à regrouper ces données mois par mois et salarié par salarié (pour l'instant je le simule dans le fichier ci-joint; je dois m'occuper de ce problème par après) dans une base de données, en y copiant (de préf. par VBA, sinon par formule), les données requises de toutes les fiches de tous les mois et de tous les services.

Ce qui donne, en gros, un fichier comme celui qui est ci-joint.

J'ai donc compté jour par jour dans les fichiers remplis par les salariés les cases horaires (00:00 à 24:00, puisque le service fonctionne nuit et jour tous les jours de l'année) où il y a une inscription (colonne D) et j'ai retranché ce chiffre de 24, ce qui me donne donc les heures libres par période de 24 heures (colonne E). La colonne F tient compte de la date d'engagement et ne reprend les valeurs de la colonne E que pour la période du contrat de travail, sinon "HC" (hors contrat) est inscrit dans la case correspondante.

Il s'agit donc à présent de détecter, 7 jours par 7 jours, les périodes où dans la colonne F l'addition de 2 cellules superposées ne donne pas au moins 44, en commençant, pour déterminer les périodes de 7 jours, par le 1er janvier, resp. par la date d'engagement si le salarié a été engagé pendant l'années en cours,

 

[Aloha]

Bonsoir,
J'ai quelques difficultés à comprendre les cellules vertes:
Fin date et heure: la fin de la dernière heure où le salarié à travaillé l'année précédente?
Début: le début du dernier cycle?
Durée max: =?
Abstraction faite du transit entre les deux années les formules semblent fonctionner correctement.
Bonne soirée
Aloha

 

[CISCO]

Bonjour

Les dénominations choisies sont effectivement un peu courtes. Voila donc quelques compléments :

Fin date et heure: la fin de la dernière heure où le salarié à travaillé l'année précédente ?

Oui, effectivement, c'est la fin de la dernière heure de travail dans la dernière période de travail de l'année précédente. Je ne sais ce que cela donne si on met une heure située plus loin dans le passé, par exemple si la personne n'a pas travaillé pendant les deux dernières semaines de décembre. A tester.

Début: le début du dernier cycle?

Oui. Cette valeur conditionne le nombre de jours de la dernière "période de 7 jours" à mettre sur 2016, et ceux à mettre sur 2017.

Durée max: =?Aloha

La dernière "période de 7 jours" de l'année 2016 n'a pas forcément 7 jours en 2016. Par ex, elle n'en a que 3. Pour pouvoir continuer correctement les calculs dans la colonne AH, il faut indiquer la durée max de repos comprise dans ces 3 jours.

@ plus

 

[Aloha]

Bonjour,
Merci pour les explications.
Je viens de réinstaller Windows 10 et e.a. MS Office et malheureusement je n'ai pas fait attention et j'ai installé la version anglaise.
Théoriquement cela ne devrait pas poser problème mais j'ai commencé à remplir le tableau en haut à gauche de la feuille qui doit afficher les données du salarié et la fonction SUMPRODUCT() ne veut pas fonctionner: #VALEUR. Lorsque j'utilise son équivalent allemand (SUMMENPRODUKT(); SOMMEPROD() dans la version française) dans la version Mac allemande (là aussi je n'avais pas fait attention et j'ai installé la version allemande au lieu de la française), j'ai le bon résultat. Allons savoir pourquoi!
Bonne journée
Aloha

 

[Aloha]

Bonjour,
J'ai résolu ce problème, même si la solution avec INDIRECT() n'est pas parfaite puisque le fichier auquel la fonction se réfère doit être ouvert pour que la fonction fonctionne, et j'ai avancé dans la confection de la fiche qui doit héberger le tout.
Maintenant je trébuche sur la transformation des jours supplém. en heures. Au cas où un salarié travaille toute l'année 40 heures par semaine il a droit à 8 heures de congé, s'il travaille 20 heures, ce sont 4 heures, etc.
Seulement s'il change de tâche hebdomadaire cela change: le jour de congé doit être réparti proportionnellement par rapport à la tâche hebdomadaire.
Là où je ne vois tout à fait clair c'est dans le cas où, comme dans le cas concret ci-joint, il a changé deux fois de tâche hebdomadaire et qu'un jour supplémentaire tombe pendant la 2ème période. Je ne vois pas trop quand il faut -question d'être équitable envers le salarié- commencer le calcul: à la date où le jour supplém. tombe (comme je l'ai fait dans le cas en espèce) ou à une autre date. Mais à laquelle? Le problème est que ce n'est pas réglé par un texte. Il faut interpréter (qu'en penses-tu?).
Il faudra que je pose cette question aux responsables.

Autre petit problème: comment faire pour que, de J6 à J11, le jour soit inscrit dans la période où il tombe. Le principe est d'additionner les jours supplém. dans la colonne AI de la date début jusqu'à la date fin de chaque période, mais je ne sais pas comment faire.

A+
Aloha

 

[CISCO]

Bonjour

Où sont indiqués le rapport durée de travail/temps plein, en % ou autre, de la personne, dans H6:H11 ?

@ plus

 

[Aloha]

Bonsoir,
Nulle part; la formule doit le calculer. Tous les éléments sont là: la tâche hebdomadaire et les dates début et fin de chaque période avec une tâche déterminée.

Je dois avouer ne pas être sûr d'avoir bien compris ta question!

Bonne soirée.
Aloha

 

[CISCO]

Bonjour

OK, je pensais que les périodes de temps partiel et les taux correspondants étaient marqués noir sur blanc quelque part.
Il est vrai que l'on peut retrouver ces informations en sommant les durées de travail au fur et à mesure. J'essaye de te faire cela dans la journée.

@ plus

 

[Aloha]

Bonjour,
Ne t'arrache pas une jambe là-dessus; cela ne doit pas absolument être fait aujourd'hui.
Mon but est que le tout soit prêt lorsque les fiches mensuelles remplies arrivent à l'administration, c.-à-d. fin janvier.

De toute façon mon code ne fonctionne pas encore comme il faut: il ouvre deux fichiers mensuels au lieu du fichier mensuel et du fichier annuel. Comme le code est extrêmement compliqué je n'arrive pas à le comprendre pour le corriger.

Et puis, je suis toujours embarrassé par le problème des références externes "flexibles", étant donné que INDIRECT() ne devient actif que si la cible est ouverte.

Bonne journée
Aloha

 

[CISCO]

Bonsoir

Est-ce que tu peux m'expliquer ton *8/40*30 sur la ligne 7 ?

@ plus

 

[Aloha]

Bonsoir,
ou plutôt bonjour,
C‘est très simple: sur base d‘une tâche hebdomadaire de 40 heures un jour vaut 8 heures.Pour tenir compte des tâches réduites je multiplie donc par 8, puis je divise par la tâche de 40 heures et je multiplie par la tâche réelle, 30 heures en l‘espèce.
Le salarié a donc droit à 30 40ièmes de 8 heures.
Cela est tout à fait clair. Ce qui n‘est pas clair du tout, c‘est, comme je l‘ai écrit dans un post précédent, c‘est comment calculer les heures en cas de changement de tâche après l‘échéance d‘un jour supplémentaire, pour ne pas être inéquitable envers l‘employeur, mais surtout envers le salarié.
Je n‘ai plus réfléchi là-dessus; je me suis plutôt tracassé la tête pour trouver une solution empêchant que les données soient furtiles, comme c‘est le cas avec INDIRECT. Ca c’est un grand problème! Il existait une fonction INDIRECT.EXT mais je nesais pas si elle est encore utilisable avec une version récente d‘Excel.
Bonne nuit
Aloha

 

[CISCO]

Bonjour

Bonsoir,
ou plutôt bonjour,
C‘est très simple: sur base d‘une tâche hebdomadaire de 40 heures un jour vaut 8 heures.Pour tenir compte des tâches réduites je multiplie donc par 8, puis je divise par la tâche de 40 heures et je multiplie par la tâche réelle, 30 heures en l‘espèce.

J'étais parti sur une durée hebdomadaire de 35 h, donc je doutais un peu de ma méthode...

@ plus

 

[Aloha]

Bonjour,
J‘ai pu éclaircir quelques questions ce matin.
J‘ai eu la confirmation de l‘exactitude de ma méthode de transformation des jours supplémentaires en heures. D‘ailleurs, si je remplace les tâches hebdom. <40 heures par 40 alors la somme des répartitions donne 8 heures, et c‘est donc exact.

Deuxièmement, les calculs s‘arrêtent le 31.12. et reprennent le 1.1. de l‘année suivante, ce qui rend les calculs un peu plus aisés.
Cependant, j’estime que, pour parer à toutes les éventualités à venir, il vaut mieux garder la possibilité de recourir à des données de l‘année précédente.

Par contre, je dois faire, dans les fiches annuelles, des calculs supplémentaires, concernant les permanences téléphoniques: elles sont comptabilisées par unités continues de 24 heures au maximum. Un nombre d‘heures de permanence pouvant aller de 1 à 24 heures constitue une unité. Si p.ex. -cas extrême- un salarié a 25 heures continues 2 unités seront comptabilisées.
Bien évidemment les mois se chevaucheront: on peut commencer la permanence le 31 et la terminer le 1er ou même le 2ème jour du mois suivant.
Si une journée de permanence est interrompue par 1 ou plusieurs heures de travail le comptage s‘arrête avant le travail et un nouveau comptage commence après.
Chaque unité vaut 0,5 heure à récupérer.

En tout cas, les calculs des 44 heures peuvent être finalisés maintenant.
A+
Aloha

 

[CISCO]

Bonsoir

Est-ce que les cellules cochées dans ton dernier fichier, colonnes B à Y, correspondent à une situation réelle, plausible, ou est-ce que tu les as remplies au hasard ?

@ plus

 

[Aloha]

Bonsoir,
il s'agit de données fictives.
Dans le fichier ci-joint j'ai copié des saisies réelles.

J'ai aussi mis des P comme Permanence. Voici les explications:
6.7.: 12 P + 7.7.: 7 P = 19 P = 1 unité * 0,5 = 0,5 heure à récupérer
8.7: 18 P + 9.7.: 7 P = 25 P = 24 P (= 1 unité) + 1 P (= 1 unité) = 2 unités * 0,5 = 1 heure à récupérer
9.7.: 2ème tournée de permanence: 8 P + 10.7.: 8 P = 16 P = 1 unité * 0,5 = 0,5 heure à récupérer
10.7.: 2ème tournée de permanence après quelques heures de travail: 8 P = 1 unité * 0,5 = 0,5 heure à récupérer.
Bonne soirée
Aloha

 

[CISCO]

Bonjour

Est-ce que tu pourrais me dire ce que devrait donner l'exemple en pièce jointe (sans tenir compte des P que je traiterai ensuite) ?

@ plus