Microsoft 365 Compter les WE libres

Aloha

XLDnaute Accro
Bonsoir,
Dans le document ci-attaché se trouvent les données (heures) en rapport avec le contrat de travail de DUPONT Marie et DURAND Germaine.
Dans la ligne en haut les 24 heures de la journée + nuit
Colonne A: Date
Colonne B: Jour semaine
Colonne C: Nom et prénom de l'agent
Colonne D: le service dans lequel l'agent travaille
Colonnes E à AB: les heures en rapport avec le contrat de travail

Signification des symboles rencontrés:
X = 1 heure de travail
C = 1 heure de congé
RS = 1 heure réunion de service comptée comme heure de travail
T = Tutorat
HS = heure supplémentaire
...et il y en a d'autres

Double problème:

* le premier:

La règle: le repos ininterrompu hebdomadaire de 44 heures doit coïncider avec le week-end au moins 20 fois par an. Le week-end s’étend sur une période de deux jours consécutifs situés entre 6 heures le samedi matin et 6 heures le mardi matin.

Les périodes samedi 6h à mardi 6h sont en jaune.

Il s'agit donc de vérifier quand la personne avait un WE de libre suivant les règles ci-dessus
et de les mettre dans un compteur pour faire le bilan à la fin de l'année.

* le second:

Chaque salarié a droit à un repos ininterrompu de 44 heures par semaine. Dès la fin d’un repos hebdomadaire, le prochain repos hebdomadaire doit intervenir endéans les prochains sept jours. Les salariés qui n’ont pas pu jouir dudit repos auront droit à un jour de congé supplémentaire pour chaque période entière de 8 semaines, successives ou non, pendant laquelle le repos ininterrompu de 44 heures par semaine n’est pas accordé.

Il faut donc compter les périodes de 7 jours où la condition est remplie (donc pas 44 heures libres consécutives)

Je ne sais pas si ces poblèmes sont résolubles par seules formules ou s'il faut du VBA. La base de données retraçant chaque heure de chaque jour de l'année pour quelque 130 salariés (qui n'existe pas encore; les données sont disponibles dans des fichiers mensuels par service regroupant tous les salariés travaillant dans ce service; il faudra copier les données par VBA) sera assez importante: 130 * 365 = beaucoup de lignes

Quelqu'un parmi vous entrevoit-il/elle comment résoudre ce double problème?
Merci d'avance pour tout tuyau et toute aide!

Bien à vous
Aloha
 

Pièces jointes

  • Saisies.xlsx
    19.2 KB · Affichages: 185
Dernière édition:

CISCO

XLDnaute Barbatruc
Bonjour

Est-ce que dans ton fichier réel, les données concernant une personne X sont regroupées ensemble, ou est-ce que tout est mélangé, quelques lignes concernant X, puis d'autres au sujet de Y, puis à nouveau au sujet de X, puis Z, puis... ?

@ plus
 

CISCO

XLDnaute Barbatruc
Bonjour

Le début d'une solution, pour résoudre le 1er problème, en pièce jointe.
Dans AD4: B4, on retranscrit toutes les données de la plage E4: AB6 et E7: J7 (4 cellules blanches et toutes les cellules jaunes) (sur une seule ligne donc, au lieu de les avoir sur 4 lignes, en mettant des 0 à la place des cellules vides).
Dans AJ5 :
DECALER(AJ4;;;;44) lit les 44 cellules sur la ligne 4 commençant dans AJ4
DECALER(AJ4;;;;44)=0)+(DECALER(AJ4;;;;44)="C") renvoie une série de 1 et de 0. 1 si la cellule contrôlée contient 0 ou "C", 0 dans le cas contraire.
EQUIV(0;(DECALER(AJ4;;;;44)=0)+(DECALER(AJ4;;;;44)="C");0) recherche la position de la première cellule ne contenant, ni un 0, ni un "C" dans les 44 cellules concernées. Si elle ne trouve pas de cellule contenant autre chose qu'un 0 ou un "C", elle renvoie #NA.
ESTNA(EQUIV(0;(DECALER(AJ4;;;;44)=0)+(DECALER(AJ4;;;;44)="C");0)) renvoie VRAI si la plage DECALER(AJ4;;;;44) ne contient que des 0 ou des "C" (La formule n'a pas trouvé autre chose, donc elle a renvoyé #NA)
On tire la formule vers la droite jusque BK5 (pas jusque DB5) pour contrôler toutes les plages de 44 cellules successives concernées.
Dans AC5, on recherche VRAI sur la plage à droite sur la ligne 5. Autrement dit, on recherche s'il existe une plage de 44 cellules successives sur la ligne 4 ne contenant que des 0 ou des "C". S'il y a un VRAI, AC5 renvoie 1, autrement dit, ce "WE" contient au moins 44 h successives convenant.

Il faut copier la plage AC4: B5 et la coller sur chaque ligne concernant un samedi, donc dans AC11, AC18 et ainsi de suite...

Dans AC3, on fait la somme des valeurs de la colonne AC (des WE convenant) concernant DUPONT Marie.

Cette méthode peut être un peu simplifiée (les colonnes AD:AI ne servent pas à grand chose) en changeant la formule sur les lignes 4, 11, 18... Elle n'est pas très pratique si tu dois faire cela sur un grand fichier (Puisqu'on recopie toutes les données des "W.E" pour les mettre sur une seule ligne) pour beaucoup de personnes (135 il me semble).

Dis moi ce qui te va, te gêne... En VBA, cela serait mieux, mais bon, dans l'immédiat, cela me demanderait trop de gamberge pour m'y remettre... Si une bonne âme pouvait passer par là...

@ plus
 

Pièces jointes

  • Aloha Copie de Saisiesbis.xlsx
    62 KB · Affichages: 6

Aloha

XLDnaute Accro
Bonjour,

Merci beaucoup pour tout ce travail!

Malheureusement je ne suis pas à même de comprendre ce type de formules.
Une précision: une heure de WE libre est totalement vide; si elle n'est pas vide elle n'est pas libre. Et les C (congé) n'existent pas le WE; le congé se prend uniquement en semaine.

Si je change une siaise il n'y a pas de réaction dans les formules.

En ce qui concerne une solution par VBA, j'en ai un début (qui ne provient pas de ma plume) mais malheureusement l'auteur ne réagit pas/plus pour y apporter les corrections nécessaires.

Dans une solution par seules formules il faut des formules passe-partout dans toutes les lignes, puisqu'il faut identifier les jours de WE dans la formule.

A+
Aloha
 

CISCO

XLDnaute Barbatruc
Rebonjour

Dans le fichier en pièce jointe, il ni a plus que 3 formules (1 couleur par formule).
La formule orange, matricielle, donc à valider avec Ctrl+Alt+Entrer, regroupe les deux précédentes (celle de la ligne 4 et celle de la ligne 5 du précédent fichier), sous la forme SI(... = "Samedi";formule1; SI(ET(...= "Dimanche");....);formule2;"")). On doit la tirer jusqu'en bas de la partie concernant DUPONT Marie.

@ plus

P.S : Les formules oranges ne prennent plus en compte les "C"
 
Dernière édition:

CISCO

XLDnaute Barbatruc
Bonsoir

Quelques petites modifications pour :
* sur la ligne 2, à partir de AD2, lister tous les noms présents dans la colonne C, sans doublon
* sur la ligne 3, à partir de AD3, lister le nombre de "WE" convenant pour chacune de ces personnes (formule matricielle).

Il faut tirer davantage ces formules vers la droite en fonction du nombre de personnes à prendre en compte. Il faut aussi tirer vers le bas les formules de la plage AC155: B155, aussi longtemps que nécessaire.

Si une autre mise en page te parait plus intéressante, dis le moi, explique moi ce qui te semblerait mieux.

@ plus
 

CISCO

XLDnaute Barbatruc
Bonsoir

Pour ce qui est du second problème, il me faut quelques précisions :
1) On est au bout de 44 h de repos hebdomadaire à, par ex 12 h. Si il y a encore à la suite des heures de repos, par ex de 12 à 13 et de 13 à 14 h, est-ce qu'on doit commencer la recherche des 44 h de repos suivantes consécutives dans les 7 jours suivants à partir de ce 12 h ou à partir de 14 h ?
2) Les 44 h de repos suivantes consécutives doivent elles être incluses dans les 7 jours suivants, ou doivent-elles être incluses ou seulement commencer dans les 7 j suivants ?
3) Quand on parlent des 7 jours suivants, pour être plus exact, est-ce en réalité les 7 x 24 h suivantes, ou le jour commencé et les 7 jours entiers suivants ?

@ plus
 

CISCO

XLDnaute Barbatruc
Bonjour

En pièce jointe, une possibilité pour résoudre le second problème.
Il y a 5 sortes de formules :
* Sur la ligne 2, à partir de AD2, et en allant vers la droite, une formule listant les personnes de la colonne C, sans doublon, une cellule sur 4 seulement (Histoire de laisser des colonnes disponibles)
* Dans la colonne AD, à partir de AD4, et en allant vers le bas, la liste des heures travaillées ou pas par la personne nommée dans AD2 (Tout ce qu'il y a dans le tableau de gauche concernant cette personne, mais sur une seule colonne, et pas sur plusieurs colonnes et plusieurs lignes). Même chose dans la colonne AH, pour la personne nommée dans AH2...
* Dans la colonne AE, à partir de AE4, et en allant vers le bas, une formule donnant 44 si la personne concernée n'a pas travaillée pendant les 43 h précédentes et l'heure en cours. Idem pour AI
* Dans la colonne AF, à partir de AF4, et en allant vers le bas, une formule donnant 1 si la période de 44 h consécutives de repos, qui vient juste de se finir, n'est pas suivie d'une autre période de 44 h consécutives de repos dans les 168 h suivantes. Idem pour AJ
* Dans la colonne AG, à partir de AG3... rien (Colonne en réserve, au cas ou il y aurait d'autres calculs à faire)
* Dans AD3, une formule faisant la somme du contenu de la colonne AF. Même formule dans AH3 par rapport à la colonne AJ.
* Dans AE3, une formule donnat le nombre de jour de congé à attribuer en plus à la personne concernée. C'est le total qui t'intéresse.

Comme tu as plus de personnes, il faut :
* Tirer la formule de la cellule AD2 aussi longtemps que désiré vers la droite uniquement.
* Copier la formule de AD3 et la coller vers la droite dans AH3, AL 3, AP3 et ainsi de suite...
* Copier la formule dans AE3 et la coller vers la droite dans AI3, AM3, AQ3 et ainsi de suite...
* Copier la plage AD4:AG4 et la coller vers la droite, de proche en proche, dans AH4, dans AL4, dans AP4 et ainsi de suite...
* Tirer ces denières formules vers le bas aussi longtemps que désiré pour prendre en compte toutes les heures possibles. Je n'ai tiré les formules des colonnes AD à AJ que jusque la ligne 3000 sur le fichier en pièce jointe.

Il te faut bien sûr vérifier tout cela.

@ plus

P.S : Cette proposition ne fonctionne que si toutes les lignes dans le tableau de gauche, concernant une personne X, sont regroupées ensemble (comme c'est le cas dans ton fichier exemple). Il ne faut pas de mélange, pas quelques lignes au sujet de la personne X, puis d'autres concernant Y, puis à nouveau X....
 

Pièces jointes

  • Aloha Copie de Saisies cinq.xlsx
    305.8 KB · Affichages: 5
Dernière édition:

Aloha

XLDnaute Accro
Bonsoir

Quelques petites modifications pour :
* sur la ligne 2, à partir de AD2, lister tous les noms présents dans la colonne C, sans doublon
* sur la ligne 3, à partir de AD3, lister le nombre de "WE" convenant pour chacune de ces personnes (formule matricielle).

Il faut tirer davantage ces formules vers la droite en fonction du nombre de personnes à prendre en compte. Il faut aussi tirer vers le bas les formules de la plage AC155: B155, aussi longtemps que nécessaire.

Si une autre mise en page te parait plus intéressante, dis le moi, explique moi ce qui te semblerait mieux.

@ plus
Bonjour,

Je suis tout confus que tu te fasses autant de travail!

J'ai toujours du mal à comprendre le fonctionnement, puisque je ne comprends pas les formules.

Si j'analyse les résultats, le produit des formules que je ne suis pas capable de comprendre:

* la cellule A19: il y a 1 alors que la personne est libre moins de 44h le WE (donc entre 6:00 le samedi et 6:00 le mardi): elle est bien libre du dimanche 13:00 au mardi 13:00, mais il ne faut compter que jusqu'à 6:00 du matin le mardi!

* la cellule AC54: 0,
alors qu'elle est libre du samedi 6:00 au lundi 10:00, donc >44h

J'ai redressé des situations qui n'existent pas: des C le WE.

Je me rends compte qu'il y aura énormément de formules pour 130 personnes! Cela deviendra énormément lent, surtout qu'il y a déjà une pléthore de formules dans ce fichier, voilà pourquoi j'avais imaginé remplacer les formules par leur valeur, par VBA, ce qui impliquerait qu'il faudrait placer les formules aussi par VBA, au moment où les données sont importées.
A+
Aloha
 

Pièces jointes

  • Saisies 11.3.21.xlsx
    845.5 KB · Affichages: 1

Aloha

XLDnaute Accro
Cette proposition ne fonctionne que si toutes les lignes dans le tableau de gauche, concernant une personne X, sont regroupées ensemble (comme c'est le cas dans ton fichier exemple). Il ne faut pas de mélange, pas quelques lignes au sujet de la personne X, puis d'autres concernant Y, puis à nouveau X....
Mais ce sera le cas étant donné que les données des mois sont collées les unes en-dessous des autres. Une pour chaque personne qui travaille toute l'année son nom apparaîtra dans 12 blocs mensuels! Ce qui ne sera pas le cas, c'est qu'il y ait quelques lignes d'une personne, puis quelques lignes d'une autre, puis à nouveau la première. Ce sera à chaque fois un mois entier.
 
Dernière édition:

Aloha

XLDnaute Accro
Pour ce qui est du second problème, il me faut quelques précisions :
1) On est au bout de 44 h de repos hebdomadaire à, par ex 12 h. Si il y a encore à la suite des heures de repos, par ex de 12 à 13 et de 13 à 14 h, est-ce qu'on doit commencer la recherche des 44 h de repos suivantes consécutives dans les 7 jours suivants à partir de ce 12 h ou à partir de 14 h ?
2) Les 44 h de repos suivantes consécutives doivent elles être incluses dans les 7 jours suivants, ou doivent-elles être incluses ou seulement commencer dans les 7 j suivants ?
3) Quand on parlent des 7 jours suivants, pour être plus exact, est-ce en réalité les 7 x 24 h suivantes, ou le jour commencé et les 7 jours entiers suivants ?

Voici le texte de la convention, qui, comme beaucoup de textes, manque de précision et exige l'interprétation:
"...chaque salarié a droit à un repos ininterrompu de 44 heures par semaine. Dès la fin d’un repos hebdomadaire, le prochain repos hebdomadaire doit intervenir endéans les prochains sept jours. Les salariés qui n’ont pas pu jouir dudit repos auront droit à un jour de congé supplémentaire pourchaque période entière de 8 semaines, successives ou non, pendant laquelle le repos ininterrompude 44 heures par semaine n’est pas accordé."

*ta 1ère question: je pense qu'il faut commencer à compter à partir de la première cellule non vide, donc à 14:00

*ta 2ème question: incluses dans les prochains 7 jours puisque normalement il y a 5 jours de travail dans la semaine et que les 2 jours restants représentent le repos hebdomadaire

*ta 3ème question: les 7*24 heures situées après la dernière cellule vide du repos hebdomadaire.
Si la personne commence à travailler le mardi à 13:00 après un repos hebdomadaire le prochain doit être situé dans les 7*24 heures à compter à partir de 13:00 le mardi
 

CISCO

XLDnaute Barbatruc
Rebonsoir

J'ai changé le fichier "Aloha Copie... quatre" dans le post 7, un $ d'oublié dans la première formule listant les données (C, RS, X...) placée en ligne sur la ligne 4 : Il fallait écrire DECALER($A$3... dans AD4 au lieu de DECALER($A3....
Dans AD11, cela donnait DECALER($A10... au lieu de DECALER($A$3... et donc on ne lisait pas les données du tableau de gauche sur la ligne 11 mais plus bas (7 lignes trop bas). Et cette erreur se retrouvait dans toutes les cellules.... :( avec un décalage de plus en plus important...


@ plus
 
Dernière édition:

Aloha

XLDnaute Accro
Bonsoir,

J'ai pris ton fichier du post 7 et je l'ai nettoyé des situations irréelles (C le WE) et je l'ai renommé. Je te suggère de continuer avec ce fichier.
Dans la cellule AC5 il devrait y avoir 1 mais il y a 0.
Si j'efface tout du 8 au 10.4. le 0 devrait changer en 1, mais il reste.
A+
Aloha
 

Pièces jointes

  • Saisies 11.3.21 bis.xlsx
    248.8 KB · Affichages: 5