Microsoft 365 Compter les WE libres

[Aloha]

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

 

[Aloha]

Re,
Je me suis amusé à trouver une solution pour effacer les tableaux, avec quoi on gagne considérablement en taille: 2.900 Kb

Range("AE4:AE" & (3 + nbre_personnes)).FormulaR1C1 = "= SUMPRODUCT((Nometprénomtrié = RC[-2])*(TableauWE=44))"
'modifié le 9.4.2021:
'Range("AF4:AF" & (3 + nbre_personnes)).FormulaR1C1 = "= SUMPRODUCT((Nometprénomtrié = RC[-3])*(Tableaureposncp=7*24))"
Range("AF4:AF" & (3 + nbre_personnes)).FormulaR1C1 = "= SUMPRODUCT((Nometprénomtrié = RC[-3])*(Tableaurepos < 44)*(Tableaureposncp=7*24))"
'ajouté le 12.5.2021
Range("AE4:AF" & (3 + nbre_personnes)).Copy
Range("AE4").PasteSpecial xlPasteValues
'fin ajoute

'EFFACER LES TABLEAUX à LA FIN
Workbooks(Fdestina).Worksheets(feuildestina).Range("AH4:DC" & DLcolA).Select
Selection.ClearContents


Application.Calculation = xlCalculationAutomatic 'Recalcul des formules automatiques
Application.CutCopyMode = False 'Presse-papier vidé
Application.DisplayAlerts = True 'Affichage automatique des messages d'alerte actif
Application.ScreenUpdating = True 'Affichage à l'écran actif

'MsgBox Timer - t

End Sub

Les cas litigieux: normalement, dès qu'on sera sûr que les calculs sont corrects il ne devrait pas y avoir des cas litigieux. Sinon, il faudrait une macro rétablissant les tableaux au besoin, là où il faut.

Ou bien, mieux: comme un cas litigieux concerne une personne déterminée, il faudrait pouvoir "isoler" cette personne dans une feuille dédiée à cet effet.

D'ailleurs, avec ce que j'ai ajouté dans le code, les tableaux n'affichent les valeurs à aucun moment, mais, je l'ai vérifié, les résultats dans les colonnes AE et AF sont les mêmes qu'avec les tableaux, de sorte qu'ils ont donc le temps d'accomplir leur mission avant d'être effacés.

La Base avec l'effaçage des tableaux:

A+
Aloha

 

[CISCO]

Bonsoir

Je ne me souviens plus : Est-ce que dans le fichier Base une fois rempli, on a tout ce qui concerne Janvier, puis Février, et ainsi de suite, ou est-ce qu'on a tout ce qui concerne une personne, pour tous les mois importés, puis pour une autre personne, pour tous les mois importés, et ainsi de suite... ?

@ plus

 

[CISCO]

Re

Re,

Ou bien, mieux: comme un cas litigieux concerne une personne déterminée, il faudrait pouvoir "isoler" cette personne dans une feuille dédiée à cet effet.

A+
Aloha

Pour cela, il suffit de mettre dans TobeCopied un fichier ne contenant que les données de la personne concernée et d'utiliser une version de la macro n'effaçant pas les tableaux de droite.

@ plus

 

[CISCO]

Re,
....
D'ailleurs, avec ce que j'ai ajouté dans le code, les tableaux n'affichent les valeurs à aucun moment, mais, je l'ai vérifié, les résultats dans les colonnes AE et AF sont les mêmes qu'avec les tableaux, de sorte qu'ils ont donc le temps d'accomplir leur mission avant d'être effacés.

A+
Aloha

Effectivement, c'est logique, c'est ce qui est prévu, mais cela n'est pas la méthode informatique la plus rigoureuse (Écrire des tableaux sur la feuille, faire des calculs avec ses valeurs, puis effacer ces tableaux). Normalement, il faudrait tout faire avec des tableaux en mémoire uniquement, et pas sur la feuille.

@ plus

 

[CISCO]

Re

Bonsoir
...
3) J'ai une version (cf. post 201) sans les tableaux de droite, et sans les formules dans les colonne AE et AF (Les calculs correspondant à ces colonnes se font au fur et à mesure, sans des SOMMEPROD). Il faut que je la retrouve et que je la vérifie...

@ plus

Cette macro ne donne pas exactement les mêmes valeurs dans la colonne AF. J'ai repéré d'où vient le problème, mais je ne vois pas le test à mettre pour résoudre ce problème.

N'importe comment la solution dans ton dernier post est déjà assez efficace.

Il faut encore résoudre les problèmes :
* début/fin d'année
* des personnes ne travaillant pas toute l'année

@ plus

 

[Aloha]

Bonsoir

Je ne me souviens plus : Est-ce que dans le fichier Base une fois rempli, on a tout ce qui concerne Janvier, puis Février, et ainsi de suite, ou est-ce qu'on a tout ce qui concerne une personne, pour tous les mois importés, puis pour une autre personne, pour tous les mois importés, et ainsi de suite... ?

@ plus

La liste est triée sur les personnes, puis les mois

 

[Aloha]

Re


Pour cela, il suffit de mettre dans TobeCopied un fichier ne contenant que les données de la personne concernée et d'utiliser une version de la macro n'effaçant pas les tableaux de droite.

@ plus

Faisable mais un peu compliqué: il faut d'abord isoler la fiche de la personne, mais faisable, peut-être de préférence dans un fichier à créer à cet effet.

 

[Aloha]

Il faut encore résoudre les problèmes :
* début/fin d'année
* des personnes ne travaillant pas toute l'année

Re,
en effet.
Pour le deuxième problème il faut recourir aux fiches par service comprenant toutes les personnes et leurs données.
Si je saisis bien les choses cela n'a rien à voir avec "nos" calculs mais doit venir après: les calculs ne font que constater la situation créée par les saisies. Il (je) faudra ajouter une colonne supplémentaire dans la feuille 44 heures qui se réfère aux fiches des services pour calculer les pro rata.

Pour le premier problème, je ne vois pas vraiment bien clair. Il me semble que pour l'instant on peut l'ignorer et nous limiter à l'exercice en cours.

A+
Aloha

 

[Aloha]

Bonjour,
J'ai trouvé les formules pour calculer le pro rata (voir feuille 44 HEURES).
Ce qui manque c'est un bout de code qui met les formules dans le tableau entier dans 44 HEURES et les remplace par leurs valeurs pour ménager le plus possible les ressources.
A+
Aloha

 

[CISCO]

Bonsoir

Si tu peux utiliser SIERREUR, la relation suivante serait plus courte :

ARRONDI((SIERREUR(RECHERCHEH($A6;INDIRECT("'"&$B6&"'!$B$1:$Z$4");4;0);$C$1)-MAX(RECHERCHEH($A6;INDIRECT("'"&$B6&"'!$B$1:$Z$4");3;0);$B$1))/365*20;0)

@ plus

 

[CISCO]

Re

Et dans la série simplification, pourquoi ne pas faire, dans les fichiers mensuels janvier 2020, Février 2020..., dans E5 et en dessous, avec
DATE(DROITE(D$1;4);BA$3;D5)
au format personnalisé jjjj
Cela donne presque la même chose que la formule déjà en place, et c'est beaucoup plus court.

@ plus

 

[CISCO]

Bonsoir

Il peut y avoir un problème avec le début et la fin car :
* Pour ce qui est du début de l'année, j'ai considéré que la dernière période de repos d'au moins 44 h finissait le 31/12 de l'année précédente à 24 h. Ce n'est pas forcément vrai. Dans la pratique, comme tout cela dépend de la réglementation, et si celle-ci table sur l'année civile, on peut passer outre. Ce n'est qu'une histoire de définition...
* Pour ce qui est de la fin, fait le calcul, à la main, pour DUCLAQUE, pour Janvier. Combien de période de plus de 7 jours sans repos de 44 h trouves-tu ? Après, fais tourner la macro et compare les résultats...

@ plus

 

[Aloha]

Bonsoir

Re

Et dans la série simplification, pourquoi ne pas faire, dans les fichiers mensuels janvier 2020, Février 2020..., dans E5 et en dessous, avec
DATE(DROITE(D$1;4);BA$3;D5)
au format personnalisé jjjj
Cela donne presque la même chose que la formule déjà en place, et c'est beaucoup plus court.

@ plus

Bonsoir,
En effet, mais les noms des jours sont affichés en allemand, alors que mon Excel et mon Win10 sont en français.
Pour changer la formule je dois faire la modification dans le fichier Mois, le fichier modèle pour fabriquer les fichiers mensuels et il faut que les gens qui préparent les fichiers adoptent cette version modifiée.
A voir; je dirais que cela ne s'impose pas puisque ces formules ne se trouvent pas dans Base.
A+
Aloha

 

[Aloha]

Bonsoir

Il peut y avoir un problème avec le début et la fin car :
* Pour ce qui est du début de l'année, j'ai considéré que la dernière période de repos d'au moins 44 h finissait le 31/12 de l'année précédente à 24 h. Ce n'est pas forcément vrai. Dans la pratique, comme tout cela dépend de la réglementation, et si celle-ci table sur l'année civile, on peut passer outre. Ce n'est qu'une histoire de définition...
* Pour ce qui est de la fin, fait le calcul, à la main, pour DUCLAQUE, pour Janvier. Combien de période de plus de 7 jours sans repos de 44 h trouves-tu ? Après, fais tourner la macro et compare les résultats...

@ plus

Re,
Manuellement j'en ai trouvé 1, et la macro aussi.
Cependant, je ne suis pas sûr avoir bien compris le message que tu veux me transmettre! Que veux-tu dire par "pour ce qui est de la fin", étant donné qu'il s'agit du mois de janvier?
A+
Aloha

 

[Aloha]

Bonsoir

Si tu peux utiliser SIERREUR, la relation suivante serait plus courte :

ARRONDI((SIERREUR(RECHERCHEH($A6;INDIRECT("'"&$B6&"'!$B$1:$Z$4");4;0);$C$1)-MAX(RECHERCHEH($A6;INDIRECT("'"&$B6&"'!$B$1:$Z$4");3;0);$B$1))/365*20;0)

@ plus

Re,
Merci pour la simplification.
Bonne nuit
Aloha