Petite formule à trouver

[May]

Bonjour à tous,

Je travaille sur des plannings et il y a une formule qu'en faite je n'arrive pas à trouver ni à imaginer

Dans mon planning ce que j'aimerai c'est de pouvoir avoir à côté du total d'heure hebdomadaire une alerte qui dit "Repos Hebdo!" lorsqu'il n'y a pas 35h consécutives de repos par semaine (on les retrouve généralement le week-end).

Du coup je n'arrive pas à voir comment je pourrai trouver cette formule.

Je vous remercie d'avance de votre aide

May

 

[May]

Re : Petite formule à trouver

Re !

Très bien.
Pour les formules, pourriez-vous m'expliquer les formules dans les colonnes H et I s'il vous plait?

Je préfère m'assurer de bien les comprendre.. ^^)

May.

 

[ysassi]

Re : Petite formule à trouver

alors une petite explication sur mes formules

dans la colonne I il est comptabilisé les heures non travaillées la veille (de la dernière h à minuit ou le cumul si c'était un jour de repos) + les heures non travaillées du matin (de minuit au début de la journée).

la colonne J c'est le nbre d'h non travaillées en fin de journée (de la dernière h à minuit)

en colonne K c'est un cumul des heures qui ne correspond de l'avant veille + 24h si c'est un jour non travaillé, ou bien le nombre d'h non travaillées en fin de journée qui servira a calculer pour le jour suivant.

je ne sais pas si j'ai été clair,
du coup en regardant d'un peu plus près j'ai modifier un peu pour permettre de finir une journée à 9h et commencer le lendemain à 20h. Il y a bien 35h consécutives

 

[ysassi]

Re : Petite formule à trouver

ha oui j'ai oublié d'effacer la colonne H !
elle ne sert a rien, c'était pour faire des essais. le contenue de la colonne H peut être supprimé

 

[Victor21]

Re : Petite formule à trouver

Re, Bonjour à vous également, ysassi.

=SI(F7="";"";ARRONDI.SUP(F7-MAX(G2:G6);7))

Si F7 est vide, ne rien mettre. Sinon
Date et heure de la prise de service- date et heure de la dernière fin de service. Le arrondi.sup (formule;7)permet d'annuler les erreurs d'arrondi.

=SI(OU(JOURSEM(A12;1)>1;MAX(H6:H12)=0);"";SI(MAX(H6:H12)<35/24;"Repos hebdo !";"OK"))

Si on n'est pas un dimanche ou si le jour n'est pas travaille, ne rien mettre. Sinon
Si toutes les valeurs, dans les 7 lignes de la semaine considérée sont inférieures à 35 heures, alerte, sinon Ok.

 

[May]

Re : Petite formule à trouver

Re à tous les deux!

Un grand merci

Je pense que vous m'avez bien aidé et que je n'aurai rien pu faire sans vous.

Je vais les regarder et voir ce que je peux faire en fonction de la présentation dont j'ai besoin alors ne vous étonnez pas si j'ai re-besoin de vous dans pas longtemps! ^^) (ce qui arrivera sûrement si je me rends compte que finalement je n'ai rien compris haha)

Je vous souhaite une agréable journée et un bon week-end!



May.

 

[CISCO]

Re : Petite formule à trouver

Bonsoir

Bonjour à tous,

Concernant l'heure de début et l'heure de fin elles sont bien rentrées (exemple: 6:55, c'est l'heure à laquelle la personne commence) mais j'imagine que ça ne vous suffit pas pour la formule...

Cisco: je ne peux pas te donner d'heures exactes pour cette phrase "Une personne commence le matin pour travailler ensuite (combien de temps ?), a une pause d'une heure et reprend le travail pour finir un peu plus tard (ou combien de temps, ou quand ?)."
Je veux dire que je n'ai pas d'heures précises parce que ça variera beaucoup et si je les avais je n'aurai pas besoin de la matrice que j'essaye de faire actuellement.
Sinon je peux p-e te dire comment la journée s'articule sans donner d'heures précises:
- Une personne commence le matin, disons 7h pour rester simple, puis travaille 6h et a ensuite 1h de pause. Il reprend à 14h pour travailler 2h, donc jusqu'à 16h.
- Pour l'après-midi, l'autre personne commence à 13h pour travailler 2h et a ensuite une 1h de pause. Il reprend à 16h pour travailler 6h, donc finir à 22h.
...

May.

Maintenant que tu nous a donné toutes ces explications, je comprends mieux ton tableau, et il est vrai que cela parait assez évident... avec ces explications, et en regardant les formules en colonnes F et G. Cela aurait été plus facile pour nous s'il y avait eu des entêtes, à savoir en B7, "début" ou "embauche", en C7 "pause", en D7 "reprise" et en E7 "fin de la journée de travail".

@ plus

 

[Victor21]

Re : Petite formule à trouver

RE, May, bonsoir, CISCO

Je reçois à 21:50 en MP :

Bonjour Victor21,

Je reviens vers vous puisqu'il y a quelque chose que je ne comprend pas dans le tableau que vous m'avez remis.
Dans la colonne I, il semble que les heures de repos prises en compte soient les heures de repos des 7 derniers jours.
Mais finalement si vous avez un peu de temps (et de la volonté surtout), pourriez-vous me réexpliquer plus simplement la formule de la colonne I?
Et juste pour m'en assurer, la formule de la colonne H également s'il vous plait?

Si je pouvais comprendre la formule je pourrai essayer de revoir la mise en forme pour ce que je suis sensée faire...

Je vous remercie de votre aide précieuse et de votre temps.

Cordialement,

May.

Pourriez-vous préciser ce que vous n'avez pas compris dans mon explication, au #19 ?

 

[May]

Re : Petite formule à trouver

Hello,

Cisco: oui j'imagine que je n'ai pas été du tout clair depuis le début. Mes excuses ^^)

Victor21: Déjà, si j'ai bien compris, dans la colonne H, ce sont bien les heures de repos de chaque jour, c'est ça?
En colonne I, est-ce un cumul des heures de repos de la semaine et ensuite vous comparer le résultat à 35h? S'il y a 35h, c'est OK sinon c'est alerte. Est-e que c'est exacte?

J'ai remarqué que la formule prenait en compte les 7 derniers jours et non la semaine. Parce que du coup quand je clique sur une cellule qui n'est pas le dimanche il n'y a rien qui apparaît. Je pense avoir compris le résonnement mais moins le fonctionnement de la formule comme ce qu'il prend en compte ou non et pourquoi.

En espérant que vous avez compris quelque chose. J'avoue que c'est un peu flou...

Mais merci à vous pour votre temps

May.

 

[Victor21]

Re : Petite formule à trouver

Re, May

En H, c'est le total d'heures de repos depuis la fin de la vacation précédente jusqu'à la reprise, pour un jour travaillé.
En I, et puisque vous n'aviez pas répondu à ma question concernant les 7 jours glissants ou la semaine, j'ai considéré que vous n'effectuiez cerre vérification qu'en fin de semaine (d'où le test SI(JOURSEM(A12;1)>1...)

 

[May]

Re : Petite formule à trouver

Re,

Je m'excuse je pensais que si... effectivement la vérification se fera en fin de semaine, c'est à dire semaine calendaire (le dimanche du coup) et non 7 jours consécutifs.

La vérification ne se fait uniquement le dimanche alors?

Merci encore

May.

 

[Victor21]

Re : Petite formule à trouver

Re,

R...La vérification ne se fait uniquement le dimanche alors?...

Dans ma proposition, clairement, oui !

Remarque : ce fil ressemble de plus en plus à un "tchat"

 

[CISCO]

Re : Petite formule à trouver

Bonjour à tous, bonjour Victor21

Une autre méthode en pièce jointe, sans colonne intermédiaire, mais avec une formule matricielle à valider avec Ctrl+maj tempo+entrer.

Je pense que c'est bon, mais il faut mieux vérifier.

Pour comprendre, Cf. les formules en colonne W. On ne doit pas avoir de journée du type travail le matin, repos l'après midi pour que cela fonctionne.


Dans Excel, l'unité de travail du temps est le jour, donc 1 heure = 1/24 par exemple, 24 h = 1.

On ne fait pas le calcul sur la ligne du lundi (car le lundi matin, on ne peut pas avoir déja plus de 35 h de travail !. Le mardi matin, si on commence après 11h, si). Le dimanche est aussi un cas particulier.

Exemple : en W13 (samedi donc pour commencer dans un cas "simple"), on doit calculer
B13+(1-E12)
(B13 pour connaitre la durée du repos avant la reprise du travail sur la ligne 13, le matin, et 1-E12 pour calculer la durée du travail entre l'arrêt du travail et minuit sur la ligne 12)
mais si E12 est vide (donc E12=0), on doit faire avec
B13+1-E12+1-E11 ce qui est fait avec (il y a un 1 en plus car la ligne vide compte pour 24 h)
B13+1-E12+(JOURSEM(A12)>2)*(E12="")*(1-E11)

En W14, le dimanche, cela donne
B14+1-E13+(JOURSEM(A13)>2)*(E13="")*(1-E12)

mais il faut traiter le dimanche différemment s'il est de repos, (B14 est vide, donc ce jour compterait pour 0 avec la formule ci-dessus, or il doit compter pour 1), ce qui est fait avec
SI((B14="")*(JOURSEM(A14)=1);1;0)+B14+1-E13+(JOURSEM(A13)>2)*(E13="")*(1-E12))

et lorsque ce n'est pas le dimanche, et que la ligne est vide, ce n'est pas la peine de faire le calcul, ce qui est fait avec
SI((B14="")*(JOURSEM(A14)>1);"";SI((B14="")*(JOURSEM(A14)=1);1;0)+B14+1-E13+(JOURSEM(A13)>2)*(E13="")*(1-E12))

En W15, on pourrait faire avec MAX(W8:W14) mais comme je cherche à trouver une formule indépendante, pour pouvoir l'utiliser toute seule, j'ai mis une formule matricielle utilisant la même forme que la dernière formule ci-dessus, mais utilisant des plages, par exemple B9:B14 au lieu de B14.

En C15, j'ai mis la même formule qu'en W15, transformée en test de la forme SI(formule en W15 <35/24;"Repos hebdo !";"") avec quelques $ en plus pour pouvoir copier facilement cette formule en J15, R15 et en dessous en lignes 25, 35 et 45.


@ plus

 

[May]

Re : Petite formule à trouver

Bonjour à vous,

Victor21: merci de votre temps, c'était juste pour m'en assurer

Cisco: ça a l'air fou ce que t'as fait. J'aurai besoin d'un peu de temps pour comprendre tout ça. Si j'ai un souci, je reviendrai sûrement vers toi pour d'autres explications ou questions que j'aurai entre temps. Bien-sûr, si tu as le temps.

Un grand merci. Je n'aurai rien pu proposer sans votre aide =)

Bon dimanche à vous.

May.

 

[Victor21]

Re : Petite formule à trouver

Re,

... On ne doit pas avoir de journée du type travail le matin, repos l'après midi pour que cela fonctionne.

Ce n'est pas un problème car facilement contournable : début et fin de la 2° vacation égale à la fin de la 1°

 

[CISCO]

Re : Petite formule à trouver

Bonjour

Beaucoup, beaucoup plus simple que ma dernière proposition en pièce jointe.

Dans mon dernier mail, j'avais écrit qu'il ne fallait pas faire les calculs pour les lignes vides.

Comme on veut mettre en évidence la présence ou l'absence d'au moins une période égale à ou de plus de 35 h de repos, ces périodes sont forcément à "cheval" sur une ligne vide. Par conséquent, au contraire, il faut utiliser ces lignes vides pour mettre en évidence ces périodes de 35 h ou plus de repos. Cf. les formules en colonne W, beaucoup plus simples, et plus particulièrement la formule en W15.

Pb : Pour que cela fonctionne, il faut mettre en E7, L7, S7, E17, L17 etc un 1, utile lorsque le lundi est un jour de repos. Or, une de tes MFC utilise un 0 dans ces cellules E7, L7... Résultat, cette MFC renvoie maintenant une mauvaise couleur en B8, I8, P8, B18 etc certaines fois. Je suis allé faire un tour dans tes MFC et ai vu que là aussi, tu en as mis beaucoup, beaucoup, ce qui explique que parfois ton fichier rame. Comme écrit dans l'autre fil, il faut essayer de regrouper les MFC... Ici, cette MFC =(NB(B8;E7)=2)*((1+B8)-E7<11/24) (par rapport au temps de repos de 11 h min) est-elle indispensable le lundi ? Ne faut-il pas uniquement quelque chose du genre =(NB(B8;E7)=2)*((1+B8)-E4<11/24) utilisée sur les lignes 8, 18, 28... ?

@ plus