Précisions formule "complexe"

Bonjour,

Je me permets de relancer le sujet pour un complément d'informations concernant une formule. Ma précédente demande a abouti concernant le retrait d'un quart de pause au delà d'un certain nombre d'heures de prestation. (cellule B3)

1. Toutefois, tout en gardant en substance la formule qui est parfaite (en B3). Je souhaiterais que les veilleurs de nuit, le calcul ne soit pas négatif (-13h ...) (cellule B6)

Cela est-il possible ?

2. Egalement, je souhaiterais qu'une valeur définie apparaisse en dessous de VA (vacances annuelles) lorsque je l'indique dans la cellule.

Cette valeur est égale à 07:36h mais diffère si la personne est en mi-temps, cette valeur serait alors de 3:48h ou de 05:00 si la personne est en 3/4 temps. Sachant que pour compléter le tout ... Cette partie de formule (si c'est possible) soit aussi imbriquée dans la formule générale (demandée au dessus avec le mix entre la formule en B3 et B6)

Il y a d'autres codes tels que CM (certificat médical), F (Férié), RF (Récup' férié). Il sont tous dans le fichier joint.

3. Enfin, concernant le CM (certificat médical), il s'agit d'un code que j'ajoute en cours de mois, donc sur un horaire qui est déjà fait et existant. Si j'indique CM, j'ai un problème "valeur" qui apparaît. La valeur du CM prend en réalité la valeur de la durée de la prestation initiale. Donc si dès lors que la prestation était de 08:00 et que j'indique CM en cours de mois, je souhaiterais simplement que la valeur 08:00 reste bien indiquée. Si la prestation était entre 07h et 10h, alors le calcul serait de 3h si j'indique CM ce jour là. Est-ce possible ?

En substance, je n'aurais dès lors qu'une seule et même formule complexe pour tout le monde, mais générale au moins.

Qlqn pour aider ?

D'avance merci !

NB : Sorry pour le "double post".

Salut Olisbos,

1) Pour avoir un résultat positif, fait une valeur absolu.

=ABS(SI(ET(B5="";C5="");"";SI((C5*24)-(B5*24)>6;(((C5*24)-(B5*24))-0,25)/24;C5-B5)))

2) Je n'ai pas trop compris, où définis-tu cette valeur ?

Bonjour,

Je vous ai corrigé votre formule pour votre 1ère demande dans votre fichier. Il suffit de garder la même idée mais si la valeur en C est inférieure à la valeur en B, le temps total de travail est la différence entre 24h et 21h + la différence entre 8h et 0h soit 11h dans votre exemple.

Je ne comprends pas vos autres demandes :

Demande 2 : où est-ce qu'il est précisé que la personne est en mi-temps ou autre?

Demande 3 : je ne comprends pas quelle valeur il faut récupérer. Pour la case B21, vous voulez la valeur de B3? C'est compliqué sans voir le fichier dans sa globalité...

Je reste dispo pour vous aider

Cindy

Merci Baboutz !

Toutefois, comment faire pour ne pas avoir un problème "#valeur" si je ne mets rien dans la cellule B7 et C7 par exemple (et partout au final, car je vais étendre la formule) ?

Merci Cindy ! Je me rends compte que j'ai demandé une chose un peu trop compliquée pour le distinguo entre les veilleurs de nuit et les éducateurs de jour.

Je m'explique :

Un travailleur de jour doit avoir 15 minutes de pause pour toute prestation de plus de 6 heures. Dès lors, le travailleur aura de facto 15 minutes déduit pour toute prestation excédent 6h. (Ex. : 06:00 - 14:15. => 15 minutes de pause obligatoire, mais déduite donc un total de 08:00 de prestation).

Le veilleur de nuit n'a pas cette caractéristique. Il n'a pas de quart d'heure de pause, quelles que soient ses prestations. Il n'y a donc rien à déduire. Le temps de leur prestation est toujours égal entre l'heure de début et celle de fin.

Donc la formule actuelle des veilleurs de nuit est bonne et l'adaptation proposée par Baboutz pour ceux de jour est juste parfaite.

Maintenant, concernant mes deux autres demandes :

1/

Il n'est précisé nulle part que la personne est en temps plein, ½ temps ou encore ¾ temps. Toutefois, il est tout à fait envisageable d'ajouter une colonne pour y indiquer le régime hebdomadaire du travailleur et ainsi adapter la formule. Le régime temps plein est de 38h/sem réparties sur 5 jours.

Concrètement les codes sont les suivants :

CM : Certificat médical --> voir demande 3 (plus bas)

VA : Vacances --> donc 07:36 si temps plein / la moitié de 07:36 si mi-temps / les ¾ de 07:36 si ¾ temps, etc.

VAS : Vacances supp' --> Idem

VJ : Vacances jeune --> Idem

F : Férié --> Idem

RF : Récup' Férié --> Idem

La durée des codes ci-dessus varie donc en fonction du régime du travailleur.

Les codes ci-dessous, en revanche, ont des valeurs définies fixes.

Le code CP (congé parental) donne lieu à une valeur définie de 0 (donc rien, donc case vide)

Le code VSS (vacances sans solde) : idem que CP

Le code SV (séjour vacances) donne toujours lieu à 11h de prestation

Le code RSV (récup' séjour vacances) donne toujours lieu à 08h20 de prestation

En gros et par exemple, je souhaiterais voir apparaître comme durée 07:36 si j'indique VA à qlqn qui est en temps plein. La moitié s'il est à mi-temps, etc.

3/ enfin donc, concernant le code CM, il est ajouté au cours du mois, une fois que l'horaire est déjà établi.

Par exemple, le début d'une prestation originelle est à 10:00 (B23) et la fin est à 18:15 (C23), donc 08:00 de prestation comptabilisée. Si le travailleur tombe malade, j'indique CM en B23 et je souhaiterais que d'office il soit indiqué 08:00 en BC24. Le code CM reprendrait simplement le résultat initial.

Je joins le fichier original pour plus de facilité. Mes demandes sont peut-être impossibles, mais si au contraire elles le sont je vous en remercie par avance !

14horaire-modele-1.xlsx (184.88 Ko)

" Toutefois, comment faire pour ne pas avoir un problème "#valeur" si je ne mets rien dans la cellule B7 et C7 par exemple (et partout au final, car je vais étendre la formule) ? "

Grosso modo, si pas de valeurs de début ni de fin, pas de raison que la formule s'applique. La case peut rester vierge. Rajouter la formule ABS engendre l'erreur visiblement.

=SI(ET(B9="";C9="");"";SI((C9*24)-(B9*24)>6;(((C9*24)-(B9*24))-0,25)/24;C9-B9)) --> pas d'erreur valeur

=ABS(SI(ET(B9="";C9="");"";SI((C9*24)-(B9*24)>6;(((C9*24)-(B9*24))-0,25)/24;C9-B9))) --> erreur valeur ...

EDIT:

De plus, la formule ABS donne une valeur positive certes mais si j'indique un 21:00 - 08:00, il indique 13:00 comme durée de plage alors que c'est 11:00 ...

Salut Olisbos,

1/ Inspiré de Cindy, il te suffis juste d'appliquer les bonnes cellules. Normalement c'est bon, dis moi :

=SI(ET(B8="";C8="");"";SI(C8>B8;SI((C8*24)-(B8*24)>6;(((C8*24)-(B8*24)))/24;C8-B8);SI((C8*24)+24-(B8*24)>6;(((C8*24)+24-(B8*24)))/24;C8+24-B8)))

2/ Je préfère y aller pas à pas donc j'ai juste réaliser une formule sans prendre en compte le CM ainsi que le mi ou trois-quart temps. Ai-je bien compris ? Est-ce ce que tu attendais ?

=SI(OU(B11="VA";B11="VAS";B11="VJ";B11="F";B11="RF");"07:36";SI(OU(B11="CP";B11="VSS");"";SI(B11="SV";"11:00";SI(B11="RSV";"08:20";""))))

Hello Baboutz,

Avoir une formule similaire pour le personnel de jour et pour le personnel de nuit n'est pas possible comme je l'expliquais. En effet, un quart d'heure de pause est déduit pour les prestations de plus de 06h pour le personnel de jour, mais cela ne s'applique pas au personnel de nuit. De plus, il arrive que le personnel de jour preste exceptionnellement la nuit.

La formule que vous m'avez proposée en 1. fonctionne mais ne prend plus en compte la formule qui déduit le 1/4 d'heure de pause ...

=SI(ET(D7="";E7="");"";SI((E7*24)-(D7*24)>6;(((E7*24)-(D7*24))-0,25)/24;E7-D7)) (Ma formule initiale)

En soi, ce n'est rien. Je pense qu'il n'est pas possible d'avoir un mix du tout.

Concernant la réponse 2.,

Si j'indique en B13 :

(=SI(OU(B12="VA";B12="VAS";B12="VJ";B12="F";B12="RF");"07:36";SI(OU(B12="CP";B12="VSS");"";SI(B12="SV";"11:00";SI(B12="RSV";"08:20";""))))

et que je mets par exemple VA en B12, cela fonctionne, cela indique donc 07:36.

Toutefois, la formule part du principe que c'est un temps plein et la formule ne reprend plus la formule du haut, celle-là :

=SI(OU(B12="VA";B12="VAS";B12="VJ";B12="F";B12="RF");"07:36";SI(OU(B12="CP";B12="VSS");"";SI(B12="SV";"11:00";SI(B12="RSV";"08:20";""))))

En gros, il faudrait un mix de :

=SI(OU(B12="VA";B12="VAS";B12="VJ";B12="F";B12="RF");"07:36";SI(OU(B12="CP";B12="VSS");"";SI(B12="SV";"11:00";SI(B12="RSV";"08:20";""))))

et de

=SI(ET(D7="";E7="");"";SI((E7*24)-(D7*24)>6;(((E7*24)-(D7*24))-0,25)/24;E7-D7))

et l'incorporation d'une proratisation en fonction du régime horaire.

Concernant le CM, voyons cela après alors

Pour la 1) :

En gros tu veux que si on est dans la partie veilleur de nuit, la formule n'enlève pas le 1/4h de pause mais si on est dans le reste (Equipe éducative, activités, hébergement, jobistes, pôle logistique, cuisine, prestataires extérieurs, personnel écarté) la formule enlève bel et bien le 1/4h de pause UNIQUEMENT s'il n'a pas travaillé de nuit ?

Si c'est ça c'est peut être faisable, ça met du challenge j'aime ça ahah !

Bon, j'ai bien avancé.

J'ai quasiment réussi ton 1). Voici le code :

=SI(ET(B5="";C5="");"";SI(C5>B5;SI((C5*24)-(B5*24)>6;(((C5*24)-(B5*24))-0,25)/24;C5-B5);SI((C5*24)+24-(B5*24)>6;(((C5*24)+24-(B5*24)))/24;C5+24-B5)))

Seul hic : la formule "repère" que le type travail de nuit quand son horaire est plus petite que son horaire de départ. C'est à dire que pour par exemple :

- 21h00-04h00 -> Il n'y a pas de soucis

- 01h00-08h00 -> Ne va pas détecter la nuit, et va donc retirer 15min de pause

- 16h00-23h00 -> Ne va pas détecter la nuit, et va donc retirer 15min de pause

J'ai réussi ton 2). Code, attention les yeux :

=SI(OU(B11="VA";B11="VAS";B11="VJ";B11="F";B11="RF");"07:36";SI(OU(B11="CP";B11="VSS");"";SI(B11="SV";"11:00";SI(B11="RSV";"08:20";SI(ET(OU(B11="VA";B11="VAS";B11="VJ";B11="F";B11="RF";B11="CP";B11="VSS";B11="RSV";B11="SV";B11="CM");C11="");"";SI(C11>B11;SI((C11*24)-(B11*24)>6;(((C11*24)-(B11*24))-0,25)/24;C11-B11);SI((C11*24)+24-(B11*24)>6;(((C11*24)+24-(B11*24)))/24;C11+24-B11)))))))

Evidemment le résultat est biaisé car 1) pas totalement juste. Mais si le 1) est résolu alors le 2) le sera.

Pour réaliser le 1), il faudrait que tu me donnes l'heure à partir de laquelle on considère que le type commence à travailler de nuit ainsi que l'heure à laquelle on considère qu'il recommence à travailler de jour. Je me disais, 21h début de nuit et 8h fin de nuit ?

Au niveau du CM, si j'ai bien compris, tu aimerais que dès que tu tapes CM, le nombre d'heure déjà présents reste ?

CM = Congé Maladie, donc je n'ai pas très bien compris, un type ne peut pas être en congé maladie et avoir fait ses 8h dans la journée, si ? Pour moi ça serait plutôt quelque chose que tu rajoutes sur les autres jours et qui t'afficheras 07:36 par exemple

Waw ! Aussitôt dit, aussitôt fait !

Bizarrement la formule de la 1) ne fonctionne pas correctement, car elle ne me déduit pas le fameux quart d'heure de pause.

Toutefois, la formule de la deux fonctionne bien visiblement, ce qui en soi est le principal !

Pour répondre à tes interrogations :

"Pour réaliser le 1), il faudrait que tu me donnes l'heure à partir de laquelle on considère que le type commence à travailler de nuit ainsi que l'heure à laquelle on considère qu'il recommence à travailler de jour. Je me disais, 21h début de nuit et 8h fin de nuit ?"

==> Je n'ai que 4 types de prestations en nuit qui sont quasi exclusivement effectuées par les veilleurs de nuit. Toutefois, lorsque cela s'avère nécessaire, un éducateur de jour peut de temps à autre passer en nuit pour les besoins du service.

Ces 4 horaires de nuit sont :

Comme je te le disais, je pense qu'il serait plus pratique d'avoir une formule pour ceux de jour et une autre pour ceux de nuit. D'autant plus que les valeurs des VA, VAS et autres diffèrent pour ceux de nuit, ce n'est pas 07:36.

En effet, un jour de VA pour un travailleur de jour est toujours de 07:36 s'il est en temps plein, de moitié s'il est à mi-temps, etc.

Pour un travailleur de nuit, la valeur du VA prend la valeur des nuits habituelles qu'il fait (mais rien n'est jamais habituel ), à savoir 08:00, 09:00, 10:00 ou 11:00.

Ça devient vraiment un micmac quant aux différentes conditions. Les veilleurs sont vraiment un régime à part au final. En d'autres termes, je pense que la formule actuelle des veilleurs peut rester comme telle.

Ce qui m'importe le plus ce sont tous les personnels de jour, et ta formule semble plutôt cool !!!

Je te joins l'horaire de mars pour que tu aies une idée à quoi ressemble l'horaire.

Lorsque j'applique ta formule à une cellule et que la cellule d'heure de début et d'heure de fin est vide (un jour de repos donc), formule calcule un 00:00. En soi, c'est vrai c'est zéro heure. Est-il possible que rien n'apparaisse au lieu d'avoir ce 00:00 ?

"Au niveau du CM, si j'ai bien compris, tu aimerais que dès que tu tapes CM, le nombre d'heures déjà présentes reste ?

CM = Congé Maladie, donc je n'ai pas très bien compris, un type ne peut pas être en congé maladie et avoir fait ses 8h dans la journée, si ? Pour moi ça serait plutôt quelque chose que tu rajoutes sur les autres jours et qui t'afficheras 07:36 par exemple "

Tu peux voir que le début de la prestation est à 07:00, que la fin est à 12:00 et que donc, la durée de la prestation est de 05:00. C'est ainsi que le travailleur voit son horaire un mois à l'avance. Si maintenant le travailleur tombe malade (CM), je souhaiterais simplement indiquer CM à la place du 07:00 et qu'automatiquement le résultat à savoir 05:00 de prestation reste simplement. Actuellement, si j'indique CM, il y a un problème #valeur qui apparaît. Les heures comptabilisées pour un CM sont celles qui auraient naturellement dû être prestées et qui étaient à l'horaire initialement.

Cela ne me pose absolument aucun problème de rajouter une colonne avec le régime de travail du travailleur, comme tu l'as fait. Cependant, il va falloir que la formule que tu as faite renvoie à cette colonne pour proratiser la valeur des différents codes, non ?

Ceux-là en fait :

  • VA : Vacances --> donc 07:36 si temps plein / la moitié de 07:36 si mi-temps / les ¾ de 07:36 si ¾ temps, etc.
    VAS : Vacances supp' --> Idem
    VJ : Vacances jeune --> Idem
    F : Férié --> Idem
    RF : Récup' Férié --> Idem

Je rappelle que les SV et RSV sont toujours des valeurs fixes, qui ne changent pas en fonction du régime horaire.

NB : J'ai parfois des contrats à 15h par semaine (autrement dit du 0,39 temps plein ...). Je n'ai donc pas que des temps plein, mi-temps et ¾ temps. Comment devrais-je l'indiquer dans la nouvelle colonne alors ?

Une fois encore, MERCI MERCI MERCI !

Bonsoir,

Pas de soucis, c'est avec plaisir !

Je dois t'annoncer... qu'il me semble avoir réussi ! Et en une seule formule !

Enfin... Il me manque juste le CM. Je n'ai pas réussi à gérer ce cas, qui peut sembler pourtant basique, mais à partir du moment où on modifie la cellule du début de l'horaire par CM, forcément on perd le résultat qui s'y trouvait avant et donc je n'ai pas encore trouvé un moyen pour gérer ça. Je vais chercher mais je préfère te prévenir qu'il faudra peut-être rentrer ce résultat à la main.

Sinon, tout y est ! Les 4 types d'horaires de nuits, le régime horaire, les types de jours de congés...

Normalement c'est bon, j'espère ne pas m'être trompé.

Voilà la formule :

=SI(ET(B11="";C11="");"";SI(ET(OU(B11="VA";B11="VAS";B11="VJ";B11="F";B11="RF");C11="07:59"*1);"08:00"*$A$12;SI(ET(OU(B11="VA";B11="VAS";B11="VJ";B11="F";B11="RF");C11="05:00"*1);"09:00"*$A$12;SI(ET(OU(B11="VA";B11="VAS";B11="VJ";B11="F";B11="RF");C11="07:00"*1);"10:00"*$A$12;SI(ET(OU(B11="VA";B11="VAS";B11="VJ";B11="F";B11="RF");C11="08:00"*1);"11:00"*$A$12;SI(OU(B11="VA";B11="VAS";B11="VJ";B11="F";B11="RF");"07:36"*$A$12;SI(OU(B11="CP";B11="VSS");"";SI(B11="SV";"11:00";SI(B11="CM";"";SI(B11="RSV";"08:20";SI(ET(B11="23:59"*1;C11="07:59"*1);"08:00"*$A$12;SI(ET(B11="20:00"*1;C11="05:00"*1);"09:00"*$A$12;SI(ET(B11="21:00"*1;C11="07:00"*1);"10:00"*$A$12;SI(ET(B11="21:00"*1;C11="08:00"*1);"11:00"*$A$12;SI((C11*24)-(B11*24)>6;(((C11*24)-(B11*24))-0,25)/24;C11-B11)))))))))))))))

$A$12 représente le régime horaire, à remplacer par la cellule correspondante dans le tableau.

Rassure moi, ça marche ? C'est bien ce que tu attendais ? Tu aimes ?

On peut améliorer cette formule, je te dirais demain comment et je rajouterai quelques informations.

Pour le moment, pour moi c'est dodo

Bonne nuit et à demain !

Ooooh la jolie coquille

Je suis tellement distrait que ça m'aurait étonné si j'avais réussi du premier coup !

J'ai oublié de prendre un compte le régime horaire pour le calcul d'une journée lambda.

Mais c'est chose faite ! Voici la formule corrigée :

=SI(ET(C14="";D14="");"";SI(ET(OU(C14="VA";C14="VAS";C14="VJ";C14="F";C14="RF");D14="07:59"*1);"08:00"*$B$15;SI(ET(OU(C14="VA";C14="VAS";C14="VJ";C14="F";C14="RF");D14="05:00"*1);"09:00"*$B$15;SI(ET(OU(C14="VA";C14="VAS";C14="VJ";C14="F";C14="RF");D14="07:00"*1);"10:00"*$B$15;SI(ET(OU(C14="VA";C14="VAS";C14="VJ";C14="F";C14="RF");D14="08:00"*1);"11:00"*$B$15;SI(OU(C14="VA";C14="VAS";C14="VJ";C14="F";C14="RF");"07:36"*$B$15;SI(OU(C14="CP";C14="VSS");"";SI(C14="SV";"11:00";SI(C14="CM";"";SI(C14="RSV";"08:20";SI(ET(C14="23:59"*1;D14="07:59"*1);"08:00"*$B$15;SI(ET(C14="20:00"*1;D14="05:00"*1);"09:00"*$B$15;SI(ET(C14="21:00"*1;D14="07:00"*1);"10:00"*$B$15;SI(ET(C14="21:00"*1;D14="08:00"*1);"11:00"*$B$15;SI((D14*24)-(C14*24)>6;((((D14*24)-(C14*24))-0,25)/24)*$B$15;(D14-C14)*$B$15)))))))))))))))

Il y a un problème dans la première ligne. Il y a un MJ ! J'ai vérifié et tu m'en as jamais parlé. Un oubli ? Dois-je le rajouter ?

Il faut que je t'explique comment j'ai fait pour gérer le cas "nuit". En fait pour cela, je regarde l'horaire de début et l'horaire de fin, ainsi s'il commence à 20h/21h/23h59 et qu'il termine à 5h/7h/8h/7h59; je sais qu'il effectue un travail de nuit et donc ne lui retire pas 15min de pause. Dans le cas "nuit +VA/VSS/ou autre", je pars du principe que son horaire est marqué et que donc lorsque tu vas marquer le statut sur l'horaire de début, grâce à l'horaire de fin, je saurais quel est son type d'horaire de nuit. Il ne faut donc pas supprimer l'horaire de fin.

Je ne sais pas si j'ai été très clair mais j'espère que tu m'auras compris.

Cela devient plus compliqué si un nouvel horaire de nuit est instauré car il faudra le rajouter dans la formule.

Si tu commences à rentrer un horaire de début et qu'il n'y a pas encore d'horaire de fin, ça indique un horaire total complètement faux. C'est du pinaillage, mais je peux améliorer la formule pour empêcher ça ahah

Au niveau du fichier en lui-même, c'est un planning mensuel c'est ça ? Si oui, pourquoi autant de semaines ?

Si c'est le cas, j'imagine que tu copies ce fichier Excel chaque mois et que tu le modifies en fonction du nouveau mois.

Je te conseillerai d'avoir un fichier vierge de base, avec déjà toute les formules rentrées dans les bonnes cellules.

On pourrait faire une macro en VBA (Langage propre à Microsoft et en particulier à Excel) qui copie/colle ce nouveau fichier automatiquement, et également une macro pour ajouter/supprimer un poste.

Cela permettrait de t'éviter qu'à chaque fois tu t'amuses à re-rentrer la formule en changeant les cellules pour que ça colle, ce qui est long et rébarbatif.

VBA permettrai également de mettre à jour la formule automatiquement si un nouvel horaire de nuit est instauré !

Voilà, je pense avoir tout dit !

Hello !

Super super !

En effet, j'ai oublié ML (Maladie longue durée). Cela équivaut à zéro heure, on est en dehors des heures de salaire garanti. J'ai pu l'implémenter sans souci dans ta formule.

Je pense qu'on peut éviter le pinaillage (en tout cas pour le moment). Je ne vois pas d'intérêt de mettre un horaire de début sans horaire de fin de toute façon.

Pas de souci pour la coquille, j'ai pu adapter. L'essence de la formule étant là, j'ai pu me débrouiller pour la mettre en relation avec la cellule du régime horaire.

En fait, je crée un modèle de document (comme tu l'as proposé) et chaque mois j'adapte simplement les dates et éventuellement l'un ou l'autre nom si il y a un engagement ou une fin de contrat. J'y ai inscrit 6 semaines car potentiellement, parfois, un mois s'étale sur 6 semaines.

Concernant la macro en VBA, je t'avoue que là je suis perdu

Merci !!!

Au plaisir ! J'ai sué, mais j'ai apprécié

D'accord !

Je te remet la formule complète au cas où, on ne sait jamais, avec le ML que j'ai mis au même niveau que CP et VSS, c'est à dire = 0

=SI(ET(C12="";D12="");"";SI(ET(OU(C12="VA";C12="VAS";C12="VJ";C12="F";C12="RF");D12="07:59"*1);"08:00"*$B$13;SI(ET(OU(C12="VA";C12="VAS";C12="VJ";C12="F";C12="RF");D12="05:00"*1);"09:00"*$B$13;SI(ET(OU(C12="VA";C12="VAS";C12="VJ";C12="F";C12="RF");D12="07:00"*1);"10:00"*$B$13;SI(ET(OU(C12="VA";C12="VAS";C12="VJ";C12="F";C12="RF");D12="08:00"*1);"11:00"*$B$13;SI(OU(C12="VA";C12="VAS";C12="VJ";C12="F";C12="RF");"07:36"*$B$13;SI(OU(C12="CP";C12="VSS";C12="ML");"";SI(C12="SV";"11:00";SI(C12="CM";"";SI(C12="RSV";"08:20";SI(ET(C12="23:59"*1;D12="07:59"*1);"08:00"*$B$13;SI(ET(C12="20:00"*1;D12="05:00"*1);"09:00"*$B$13;SI(ET(C12="21:00"*1;D12="07:00"*1);"10:00"*$B$13;SI(ET(C12="21:00"*1;D12="08:00"*1);"11:00"*$B$13;SI((D12*24)-(C12*24)>6;((((D12*24)-(C12*24))-0,25)/24)*$B$13;(D12-C12)*$B$13)))))))))))))))

J'ai rajouté des infos pendant que tu envoyais ce message, sur comment j'ai fait pour le cas des nuits, je t'invite à aller voir mon message précédent.

Pour le VBA c'est comme tu veux, c'est un plus qui peut faire gagner du temps, je peux aider si besoin mais si tu es satisfait et que tu penses que ça ira comme tel alors on peut s'arrêter là !

En tout cas, n'hésite pas !

Bonne journée

PS :

Je pense qu'à ce niveau là, c'est déjà très très bien !

Si à l'occase, le dernier petit point concernant le CM est envisageable, ce serait top.

Quoiqu'il en soit, UN TOUT GRAND MERCI ! ! !

J'arrive un peu tard ...

Pour moi une formule aussi longue mériterait de la repenser entièrement, en s'appuyant sur un onglet paramètres et un tableau. Ce qui serait du reste un plus pour la maintenance de la formule.

Mais si tu es satisfait, c'est parfait.

Je reste preneur de toute solution facilitante

Je vais y réfléchir, mais je n'ai pas comme les autres contributeurs acquis les mécanismes du calcul que tu souhaites ! je regarderai quand même dans le principe même si je fais une erreur au final.

Salut Steelson,

Olisbos souhaite à partir d'un fichier excel, effectuer le calcul du nombre d'heure effectuées par une personne sur une journée.

Il faut savoir plusieurs choses :

  • Dès qu'une journée de travail excède 6 heures, on attribue 15 min de pause au travailleur, qu'il faut soustraire donc.
  • Il y a des travailleurs de nuit qui n'ont pas de pause même s'ils travaillent plus de 6 heures, avec différents horaires :
    20:00/05:00
  • 21:00/07:00
  • 21:00/08:00
  • 23:59/07:59
  • Tous les travailleurs n'ont pas le même régime horaire (plein temps, mi-temps, 3/4 temps, 0,39 temps etc...)
  • Enfin, s'ajoute la possibilité de différents statuts tels que VA pour vacances, CP pour congé paternité, CM certificat médical :
    VA -> Vacances Annuelles.
    [list]Affiche automatiquement "07:36"
  • Si travail de nuit, affiche l'horaire plein (8h/9h/10h/11h) en fonction de son horaire
  • Est impacté par le régime horaire
  • VAS -> Vacances Supplémentaires.
    Affiche automatiquement "07:36"
  • Si travail de nuit, affiche l'horaire plein (8h/9h/10h/11h) en fonction de son horaire
  • Est impacté par le régime horaire
  • VJ -> Vacances Jeunes.
    Affiche automatiquement "07:36"
  • Si travail de nuit, affiche l'horaire plein (8h/9h/10h/11h) en fonction de son horaire
  • Est impacté par le régime horaire
  • F -> Férié.
    Affiche automatiquement "07:36"
  • Si travail de nuit, affiche l'horaire plein (8h/9h/10h/11h) en fonction de son horaire
  • Est impacté par le régime horaire
  • RF -> Récupération Férié.
    Affiche automatiquement "07:36"
  • Si travail de nuit, affiche l'horaire plein (8h/9h/10h/11h) en fonction de son horaire
  • Est impacté par le régime horaire
  • CP -> Congé Parental.
    Affiche automatiquement "00:00"
  • VSS -> Vacances Sans Soldes.
    Affiche automatiquement "00:00"
  • ML -> Maladies Longue durée.
    Affiche automatiquement "00:00"
  • SV -> Séjour Vacances.
    Affiche automatiquement "11:00"
  • N'est pas impacté par le régime horaire
  • RSV -> Récupération Séjour Vacances.
    Affiche automatiquement "08:20"
  • N'est pas impacté par le régime horaire
  • CM -> Congé Maladie.
    Affiche l'horaire qu'aurait du faire le travailleur (Par exemple, horaire 08:00-17:00, affiche 08:45
  • Est impacté par le régime horaire
[/list]

Voici le fichier Excel de base joint par Olisbos :

22horaire-modele-1.xlsx (184.88 Ko)

Et voici le fichier avec la formule ainsi que des exemples :

Le seul soucis qu'il y a avec la formule, c'est que je n'ai pas réussi à prendre en charge le cas "CM".

Voilà c'est à peu près tout, bonne chance !

Beau travail de Baboutz !!

Au passage, on peut remplacer les $B$n par $Bn ...

En prenant (au hasard) la cellule K13, j'obtiens

=SI(ET(K12="";L12="");"";
SIERREUR(EQUIV("*"&K12&"*";_param!$A$2;0)*INDEX(_param!$B$2:$E$2;EQUIV(L2;_param!$B$1:$E$1;0))*$B13;
SIERREUR(RECHERCHEV(K12;_T1;2;0)*SI(RECHERCHEV(K12;_T1;3;0);$B13;1);
SI(ET(K12="23:59"*1;L12="07:59"*1);"08:00"*$B13;
SI(ET(K12="20:00"*1;L12="05:00"*1);"09:00"*$B13;
SI(ET(K12="21:00"*1;L12="07:00"*1);"10:00"*$B13;
SI(ET(K12="21:00"*1;L12="08:00"*1);"11:00"*$B13;
SI((L12*24)-(K12*24)>6;((((L12*24)-(K12*24))-0,25)/24)*$B13;(L12-K12)*$B13)
)))))))

au lieu de

=SI(ET(K12="";L12="");"";
SI(ET(OU(K12="VA";K12="VAS";K12="VJ";K12="F";K12="RF");L12="07:59"*1);"08:00"*$B$13;
SI(ET(OU(K12="VA";K12="VAS";K12="VJ";K12="F";K12="RF");L12="05:00"*1);"09:00"*$B$13;
SI(ET(OU(K12="VA";K12="VAS";K12="VJ";K12="F";K12="RF");L12="07:00"*1);"10:00"*$B$13;
SI(ET(OU(K12="VA";K12="VAS";K12="VJ";K12="F";K12="RF");L12="08:00"*1);"11:00"*$B$13;
SI(OU(K12="VA";K12="VAS";K12="VJ";K12="F";K12="RF");"07:36"*$B$13;
SI(OU(K12="CP";K12="VSS";K12="ML");"";
SI(K12="SV";"11:00";
SI(K12="CM";"";
SI(K12="RSV";"08:20";
SI(ET(K12="23:59"*1;L12="07:59"*1);"08:00"*$B$13;
SI(ET(K12="20:00"*1;L12="05:00"*1);"09:00"*$B$13;
SI(ET(K12="21:00"*1;L12="07:00"*1);"10:00"*$B$13;
SI(ET(K12="21:00"*1;L12="08:00"*1);"11:00"*$B$13;
SI((L12*24)-(K12*24)>6;((((L12*24)-(K12*24))-0,25)/24)*$B$13;(L12-K12)*$B$13)
))))))))))))))

en mettant en place 2 tableaux comme suit

capture d ecran 711

j'ai donc remplacé

SI(ET(OU(K12="VA";K12="VAS";K12="VJ";K12="F";K12="RF");L12="07:59"*1);"08:00"*$B$13;
SI(ET(OU(K12="VA";K12="VAS";K12="VJ";K12="F";K12="RF");L12="05:00"*1);"09:00"*$B$13;
SI(ET(OU(K12="VA";K12="VAS";K12="VJ";K12="F";K12="RF");L12="07:00"*1);"10:00"*$B$13;
SI(ET(OU(K12="VA";K12="VAS";K12="VJ";K12="F";K12="RF");L12="08:00"*1);"11:00"*$B$13;

par

SIERREUR(EQUIV("*"&K12&"*";_param!$A$2;0)*INDEX(_param!$B$2:$E$2;EQUIV(L2;_param!$B$1:$E$1;0))*$B13;

et remplacé

SI(OU(K12="VA";K12="VAS";K12="VJ";K12="F";K12="RF");"07:36"*$B$13;
SI(OU(K12="CP";K12="VSS";K12="ML");"";
SI(K12="SV";"11:00";
SI(K12="CM";"";
SI(K12="RSV";"08:20";

par

SIERREUR(RECHERCHEV(K12;_T1;2;0)*SI(RECHERCHEV(K12;_T1;3;0);$B13;1);

Il resterait à simplifier la fin de la formule (calcul d'heures proprement dite).

Bon, ce n'est pas merveilleux je l'avoue, car en plus cela limite un peu la lisibilité/compréhension de la formule ! le seul avantage est d'avoir un tableau de paramètres séparé de la formule.

Dans le fichier joint, je n'ai appliqué qu'à K13.

Rechercher des sujets similaires à "precisions formule complexe"