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,
 

Fichiers joints

Aloha

XLDnaute Accro
Bonjour,

Merci.

Cette formule met en effet un chiffre 1 là où il faut, mais uniquement si la personne est libre pendant 48 heures de suite; cependant la condition est qu'elle doit être libre pendant au moins 44 heures.

De toute façon, je me suis rendu compte que je dois aborder le problème autrement, sous peine de résultats faux. Je dois définir la première et la dernière heure par jour où il y a une inscription.

Je m'explique: il y a une différence fondamentale entre les deux situations suivantes où le nombre d'heures libres par jour est le même:

1. une personne a inscrit une valeur un premier jour dans toutes les cases de 00:00 à 12:00, le lendemain rien, le surlendemain de 12:00 à 24:00
Résultat: 12 heures libres le 1er jour, 24 h. le lendemain et 12h le surlendemain, ce qui fait 48 heures

2. elle a inscrit une valeur un premier jour dans toutes les cases de 12:00 à 24:00, le lendemain rien, le surlendemain de 00:00 à 12 heures
Même résultat avec la méthode actuelle

Mais:
1ère situation: 48 h. libres de suite
2ème situation: elle est libre de suite du 1er jour à minuit au 2e jour à minuit, donc seulement 24 heures

J'ai transformé le fichier pour intégrer le formulaire sur lequel le salarié saisit ses heures (X=travail, M=Maladie, C=Congé, R=Réunion).

Ce qu'il me faut en premier lieu c'est une formule pour produire les valeurs que j'ai mises en orange.

Bien à vous
Aloha
 

Fichiers joints

CISCO

XLDnaute Barbatruc
Bonjour

Tu peux faire dans AA4 avec SIERREUR(EQUIV("*";C4:Z4;0)-1;"")
et dans AB4 avec SIRERREUR(EQUIV("zz";C4:Z4;1)-1;"")

@ plus
 

Aloha

XLDnaute Accro
Bonjour,
J'ai pas mal progressé dans mon projet mais maintenant je me retrouve devant un obstacle de taille!
Il s'agit de convertir les jours de congé supplémentaires en heures et de les répartir doublement proportionnellement: d'après la tâche hebdomadaire et du nombre de jours par tâche hebd., celle-ci pouvant changer au cours de l'année.

Si p.ex. une personne a droit à un jour supplémentaire le 28.3. (parce que à cette date il y a un cycle complet de 8 périodes de 7 jours sans temps libre ininterrompu de 44 heures).
Admettons que cette personne ait une tâche hebdomadaire de 40 heures du 1.1. au 30.4., puis de 25 heures du 1.5. au 31.8., et de 30 heures du 1.9. au 31.10.
1ère difficulté; définir la période dans laquelle tombe la date du 28.3. (et ignorer celles d'avant; p.ex. si le jour tombe le 11.7., la première période, allant du 1.1. au 30.4. n'est pas concernée); c'est donc dans la première période
2ème difficulté: prendre les bonnes dates dans le tableau
3ème difficulté: calculer les jours depuis la date d'échéance du jour de congé
4ème difficulté: répartir les heures selon la tâche hebdomadaire

J'ai préparé un exemple.
Est-il possible d'intégrer tous les calculs dans une formule par jour de congé supplém. (pour éviter d'ajouter trop de formules et de lignes supplémentaires dans une feuille qui contient déjà des centaines de formules, et pas des moindres).

C'est très très complexe, ce que je vous présente là! Merci à tous ceux qui voudront s'y attarder.

Bien à vous
Aloha
 

Fichiers joints

Aloha

XLDnaute Accro
Bonsoir,
Dans le fichier réel elle vient d'une base de données avec RECHERCHEV(). Donc pas de problème de ce côté-là.

D'ailleurs j'ai remarqué qu'il y a une erreur dans G11 à G13: il y a un renvoi à une cellule qui se trouvait probablement dans une colonne avec d'autres calculs qui ne menaient à rien et qui doit contenir la date du 31.12.2017.
Bonne soirée
Aloha
 

CISCO

XLDnaute Barbatruc
Bonjour

Quel est le problème dans I15 ?

Est-ce que tu peux m'expliquer davantage la formule dans J11 ?

@ plus
 

Aloha

XLDnaute Accro
Bonjour,

Tu veux dire I13? Tout simplement, je n'ai pas réussi à fabriquer une formule correcte qui maîtrise toutes les situations. Mais ça c'est le plus petit problème. En m'appliquant bien, je pense que j'y arriverais.
Mon but est de limiter le tout au plus petit nombre de formules et colonnes supplémentaires possible.

La mission de la formule en J11 est de calculer (en heures)la part des 33 jours sur le total de 217 jours du 28.3. au 31.10.où la tâche est de 40h/sem. du jour de congé supplém. ,
celle en J12 la part des 123/ jours sur 217 à 25h/sem. et celle en J13 (qui ne fonctionne pas parce que celle en I13 ne fonctionne pas) la part des 61 jours à 30 h/sem.

A+
Aloha
 

CISCO

XLDnaute Barbatruc
Bonjour

Pourquoi ne fais tu pas directement avec =G11-G10 dans I11 ?

Je te pose des questions pour bien cerner ton besoin, certaines ne servent pas directement pour résoudre ton problème actuel, mais bon...

@ plus
 

Aloha

XLDnaute Accro
Bonsoir,
Parce qu'il n'y a pas toujours, ou même rarement, une 3e période. C'est pourquoi je dois vérifier s'il y en a une. Et en plus la date fin peut être -ou l'est même normalement- vide, ce qui signifie qu'il y a CDI et que la salariée continue au-delà de l'exercice en cours avec cette tâche.

La plupart des salariés ne changent pas de tâche hebdomadaire au cours de l'année, mais ça arrive.
Au début je n'avais prévu que 3 tâches différentes possibles, mais il y avait une situation où une salariée avait 3 changements, c'est pourquoi j'avais étendu le système à un maximum de 6 tâches différentes.
Bonne soirée
Aloha
 

Discussions similaires


Haut Bas