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,

 

[CISCO]

Rebonjour

Dans ton dernier fichier, les heures de début et de fin ne sont pas bonnes car les formules des colonnes AA et AB utilisent, à un moment ou un autre un calcul de la forme COLONNE(B:Y)-6 ou COLONNE(B:Y)-5. Ces -6 et -5 ne sont valables que si le tableau commençe en colonne F. Maintenant que tu as tout décalé vers la gauche, il faut faire avec COLONNE(B:Y)-2 ou COLONNE(B:Y)-1.

J'ai corrigé cela dans la pièce jointe. J'en ai profité pour simplifier la formule dans la colonne AG.

@ plus

P.S : De même, j'ai encore simplifié la même formule dans la colonne FN de ma précédente pièce jointe, Formule modifiée avec ref. mois, dans mon post #87.

 

[Aloha]

Rebonjour,

J'aurais pu comprendre cela de moi-même!
Mais là tu travailles maintenant sur les deux systèmes, le système mensuel et le système annuel.
Cela m'embêterait beaucoup de te faire faire du travail pour rien!
A+
Aloha

 

[CISCO]

Re

Bonjour,
"pourquoi est-ce que vous ne faites pas directement en inscrivant les valeurs début et fin des colonnes FJ et FK, avec une colonne indiquant l'activité du jour correspondant, X, M, F...?"
Je ne peux pas changer le système actuel;

Je me doute un peu, mais bon, cela ne me semble pas des plus simple. Comment est-ce que cela fonctionne : les salariés remplissent un document papier, ou ils ouvrent un fichier informatique ? Dans le premier cas, il faut quelqu'un pour retranscrire tout cela. Dans les deux cas, bonjour la perte de temps. Pour 10 personnes, cela n'est pas important, pour plus de 100...

A ta dernière solution il manque la double ligne, qui est une donne inévitable aussi.

Qu'appelles-tu la double ligne ?

Je ne comprends pas comment un cycle peut être complété le 1.1.
...
Aloha

Si tu parles de la valeur de FK6... C'est juste un exemple, histoire d'avoir des formules qui fonctionnent aussi dans ce cas, à savoir avec quelqu'un qui travaille du 31/12/2016 au 1/1/2017 (d'où les X dans AC6, F7 et G7). Dans les premiers fichiers de ce fil, cette première ligne ne donnait pas les bonnes valeurs, puisque nous ne connaissions pas les valeurs avant le 1/1/2017. Il ne faut pas oublier que ce fichier mensuel, tel que présenté actuellement, doit être polyvalent. Tu changes la valeur dans A1, en y mettant 2, et cela te donnera les valeurs pour le mois de février, par 3, et tu auras celles de mars, et ainsi de suite. Donc, il faut envisager le cas de quelqu'un qui travaille du 31/1/2017 au 1/2/2017, ou du 28/2/2017 au 1/3/2017, ou du 31/3/2017 au 1/4/2017...

Si tu parles des valeurs dans la colonne FM, dans les fichiers depuis le début de ce fil, nous considérons que la première période commence le 1/1/2017. Si c'est une obligation, il suffit de mettre dans FM6 la date 25/12/2016 pour obtenir 01/01/2017 dans FM7. Si cela n'est pas une obligation, on écrit la date du début de la dernière période de l'année 2016 dans FM6.

@ plus

 

[CISCO]

Bonjour

Bonjour,
Le 15.1. il ne peut pas y avoir 1, puisque la période de 7 jours commence par 1 jour libre, 4 jours travaillés suivis de 2 jours libres: donc >44h de libre.
A+
Aloha

Effectivement, il y a un bug dans la colonne FO, c'est MAX(DECALER(FN7;-6;;7)) qu'il faut écrire, et pas MAX(DECALER(FL7;-6;;7)). Et cela ne convient pas tel que pour le 31/1. De même, il faut changer le formule de la colonne FL pour qu'elle affiche une valeur dans FL37, la durée de la dernière période de repos.

@ plus

 

[CISCO]

Rebonjour

Dans le fichier du post #87, est que le temps de repos à la fin du 31/1/2017 doit être de 48 h (donc depuis le 30/01/2017 00:00) ou de 55 h (donc depuis le 29/01/2017 17:00) ?

@ plus

 

[Aloha]

Dans toutes situations le repos doit être ininterrompu pendant au moins 44 heures.
Le principe immuable est que le temps libre entre la dernière heure travaillée et la première après la pause doit être de 44 heures. Donc, un repos de 44 h. à partir de 17 heures le 29.1. suffirait pour que la condition soit remplie: le salarié pourrait recommencer à 16 heures le surlendemain pour qu'il n'y ait pas de cycle.

Je n'ai pas répondu à ta question concernant le fonctionnement du système: les salariés saisissent eux-mêmes leurs heures dans le fichier sur ordinateur. A la fin du mois le responsable est censé contrôler les saisies avant d'envoyer le fichier à l'administration. Le système est rodé depuis plus de 10 ans et il fonctionne sans problèmes.

Sincèrement, ne penses-tu pas que ta première idée, qui ne me plaisait pas tellement au début, mais pour laquelle j'ai développé de la sympathie entretemps, à savoir réunir le tout dans un fichier annuel en copiant la plage de saisie mensuelle par macro dans la plage annuelle, ne serait pas plus simple? Cela éviterait les problèmes entre les mois. Seulement, il me faudrait le code y adapté.
Le seul point que je devrais éclaircir est de savoir si on recommence tous les ans à compter depuis le début au 1.1. ou si le comptage doit être continu entre deux années.
A+
Aloha

 

[CISCO]

Bonjour

Dans toutes situations le repos doit être ininterrompu pendant au moins 44 heures.
Le principe immuable est que le temps libre entre la dernière heure travaillée et la première après la pause doit être de 44 heures. Donc, un repos de 44 h. à partir de 17 heures le 29.1. suffirait pour que la condition soit remplie: le salarié pourrait recommencer à 16 heures le surlendemain pour qu'il n'y ait pas de cycle.

Les choses ne sont pas simples. Si je comprend bien, une période de plus de 44 h est comptée comme telle, même si elle est à cheval sur deux périodes de 7 j. Dans quelle semaine faut-il la compter, la première ou la seconde ?

Je n'ai pas répondu à ta question concernant le fonctionnement du système: les salariés saisissent eux-mêmes leurs heures dans le fichier sur ordinateur. A la fin du mois le responsable est censé contrôler les saisies avant d'envoyer le fichier à l'administration. Le système est rodé depuis plus de 10 ans et il fonctionne sans problèmes.

Je m'en doute aussi, mais ce n'est pas le plus simple.

Sincèrement, ne penses-tu pas ... à savoir réunir le tout dans un fichier annuel en copiant la plage de saisie mensuelle par macro dans la plage annuelle, ne serait pas plus simple?

Oui, mais maintenant que j'ai presque fini la méthode mensuelle... Si tu as les deux , tu pourras comparer les résultats.
Le fichier annuel aurait l'avantage de garder toutes les données les unes en dessous des autres, ce qui permettrait des vérifications en cas de litige.

Seulement, il me faudrait le code adapté.
A+
Aloha

Et voui

@ plus

 

[CISCO]

Bonjour

Tu as de la lecture ce matin .

Le fichier mensuel, dans sa dernière mouture, en pièce jointe.

Tel que, une période de repos commencée en période x, est comptée en totalité dans la période x (de 7 j) même si elle déborde sur la période suivante (de 7 j). Cela parait correct comme règle, et en même temps...

@ plus

 

[Aloha]

une période de plus de 44 h est comptée comme telle, même si elle est à cheval sur deux périodes de 7 j

Je pense qu'il faut faire une séparation rigoureuse entre les 2 périodes: la partie des 44 heures située dans la première période est comptabilisée dans cette 1ère, idem pour la 2e. Il s'en suit, même s'il y a 44 heures de repos, elles n'évitent pas une période de 7 jours sans 44 heures de repos; pour l'éviter elles doivent donc dans ce cas être situées ou bien à la fin ou bien au milieu.

Cependant, comme c'est matière à interprétation je vais demander leur avis à la direction (j'y étais moi-même avant ma retraite).

ne période de repos commencée en période x, est comptée en totalité dans la période x (de 7 j) même si elle déborde sur la période suivante (de 7 j). Cela parait correct comme règle

A mon entendement ce n'est pas correct.

A+

 

[CISCO]

Bonjour

A mon entendement ce n'est pas correct.
A+

Dans ce cas, il faut modifier la formule de la colonne FM dans le fichier mensuel pour que le début de la période de 7 j soit positionnée :
* soit à 00: 00 si la personne concernée a arrêté de travailler à minuit au plus tard le jour précédent
* soit juste à la fin de son travail si elle a travaillé plus tard que minuit le jour précédent.

J'ai fait la correction dans le fichier en pièce jointe.

@ plus

PS. : J'ai fait avec

SI(A7="";"";SI(ENT(FM6)+7=A7;A7+MIN(SI(NON(ESTNUM(EQUIV(F7:AC7;FS$6:FS$11;0)));COLONNE(F:AC)-6))/24;FM6))

en matriciel
Normalement, cela devrait fonctionner plus simplement avec

SI(A7="";"";SI(ENT(FM6)+7=A7;A7+(EQUIV("";F7:AC7;0)-1)/24;FM6))

mais je ne sais pas pourquoi EQUIV("";F7:AC7;0) renvoie #NA au lieu de renvoyer la position de la première cellule vide rencontrée dans la plage F7:AC7.

 

[CISCO]

Rebonjour

Et ci-dessous le fichier annuel avec la formule de la colonne AF modifiée (J'ai, là aussi un problème avec EQUIV("";...;0), donc je suis passé par une formule matricielle).

@ plus

 

[Aloha]

Bonsoir,
Si je comprends bien il faut copier dans le fichier du message 100 (!) la formule de FM36 dans FM37; la formule qui se trouve dans FM37 sans le signe d‘égalité se trouvant là pour illustrer le problème que tu relates avec EQUIV()?

Le fait d‘insérer une ligne entre deux dates (puisque la structure de la feuille originale est comme cela) ne devrait pas poser problème pour les formules, je pense, puisqu‘elles s‘adaptent automatiquement.

Je dois avouer que je ne comprends pas comment la formule peut afficher en FO7 un cycle complété. Pour qu’il y ait un cycle complété il faudrait l‘information que la dernière période de l’année d’avant il n’y avait pas 44 heures libres. Or, je ne vois nulle part cette information, mais uniquement en FO 6 l‘information que depuis le dernier jour de congé supplémentaire en 2016 5 cycles sont complétés, mais pas ce qui manque pour compléter le 6ème.

Dans le fichier mensuel tu as travaillé sur quelle feuille?

Bonne nuit
Aloha

 

[CISCO]

Bonsoir

Bonsoir,
... la formule de FM36 dans FM37...

Oui. Tu peux aussi mettre un signe égal devant et valider en matriciel avec les trois touches Ctrl+maj+entrer.

Le fait d‘insérer une ligne entre deux dates (puisque la structure de la feuille originale est comme cela) ne devrait pas poser problème pour les formules, je pense, puisqu‘elles s‘adaptent automatiquement.

.
J'ai l'impression que oui, mais je n'en suis pas certain: il y aura peut être des -6 à remplacer par des -12, et des 7 par des 14, dans les DECALER. Je regarderai cela demain. Il faut faire cela une fois tout fini, car toute modification des formules sera difficile à propager vers le bas ensuite.

...comment la formule peut afficher en FO7 un cycle complété. Pour qu’il y ait un cycle complété il faudrait l‘information que la dernière période de l’année d’avant il n’y avait pas 44 heures libres...

Dans FN6, on a la durée de repos la plus longue de la dernière partie de période de 7 jours dans le mois précédent. La formule dans FO7 cherche le max de FN6 et FN7. Si aucun des deux n'est supérieur ou égal à 44:00, on a un 1 dans FO7.
Si la dernière période finit une ligne plus bas, FO8 ira chercher le max de FN6, FN7 et FN8.
Pour que cela fonctionne correctement, j'ai dû insérer des lignes vides au début du tableau, car la formule dans FO7, de la forme DECALER(FN7;-6;;7), utilise la plage FN1:FN7. Si on élimine une ou des lignes vides du haut, cette formule ne trouvera pas cette plage verticale comportant 7 cellules, et cela plantera. Si ces lignes vides en plus te posent problème, je peux modifier la formule dans la colonne FO pour contourner ce problème. Mais la formule sera encore plus compliquée à comprendre.

Dans le fichier mensuel tu as travaillé sur quelle feuille?

Bonne nuit
Aloha

Je ne comprends pas la question !

@plus

 

[Aloha]

Bonjour,
Ma question concernant le fichier Résultats divergeants: il y a 2 feuilles identiques (Salarié 1 et Salarié 1(2) )sauf que la première n‘a pas de colonnes vides entre la colonne contenant les dates et la plage de saisie.
C‘était l‘histoire de DECALER() qui faisait que les heures de début et de fin n‘étaient plus correctes lorsque j‘effaçais les colonnes vides. Je constate des différences entre les formules, d‘où ma question sur quelle feuille tu as travaillé avant de reposter le fichier.

Malheureusement, comme ce n‘est pas le cas dans le fichier dans lequel je devrai intégrer les formules quand tout sera prêt, on ne peut pas ajouter une ligne vide au début. Cela me créerait trop de problèmes dans mon système qui tourne bien.
A+
Aloha

 

[CISCO]

Bonjour

Bonjour,
Ma question concernant le fichier Résultats divergents: il y a 2 feuilles identiques ...

J'ai fait les changements sur la feuille Salarié 1 (2)

Malheureusement, comme ce n‘est pas le cas dans le fichier dans lequel je devrai intégrer les formules quand tout sera prêt, on ne peut pas ajouter une ligne vide au début. Cela me créerait trop de problèmes dans mon système qui tourne bien.
A+
Aloha

Je vais essayer de faire sans les premières lignes vides.

@ plus