Problème de colorisation de cellules en fonction d'un créneau horaire

[MasterX]

Bonjour à tous,

voilà mon problème, je dois présenter bientôt un tableau hebdomadaire où doit figurer les heures (divisées en quart d'heure) effectuées par le personnel par des codes couleurs.
En orange les heures en sus et en vert les heures normalement travaillées. En sachant que les fameuses heures en sus peuvent être effectuées soit en début, soit en fin de vacation.
Cependant je me heurte à une difficulté quand les créneaux se rapprochent de 00h00(si la vacation excède minuit, la formule bug également), les colorisations ne se font pas comme il faudrait. J'utilise une formule de ce type en MFC : =(J$5>=MOD($A6;1))*(K$5<=MOD($B6;1))
Je précise que les employés peuvent s'il le désire, revenir travailler sur une des 2 journées de repos, ce qui a pour effet de rajouter un 3ème code couleur (rouge).

Dernier petit soucis, en fin tableau, un commentaire succinct récapitule la plage horaire sur laquelle les heures supplémentaires ont été faites et si sur un 1er ou 2ème jour de repos.

Mon niveau étant assez basique, je n'arrive pas à trouver les formules adéquates pour y parvenir. Je ne sais pas si j'ai été clair dans mes explications mais si une âme généreuse se dévoue pour se pencher sur mon problème, vous êtes la bienvenue....

Merci par avance....

 

[Guest]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Bonjour,

En attendant qu'un super formuliste comme il en existe ici te réponde, voici un essai, si j'ai tout compris avec la formule MEDIANE, sachant que la mediane de trois nombres triés est toujours le deuxième.

A+

 

[MasterX]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Bonjour Hasco,

merci pour ton début (et même quasi) solution, franchement je ne connaissais pas l'utilité de la fonction médiane!!! Je me pencherais dessus dès que possible.

Cependant, il s'avère que dans la dernière ligne (21 août), la "colorisation" est faussée. Oui en effet, celle des heures optimisées doit démarrer de 23h30 à fin du tableau car en en fait, il s'agit d'une seule journée continue qui se termine le jour suivant!

Quelqu'un du forum, avait déjà soulevé le même type de problème il y a quelques temps et apparemment, il n'a pas eu la solution tant attendue.

C'est pourquoi ,j'ai rajouté en fin de tableau les petites annotations (qui sont censées apparaître que lorsque la journée a bien été travaillée) pour indiquer que la vacation s'est terminée le jour suivant mais aussi quel était le type de journée.

Comme tu peux le constater, je me suis emmêlés les pinceaux dans les dites formules.

Vu le nombre de post à ton actif, je pense que tu détiens peut être le sésame concernant cette partie et éventuellement le secret de la "colorisation" s'effectuant quand les heures dépasse minuit.

Dernière petite chose, sur le 1er onglet saurait tu pourquoi quand je concatène les heures, un bug de formule (#######) apparaît?

Je t'en demandes beaucoup je sais, mais j'ai vraiment besoin de résoudre ce casse tête.

Merci encore pour tes recherches et je m'y replonge également de mon côté....

A+ tard

 

[Victor21]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Bonsoir, MasterX, Hasco.

Concernant la "dernière petite chose",

sur le 1er onglet saurait tu pourquoi quand je concatène * les heures, un bug de formule (#######) apparaît?

Si tu passes la souris sur la cellule J13, Excel te donne la réponse : heures négatives.
Bien qu'excel sache les calculer, il ne veut toujours pas les afficher ...
Pour pallier à ce problème, remplace la formule actuelle par :

=MOD(E13-D13;1)+MOD(I13-H13;1)

* Sauf erreur de ma part, tu ajoutes des heures, et tu concatènes des chaines de caractère;

 

[MasterX]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Bonsoir Victor21,

je me doutais bien que c'était un problème d'horaires négatifs mais apparemment j'ai oublié les bases élémentaires, j'ai pas touché à ce genre de formule depuis X années (honte sur moi, snifff...). Merci pour le rectif' sur le terme "concaténation" mais surtout d'avoir résolu mon p'tit problème d'heures négatives.

Bonne soirée.....

 

[Guest]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Re,

Hello Victor

@MasterX, merci pour ton retour, mais crois moi, je suis loin d'avoir le sésame ouvrant la solution. Je ne suis pas du tout performant, en matière de calculs horaires un peu complexes.

Patiente un peu, éventuellement fait remonter ton fil. Il y aura bien un "costaud des formules" qui te répondra.

Bon courage.

A+

 

[Guest]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Re,

En utilisant les priorités des formats conditionnels, j'ai réussi à simplifier les formules, en rendant la première plus "juste" (à mon sens).

la cellule DA6 est passée de 00:00:00 à 23:59:59

Pour la MFC Orange j'ai rajouté une condition à Mediane pour passer éventuellement au lendemain (même ligne)

Ce serait plus facile si dans tes cellules A7 à D13 tu rajoutais les dates correspondantes (en affichant uniquement l'heure)

A+

 

[MasterX]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Bonjour le forum,

Hasco je viens de prendre connaissance de ton dernier post, et pour quelqu'un qui n'est "pas du tout performant, en matière de calculs horaires un peu complexes", et ben chapeau bas!!! Ta solution me convient tout à fait et réponds parfaitement à ma demande, je te remercie pour ton implication et tes recherches.

Je vais un peu peaufiner la partie des commentaires de fin de journée et je pourrais enfin mettre le tableau en application après 5 jours de galère intense.....

Merci encore à tous ceux qui se sont penchés sur mon problème et très longue vie à ce forum qui est par ailleurs le meilleur de tous.

Je considère le problème comme résolu.

Bonne journée à tous.

 

[david84]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Bonjour à tous, Hasco, Victor
juste pour voir si j'ai compris avant éventuellement d'affiner (je n'ai traité que la MFC mais sûrement pas complètement).
A+

 

[Guest]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Salutations du matin,

@David, savais bien qu'un formuliste de premier ordre passerait par là. Ben j'aurais pas pensé à Equiv! Bravo.

Je vois que tu as eu le même problème que moi pour les cellules en DA12 sur la ligne où il n'y a rien. La seule façon que j'ai trouvée de le résoudre c'est de mette 23:59:59 en DA6 plutôt que 00:00:00.

D'après toi, une autre solution existe-elle?

A++

 

[david84]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Re Hasco,
Je n'avais même pas fait attention à cela.
cf.fichier.
Si tu vois d'autres points à affiner ou reprendre, n'hésite pas.
A+

 

[Victor21]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Bonjour, le fil, Hasco.

@ Hasco : que se passe-t'il si tu saisis "1" (sans les guillemets) tout simplement en DA6 ?
Explication : une journée = 1, une heure =1/24...

 

[david84]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Re re Hasco,
erreur de fichier au message précédent : erreur corrigée (cf fichier message précédent).
A+

 

[Guest]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Re,

Avec 1 c'est bon.

Par contre il y a une chose que je comprend peut-être mal:

Sur la ligne 13: La période 23:30 à 1:30 (le lendemain) est colorisée sur la même ligne 23:30 à 00:00 en fin de ligne (logiquement normal) mais 00:00 à 01:30 est colorisée en début (de journée?!)

A+

 

[david84]

Re : Problème de colorisation de cellules en fonction d'un créneau horaire

Re Hasco

Sur la ligne 13: La période 23:30 à 1:30 (le lendemain) est colorisée sur la même ligne 23:30 à 00:00 en fin de ligne (logiquement normal) mais 00:00 à 01:30 est colorisée en début (de journée?!)

Ben oui puisque la plage d'heures d'optimisation va de 23:30 à 1:30...il me semblait que c'était ce que voulait MasterX...suis-je dans l'erreur ? Avais-tu une autre idée pour faire apparaître cette plage dans la MFC ?
A+