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]

Bonsoir

Et si tu veux les résultats en heure, minute..., il faut diviser par 24 et mettre au format hh:mm:ss.

@ plus

 

[Aloha]

Bonsoir,
en effet, comme il y a déjà la somme, il vaut mieux l‘utiliser plutôt que de faire une nouvelle addition.
Je vais analyser l‘affaire demain; pour l‘instant je suis confronté à une attaque de phishing probable et probablement réussie. Embêtant.
Bonne soirée
Aloha

 

[Aloha]

Bonjour,
Mais à présent il y a un nouveau problème: dans le dernier fichier le tableau contenant les données de base a changé, alors que l‘original correspond en gros au tableau réel.
Qui plus est, le tableau était, pour l‘instant, afin de ne pas trop compliquer les choses au début, réduit à une seule personne, les données se trouvant dans la colonne C.
Dans le fichier réel il y a, à partir de D, une multitude d‘autres personnes.
Donc, modifier le tableau crée des problèmes supplémentaires, et ne permet pas de l‘étendre aux autres personnes, sauf si je collecte les données dans un tableau comme le tien, dans une seule colonne, en bas des données existantes. Mais comme il y a déjà presque 500 lignes et 32 colonnes remplies de formules, je voulais limiter au maximum de nouvelles formules.
Intégrer ton tableau signifierait donc 18 fois 32 formules.
Le résultat doit être décimal, donc 0,75 pour 3 quarts d‘heure.
Bonne journée
Aloha

 

[Aloha]

Bonjour,

J‘ai intégré, malgré la multitude de formules, ta dernière solution dans mon fichier réel.
Il y a un problème dans l‘équivalent de H12 qui renvoie la même date qu‘en H11.
Lorsque j‘analyse les deux formules je constate que le contenu n‘est pas le même:

H11: ...SI((H9 [cellule vide au-dessus du 1er j. de congé] <>““)*(H10 [1er j. de congé]......
H12: ...SI((H10 [1er j. de congé] <>““)*(H11 [Fin 1ère période]...

Est-ce normal et voulu?

Mise à jour du message: tu peux l‘ignorer: une erreur s‘était introduite dans ma formule transposée!

 

[Aloha]

Re,
J‘ai réussi à intégrer tes formules correctement, mais au détriment d’un alourdissement considérable en formules: jusqu’ici il y en avait déjà en gros 15 000 Maintenant il y en a quelque 2400 supplémentaires pour calculer les heures de 2 jours de congé supplémentaires.

Il serait sûrement possible de combiner des formules; il y en aurait moins mais elles seraient plus complexes, ce qui ne serait pas mieux.
A+
Aloha

 

[CISCO]

Bonjour

J'avais oublié de prendre en compte les cas où il faut afficher le 31/12/2017. Pour corriger cela, j'ai changé les formules des colonnes H et I dans la pièce jointe.

@ plus

 

[CISCO]

Bonjour

Et une autre possibilité, avec ton fichier initial, donc avec la disposition initiale, mais avec deux formules matricielles dans les colonnes G et H, à valider avec les 3 touches Ctrl+maj+entrer.

@ plus

P.S : J'ai rajouté dans C1 la date 31/12/2017

 

[Aloha]

Bonsoir,
Merci beaucoup. Ta solution fonctionne bien.
Je me bute maintenant à un autre problème: compter correctement les heures libres.
Mais pour l’instant je suis toujours sous le choc d’une attaque phishing probablement réussie et je ne sais pas si mon ordi est propre ou non. Voilà pourquoi je l’ai déconnecté d’Internet et je n’ai pas encore téléchargé tes fichiers.
A bientôt
Aloha

 

[CISCO]

Bonjour

Bonsoir,
...
Je me bute maintenant à un autre problème: compter correctement les heures libres.
...
Aloha

J'imagine que tu as d'autres chats à fouetter...
Quand tu auras résolu ces problèmes, est-ce que tu pourras nous expliquer davantage ton besoin ?

@ plus

 

[Aloha]

Bonjour,
Je n’ose plus, pour l’instant, reconnecter mon ordinateur au Internet.
Je vais d’abord l’analyser moyennant le Rescue Disk de Kaspersky et d’autres logiciels.

Pour l’instant je ne peux qu’expliquer le problème ici sur mon iPad.

J’ai déjà une solution pour vérifier s’il y a des périodes de 7jours sans temps libre ininterrompu de 44 ans, mais malheureusement les calculs ne sont pas corrects dans certaines situations, et je dois donc améliorer la solution.
Dès que je serai reconnecté je vais envoyer un fichier exemple.
A+
Aloha

 

[Aloha]

...en attendant, j’ai des problèmes avec la soustraction de 2 dates +heures
Ainsi, si je soustraye 12/1/2017 08:00 de 12/1/2017 18:00, le résultat est 9.83

 

[CISCO]

Bonjour

Chez moi, cela donne bien 10:00

@ plus

 

[Aloha]

...je n‘ai toujours pas résolu mon problème de sécurité. J‘ai aussi un MacBook Air où je n‘ai que le tableur Numbers. Je ne sais pas jusqu‘à quel point il est compatible avec Excel. Je vais le tester...

 

[Aloha]

Bonsoir,

J‘ai enfin installé MS Office sur mon Mac et je peux continuer mon projet.

Je rappelle brièvement le but du projet:
Accorder à chaque salarié un jour de congé supplémentaire lorsqu‘un ensemble de 8 cycles continus ou non de 7 jours où il n‘a pas 44 heures de temps libre continu (temps libre + valeurs assimilées à du temps libre: voir ci-dessous) est accompli.

Dans le fichier joint j‘ai élaboré un exemple tout proche de la réalité (pour rendre le transfert des formules plus simple et avec moins de risque d’erreur j’utilise les mêmes colonnes que dans le fichier réel: c’est la raison pour laquelle un certain nombre de colonnes sont cachées), abstraction faite (j‘espère que cette abstraction ne me posera pas problème par après) du fait que les salariés font en partie des tournées se terminant le lendemain, et abstraction faite aussi du fait que le fichier réel est mensuel.

Architecture de l‘exemple:
Les colonnes:
A: Date
F3 à AC4: les heures de la journée, le début dans la ligne 3 et la fin dans la ligne 4
F5 à F32 (février à 28 jours), F33 (février à 29 jours), F34 (mois à 30 jours) ou F35 (mois à 31 jours): les valeurs saisies, chaque X représentant une heure de travail.
FH: la première heure dans laquelle il y a une valeur comptée, les valeurs non comptées, c.-à-d. celles qui interrompent les valeurs comptées, et donc assimilées au temps libre, comme p.ex. C = Congé; ces valeurs se trouvent dans FV4:FV9
FI: la dernière heure où il y a une valeur comptée
FJ: heure en FH + la date
FK: heure en FI + la date
FL: dernière heure et date où il y a une valeur comptée
FM: début des cycles de 7 jours
FN: nombre de cycles
FL: 1 jour de congé supplémentaire lorsqu‘il y a 8 cycles complets

FL4: la dernière valeur du mois précédent, insérée moyennant une formule
FM4: idem
FN4: idem

Comme les valeurs dans FN ne sont pas tout à fait exactes (les calculs sont corrects dans les cycles normaux à 5 jours comptés et 2 jours libres ou assimilés (comme le congé) j‘ai ajouté manuellement les valeurs exactes en FQ à FT.

Le principe est donc de considérer un cycle de 7 jours après l‘autre et d‘augmenter le compteur des cycles d‘une unité chaque fois qu‘il n‘y a pas 44 heures libres continues par cycle de 7 jours.

Il existe une situation où je ne vois pas encore tout à fait clair: lorsqu‘un salarié a p.ex. 1 ou 2 semaines de congé je pense qu‘il faut commencer un nouveau cycle le premier jour compté après le congé. La question est, à partir de combien de jours de congé il faut commencer un nouveau cycle.

Un problème qui n'est pas résolu non plus à l'heure actuelle est de remettre à 0 le compteur des cycles lorsqu'un ensemble de 8 cycles est accompli et qu'un jour de congé supplémentaire est "tombé", mais je pense que cette tâche est bien moins compliquée.

C‘est une affaire très compliquée et je ne suis pas sûr qu'une pure solution à formules puisse satisfaire à toutes ces exigences.

Bonne nuit

Aloha

 

[CISCO]

Bonsoir

Pourquoi est-ce que tu mets 0 cycle dans FR32, puisqu'il ni y a pas de période de 44 h de repos (ou assimilé) dans les 7 jours considérés ? Est-ce à cause des plus de 44 h obtenues avec les lignes 25 et 26, même si la ligne 25 appartient à la période de 7 jours précédente ? Si la réponse est oui à cette dernière question, comment faudrait-il prendre en compte cette ligne 25 vide si la ligne 24 avait elle aussi été vide, c.-à-d. si les lignes 24 et 25 vides avaient constituées une période de plus de 44 h de repos prise en compte dans la période de 7 jours précédente ?

@ plus