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

Aloha

XLDnaute Accro
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,
 

Pièces jointes

  • 44 h. libres.xlsx
    18 KB · Affichages: 50

CISCO

XLDnaute Barbatruc
Bonsoir

Le système attribuant les 1/2 h de congé en fonction des permanences a un petit défaut :
Si le dernier jour du mois comporte 24 h de permanence, le temps de congé attribué ce jour là ne sera pas forcément bon. En effet la valeur attribuée dans ce cas (24 h de permanence) dépend du contenu du jour suivant, contenu qu'on ne connait pas.

Exemple :
le 28, 10 h de permanence en fin de journée
le 29, 24 h de permanence, donc --> 00:30
le 30, 24 h de permanence, donc --> 00:30
le 31, 24 h de permanence, donc --> 00:30 + x

Si le 1er suivant, il y a 0 permanence, on devrait avoir x = 00:30. S'il y a au moins 1 h de permanence dès le début, x= 0.
Dans la pratique, on ne peut pas attribuer la bonne valeur à x, puisqu'on ne connait pas ce qui se passera le 1er du mois.

Pour résoudre ce problème, il faut lancer ces calculs avec les données du 1er du mois suivant.
Ou, si on ne peut pas le faire, vérifier/corriger la valeur attribuée au 31 (30) du mois précédent.

@ plus
 

Aloha

XLDnaute Accro
Bonsoir et bonne année!

Je ne coomprends pas trop le problème, puisque les calculs se font sur les mois cumulés. Il ne devrait donc pas avoir de différence entre le passage du, mettons, 22 au 23 et celui du 31 au 1.

J'ai pas mal travaillé ces jours, j'ai appliqué les changements (permanence) et les nouvelles informations (congé <44h) dans les fichiers réels.
Le code a aussi fonctionné, même si je n'arrive pas à l'intégrer dans le code existant (ainsi je ne sais pas faire autrement que d'ouvrir deux fois les fichiers mensuels, la première fois pour que le code existant copie les données importantes dans la base de données, et une deuxième fois pour copier les chiffres concernant la permanence et le congé <44h.
Seulement, le même code collé dans le fichier réel bloque. - Mais c'est une autre histoire, qui n'a pas sa place dans cette partie du forum, je pense.

J'ai constaté dans mes tests de copier les données permanence & 44h. dans le fichier annuel et que la personne a travillé le 1er du mois, il y a un nombre infini de # dans les colonnes intitulées "Durée du repos avant" et "Durée du repos avant le début du travail, mais après le début de la période de 7 j".

Bonne soirée
Aloha
 

CISCO

XLDnaute Barbatruc
Bonsoir

Bonsoir et bonne année!

Je ne comprends pas trop le problème, puisque les calculs se font sur les mois cumulés. Il ne devrait donc pas avoir de différence entre le passage du, mettons, 22 au 23 et celui du 31 au 1.

Fais un test avec par ex
28/mois : Permanence de 12:00 à 24:00 --> rien
29/mois : Permanence toute la journée --> 00:30
30/mois : Permanence toute la journée --> 00:30
31/mois : Permanence toute la journée --> 00:30 + x

et 1er/mois suivant : soit pas de permanence la première heure (ce qui donnera x = 00:30), soit une permanence (x = 00:00)

Lorsqu'il y a un jour complet (24 h), la formule donne systématiquement 00:30 plus rien ou quelque chose en fonction du contenu de la première heure du jour suivant.

J'ai constaté dans mes tests de copier les données permanence & 44h. dans le fichier annuel et que la personne a travaillé le 1er du mois, il y a un nombre infini de # dans les colonnes intitulées "Durée du repos avant" et "Durée du repos avant le début du travail, mais après le début de la période de 7 j".

Bonne soirée
Aloha

Cela veut soit dire que la colonne est trop étroite pour afficher correctement son contenu, soit qu'il y a une erreur. Élargi là pour voir ce que cela donne.

@ plus
 

Aloha

XLDnaute Accro
Re,
ce n'est pas dans la largeur de la colonne où réside le problème.
Pour l'histoire du passage d'un mois à l'autre, est-ce que tu ne vois pas de remède?
Bien évidemment la situation que tu décris se produira.
J'ai testé et il me semble que cela n'a rien avoir avec le passage d'un mois à l'autre, mais que cette faute se produit lorsque la même situation se produit à n'importe quelle date. J'ai changé les dates et le problème est resté.
Le problème apparaît lorsqu'il y a plus d'une tournée de 24 h. Ce qui dépasse les 24 heures est ignoré. Le dernier jour d'une multi-tournée, la dernière tournée pleine de 24 heures est compensée correctement par 0,5 h., mais les heures qui restent, et qui valent aussi 0,5 h., sont ignorées.
Bonne nuit
Aloha
 

CISCO

XLDnaute Barbatruc
Bonjour

Re,
ce n'est pas dans la largeur de la colonne où réside le problème.

Dans ce cas, est-ce que tu peux mettre une partie du fichier sur le site, en anonymisant le maximum de choses, et en éliminant le bas du fichier. Il suffit qu'il y ai deux ou trois lignes avec des #...


Le problème apparaît lorsqu'il y a plus d'une tournée de 24 h. Ce qui dépasse les 24 heures est ignoré. Le dernier jour d'une multi-tournée, la dernière tournée pleine de 24 heures est compensée correctement par 0,5 h., mais les heures qui restent, et qui valent aussi 0,5 h., sont ignorées.
Bonne nuit
Aloha

C'est la méthode utilisée qui veut ça.

Exemples :
1er cas
a/mois : Permanence de 12:00 à 24:00 --> rien
On ne peut pas attribuer 00:30 à ces 12 premières de permanence car elles sont incluses dans les 24 premières heures de permanence
a+1/mois : Permanence toute la journée --> On a dépassé 24 h de permanence, donc on attribue 00:30
a+2/mois : Permanence toute la journée --> On a dépassé 24 h de permanence supplémentaires, donc on attribue 00:30
a+3/mois : Permanence toute la journée -->On a dépassé 24 h de permanence supplémentaires, donc on attribue 00:30
a + 4/mois : Permanence pendant la première heure uniquement --> On a les 12 dernières h de permanence du jour précédent + la 1ère du jour en cours, soit 13 h de permanence , donc on attribue 00:30.

2ème cas
a/mois : Permanence de 12:00 à 24:00 --> rien
On ne peut pas attribuer 00:30 à ces 12 premières de permanence car elles sont incluses dans les 24 premières heures de permanence
a+1/mois : Permanence toute la journée --> On a dépassé 24 h de permanence, donc on attribue 00:30
a+2/mois : Permanence toute la journée --> On a dépassé 24 h de permanence supplémentaires, donc on attribue 00:30
a+3/mois : Permanence toute la journée -->On a dépassé 24 h de permanence supplémentaires, donc on attribue 00:30, et comme le lendemain ne commence pas par une permanence, les 12 dernières heures apportent aussi 00:30, ce qui fait un total de 01:00 pour ce jour
a + 4/mois : pas de permanence la première heure


3ème cas
a/mois : Permanence de 9:00 à 24:00 --> rien (< 24 h et suivi d'une h de permanence dès le début le lendemain)
On ne peut pas attribuer 00:30 à ces 15 premières de permanence car elles sont incluses dans les 24 premières heures de permanence
a+1/mois : Permanence toute la journée --> On a dépassé 24 h de permanence, donc on attribue 00:30
a+2/mois : Permanence toute la journée --> On a dépassé 24 h de permanence supplémentaires, donc on attribue 00:30
a+3/mois : Permanence toute la journée -->On a dépassé 24 h de permanence supplémentaires, donc on attribue 00:30
a + 4/mois : Permanence pendant les 12 premières heures uniquement
On a 15 h de permanence en reliquat du jour précédent + 12 h le jour en cours = 27 h, donc on attribue 01:00.

Si a+3 correspond au dernier jour du mois, comme on n'a pas les informations correspondant à a+4, le 1er jour du mois suivant, on ne peut pas lui attribuer immédiatement le nombre correct d'heure.

Ce n'est pas un problème lié à Excel ou à la méthode : On ne peut pas attribuer correctement et à coup sûr les heures à un jour comportant 24 h de permanence sans connaitre ce qui se passe le lendemain.

Pour résoudre cela, deux possibilités :
* Attribuer des heures au dernier jour du mois comme si la première heure du jour suivant n'était pas une permanence. Mais il faudra vérifier/corriger le mois suivant, l'attribution correspondant au 1er jour (ce qui peut être fait avec Excel) car on aura peut être certaine fois attribué 00:30 trop tôt.
* Faire les calculs correspondant au mois avec les données du mois et avec celles du 1er jour du mois suivant, donc au plus tôt le 2.

@ plus
 

Aloha

XLDnaute Accro
Re,
Peut-être que je ne conçois pas les choses correctement, mais voici comment je vois les choses:
Comme je ne livre les données, autant pour les 44h que pour la permanence, il ne devrait pas y avoir de problème dans le passage d'un mois à l'autre puisque les données du mois entier seront disponibles.
C-à-d. que, si je livre début juin, au moment où je confectionne les fiches mensuelles vides dans lesquels je fournis les données de janvier à avril, j'ai à ma disposition les données du 1er mai et du 2 mai etc et les calculs devraient être corrects jusqu'au 30 avril puisque je sais déjà comment la personne a travaillé, resp. a fait des tournées de permanence le 1er mai.
Que penses-tu de ce raisonnement?

Par contre, je viens de faire un essai (fichier joint), en mettant des P du 29.1. au 1.2.et la dernière tournée ne donne pas 0:30 h.
Cependant, lorsque je mets les mêmes P du 13.1. au 16.1. le problème reste: la dernière tournée du 16.1. n'est pas prise en compte.

J'en conclus que le problème n'a rien à voir avec le passage d'un mois à l'autre.

Dans le fichier ci-joint tu verras aussi les #. Si tu enlèves les saisies du 1.1. les # disparaissent.

La solution, s'il n'y en a pas d'autre, ne pourrait-elle pas consister à commencer l'années avec le 31.12., sans saisies?

A+
Aloha
 

Pièces jointes

  • Book1.xls
    974 KB · Affichages: 40

CISCO

XLDnaute Barbatruc
Bonjour

Re,
Peut-être que je ne conçois pas les choses correctement, mais voici comment je vois les choses:
.....
vides dans lesquels je fournis les données de janvier à avril, j'ai à ma disposition les données du 1er mai et du 2 mai etc et les calculs devraient être corrects jusqu'au 30 avril puisque je sais déjà comment la personne a travaillé, resp. a fait des tournées de permanence le 1er mai.
Que penses-tu de ce raisonnement?

Si tu arrêtes les calculs fin avril, mais que tu as des données débordant sur mai, il ni a pas de problème.

Par contre, je viens de faire un essai (fichier joint), en mettant des P du 29.1. au 1.2.et la dernière tournée ne donne pas 0:30 h.
...
A+
Aloha

Je regarde ça dès que possible.

@ plus
 

CISCO

XLDnaute Barbatruc
Bonsoir

Dans les colonnes AE et AG, j'avais fais commencer certaines plages en ligne 16, avec un $16 , alors qu'il fallait les faire commencer en ligne 15 avec un $15.


Et pour ce qui est de la colonne AO, dans les versions 1200 et 1200bis, j'avais oublié que les cellules apparemment vides des colonnes AM et AN contiennent en réalité un zéro. Donc, il ne fallait pas faire avec un EQUIV(9^9;dernièrelignenonpleine;1) mais avec SIERREUR(EQUIV(0;dernièrelignenonpleine;0)-1;3).

C'est corrigé dans les 4 derniers fichiers de mes derniers posts et dans la pièce jointe.

@ plus

* : EQUIV(9^9;dernièrelignenonpleine;1) renvoie toujours la position du dernier nombre dans la plage dernièrelignenonpleine. Comme dans cette plage il y a des séries du style 5, 3, 7, ou 5, 3, 0, ou 10, 0, 0 (et jamais 0, 0,0), cela renvoie toujours 3 alors qu'on voudrait avoir respectivement, pour les 3 exemples précédent 3, 2 et 1.
 

Pièces jointes

  • Book1.xls
    994.5 KB · Affichages: 29
Dernière édition:

Aloha

XLDnaute Accro
Re,
Merci
J'ai changé les formules de AE, AG et AO dans mon fichier réel.
Malheureusement, lorsque je mets un X dans une cellule de la première ligne vide du fichier réel, il y a à nouveau les #, alors que dans le fichier que tu viens de me renvoyer ils n'apparaissent pas.
Il faut que j'en comprenne le pourquoi.
A+
Aloha
 

Aloha

XLDnaute Accro
Bonjour,
Je ne suis pas à la maison jusqu'à ce soir, voilà pourquoi je n'ai pas encore pu analyser ce problème.
En tout cas c'est assez bizarre puisque la fiche dans laquelle tu viens de faire les corrections est identique à celle que j'ai corrigée par la suite. Avant de l'envoyer j'ai fait une copie de la fiche dans un nouveau classeur, c'est tout - et j'ai remplacé les dates de la personne par leurs valeurs mais cela ne peut pas avoir un impact.
Je vais voir ce soir et s'il faut je vais prendre celle que tu as corrigée avec succès comme modèle.
A+
Aloha
 

Aloha

XLDnaute Accro
Bonjour,

Je n'ai pas réussi à faire disparaître les # et j'ai donc pris le fichier que tu m'as renvoyé et j'en ai fait la fiche modèle.
Une question: comme les calculs reprennent le 1.1., faut-il toujours les cellules vertes de AD12 à AK12?

A+
Aloha
 

CISCO

XLDnaute Barbatruc
Bonjour

Les colonnes AJ et AK ne sont pas indispensables (Je pensais qu'elles seraient nécessaires pour faire les calculs de la plage L6:U8, mais ce n'est pas le cas).

Les valeurs dans les cellules vertes dans la plage AD12:AH12 sont, dans l'état actuel, nécessaires. Si je me souviens bien, au début, nous avions commencé sans. Elles sont nécessaires si tu n'utilises pas pour chaque employé un seul fichier (les nouvelles données étant mises au bout du précédent fichier), mais si tu en utilises un nouveau à chaque fois.

Dans AF12, j'avais mis 28/12/2017 pour obtenir une période de 7 jours à cheval sur les deux années. Mais comme maintenant, tu m'as dit que les calculs devaient recommencer 1/1/x chaque année, il ne faut plus faire avec cette date mais avec le 25/12/2017. Il faut aussi mettre le dernière heure travaillée par la personne en question dans la cellule AD12, le 31/12/2017 à 18:00 par ex. Mais si vous voulez vraiment recommencer tous les calculs en début d'année, il faut plutôt faire avec 01/01/2018 à 00:00 dans cette cellule.

Essaye sur ton fichier en changeant le contenu de ces deux cellules et dis moi ce que cela donne.


@ plus

PS : AD12 --> fin de la dernière heure travaillée par la personne dans la période précédente
AF12 --> début de la dernière période de 7 jours précédente
 

Discussions similaires