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]

Bonjour

J'ai supprimé une ligne vide au début, et modifié la formule de la colonne FO pour ne pas avoir de problème.

Testes bien les deux fichiers. En fonction des résultats de tes essais, j'intercalerai des lignes vides entre chaque date, et verrai s'il faut modifier les formules.

@ plus

 

[Aloha]

Re,
Je suis vraiment désolé pour tout le travail que tu as fait, mais il ne faut pas de cellules vides dans la ligne 5 jusqu'à AC, puisque la feuille dans laquelle j'intégrerai les formules n'en a pas, à partir de AD, pas de problème.
A+
Aloha

 

[CISCO]

Bonsoir

Et comme ça, est-ce que c'est possible ?

@ plus

 

[Aloha]

Bonsoir,
J'ai fait une copie d'écran de la partie concernée de la feuille originale.
Entre les lignes 4 et 5 du tableau original il n'y a pas de ligne vide; si j'en ajoutais une elle me poserait trop de problèmes.

Mais j'ai une idée: si le système ne peut vraiment pas fonctionner sans cette ligne vide: si je reproduisais, à l'aide de simples formules, le tableau sur la même feuille à droite de la dernière colonne utilisée: là il y aurait davantage de facilités.

A+
Aloha

 

[Aloha]

Re,

J'ai fait exactement cela dans le fichier joint. En plus cela a l'avantage de ne pas être embêté par les lignes vides entre les dates.

A+
Aloha

 

[CISCO]

Bonsoir

N'importe comment, ma dernière proposition n'est pas assez polyvalente : écris 27/12/2017 dans FM4 et tu verras le "désastre".

@ plus

 

[CISCO]

Bonsoir

Et comme ça ?

@ plus

P.S : 23 h 25 : Je viens de changer la pièce jointe, car il y avait des erreurs dans certaines cellules vertes (elles allaient chercher des infos systématiquement et uniquement dans la dernière ligne du tableau, sur la ligne 35, ce qui convient pour les mois comportant 31 jours, pas pour les autres)

 

[Aloha]

Bonsoir,
Si cela fonctionne dans toutes les situations alors c‘est parfait.
Je suis couché et sur iPad où je ne peux voir le tableau que comme image. Le formatage de la dernière colonne, contenant les jours supplémentaires, semble être particulier: toutes les cellules affichent [0] et [1].
Bonne nuit
Aloha

 

[CISCO]

Bonsoir

Non, il ni a pas d'affichage particulier dans la dernière colonne. C'est peut être lié à ton Ipad/Excel.

@ plus

P.S : Attention, j'ai changé la dernière pièce jointe, à cause d'erreur dans les cellules vertes.

 

[CISCO]

Bonjour

J'espère que le dernier fichier fonctionne.
Je crois que je l'ai déjà écrit, mais je préfère me répéter au cas où...
Le fichier mensuel pose encore un petit problème. Je m'explique :
Nous connaissons les données utiles du mois précédent grâce aux cellules vertes. Pas de problème de ce coté là, au début du mois donc. Par contre, pour le dernier jour du mois, il y a un problème si la personne travaille après minuit. Dans ce cas, et comme nous n'avons pas les données du premier jour du mois suivant, la dernière valeur dans la colonne FI sera fausse (elle indiquera 24:00 alors que c'est x:00 le lendemain), et de proche en proche, d'autres sur la même ligne et dans les cellules vertes destinées au mois suivant...

Pour résoudre cela, il faut :
* soit faire des calculs sur des périodes légèrement plus grandes, par ex du 1/12017 au 1/2/2017 inclus (sachant que seuls seront corrects les résultats du 1/1/2017 au 31/1/2017), du 1/2/2017 au 1/3/2017 inclus (sachant que seuls seront corrects les résultats du 1/2/2017 au 28/2/2017), du 1/3/2017 au 1/4/2017 inclus et ainsi de suite. On peut même aller plus loin que le premier jour du mois suivant. A voir...
* soit trouver une astuce au niveau du passage du dernier jour du mois au premier jour du mois suivant pour tenir compte de cette possibilité (travail après minuit).

Dans les deux cas, il faut revoir un peu les formules.

@ plus

P.S : J'ai changé la dernière pièce jointe, car il y avait des erreurs dans la colonne FN

 

[Aloha]

Bonjour,

Vu tous ces problèmes (un problème que tu viens de mentionner me semble irrésoluble: savoir en janvier comment la personne travaillera le 1er février, puisqu‘uniquement les données jusqu‘au 31.1. sont disponibles) ne penses-tu pas qu‘il vaut mieux abandonner la solution mensuelle et nous concentrer sur la solution annuelle qui, me semble-t-il est déjà en état de fonctionner?

Voici éventuellement un argument supplémentaire pour la solution annuelle: ce matin j‘ai eu un petit choc lorsque j‘ai entendu une interprétation différente des „7 jours“, à savoir qu‘il faudrait considérer toujours les derniers 7 jours pour voir s‘il y avait les 44 heures.
P.ex. le 16.1. il faudrait vérifier si depuis le 10.1. il y eu les 44 h., le 17.1 vérifier à partir du 11.1., le 17.1. à partir du 12.1.

Il faudrait commencer le 7.1., où il y a les premiers 7 jours complets.

Pour l‘instant j‘ignore quelle interprétation est la bonne. Je verrai plus clair jeudi prochain.

Ce qui me semble certain c‘est que ce système-là ne peut fonctionner que sur une base annuelle, puisqu‘il faut toujours remonter jusqu‘au dernier du mois précédent moins 5 jours.

Conclusion: ne travaille plus trop sur la solution mensuelle pour l‘instant pour ne pas faire du travail inutile.

Si par contre tu considères cette tâche bien complexe comme un défi, alors je t‘inviterais à réfléchir ensemble à la faisabilité de ce système tout à fait différent.
A premier abord il me paraît bien plus simple à réaliser puisqu‘il regarde strictement les derniers 7 jours.
A+
Aloha

 

[CISCO]

Bonjour

En fait, le dernier jour du mois, ne pose pas réellement problème. Et si, et si...

On a deux cas :
* Soit le dernier jour du mois est aussi le dernier jour de la période de 7 jours en cours, et on n'a pas besoin des informations concernant le 1er jour du mois suivant pour calculer la durée de la dernière période de repos intéressante :
Si la personne travaille ce jour là, la période de repos avant ce travail est connue. Celle après est trop petite (et d'ailleurs, elle n'est jamais calculée dans les fichiers précédents)
Si la personne ne travaille pas ce jour là, la dernière période de repos finie à minuit (puisque les heures de repos du lendemain compteront sur les 7 j suivants) et on peut la calculer.
* Soit le dernier jour du mois n'est pas le dernier jour de la période de 7 jours en cours. Très souvent, on ne peut pas attribuer de jour de congé supplémentaire en fonction de cette période de 7 j, puisqu'elle n'est pas finie. Dans ce cas, certains calculs concernant ce dernier jour sont faux si la personne travaille après minuit, mais cela n'est pas grave. Les calculs des durées de repos seront bons une fois la période de 7 jours finie. Autrement dit, vous aurez l'information, faut-il attribuer ou pas 1 j de congé supplémentaire, un peu trop tard, à la fin du mois suivant (Cf. PS).


Bien sûr, cela nécessite quelques changements dans les formules. C'est fait en pièce jointe.

En fait pour ne pas avoir de problème, il faudrait faire les calculs uniquement à la fin des périodes de 7 j. Et comme tout le monde n'a pas les mêmes périodes de 7 j, je suppose, cela n'est pas vraiment possible...

@ plus

P.S : Si on fait le difficile, on pourrait chercher à voir si dans le temps qu'il reste avant la fin de cette période de 7 j, on peut avoir une période de 44 : 00 de repos, en tenant compte du repos en cours, mais bon... S'il ne reste plus qu'un jour, et que la période de repos en cours est de par ex 8 h, on sait que cela donnera au plus 8 + 24 = 32 h ... C'est bien compliqué...

 

[CISCO]

Bonjour

Bonjour,
....
P.ex. le 16.1. il faudrait vérifier si depuis le 10.1. il y eu les 44 h., le 17.1 vérifier à partir du 11.1., le 17.1. à partir du 12.1.

A+
Aloha

Effectivement, cela serait certainement plus simple sur un fichier annuel... A voir et à préciser.

@ plus

 

[Aloha]

Bonsoir

J'ai étudié un peu cette méthode alternative pour compter les 7 jours et je me rends compte que ce n'est sûrement pas la bonne: jusqu'au 28.1. il y aurait 12 occurences; avec l'autre méthode seulement 1.

Donc il faut rester sur la première méthode, et nous concentrer sur la solution annuelle.

Bonne nuit
Aloha

 

[CISCO]

Bonjour

Qu'est-ce qu'il faut faire sur le fichier annuel, à part glisser une ligne vide entre chaque date, et modifier la macro en ta possession pour qu'elle aille écrire les nouvelles données mensuelles à la fin du tableau et pas systématiquement à partir de la ligne 5 ?

@ plus