Calcul d'une durée en fonction des jours feries

[Raphaël]

Bonjour,

Depuis quelques mois je suis ce forum, j'y apprend beaucoup a ma vitesse, MERCI à tous les participants.

L'un d'entre vous pourrait-il m'expliquer comment calculer le nombre d'heures (case heures feries) en fonction de la date feriée (jour ferie ne comprenant pas les dimanches mais seulement les jours comme le 01/05/03) (cf classeur ci joint), si possible avec la même formule que se soit pour le travailleur de nuit ou celui de jour.

Merci de votre aide

PS : Le jour ferie est definit par une mise en forme conditionnelle du type "=OU(J4=Feries;JOURSEM(J4)=1)"

 

[Monique]

Bonsoir, Raphaël,

Pour les gens qui travaillent de jour :
En B42 :
=SI(SOMMEPROD(NB.SI(B28;Feries))>0;B36;0)
Si la date, en D28, fait partie des jours féries, ça donne B36, c'est-à-dire la totalité des heures travaillées ce jour-là, sinon ça donne 00:00

Pour les gens qui travaillent de nuit :
En B18 ;
=SI(SOMMEPROD(NB.SI(D4;Feries))>0;C10;+SI(SOMMEPROD(NB.SI(B4;Feries))>0;1-B10;0))
Si la date du lendemain, en D4, fait partie des jours féries, ça donne C10, c'est-à-dire le temps travaillé entre minuit et la fin de la nuit, +, si, etc
+ si la date du jour, en B4, fait partie des jours fériés, ça donne (1-B10), c'est-à-dire le temps travaillé avant minuit.

Je vais essayer de te trouver une formule commune jour-nuit.

 

[Monique]

Re,

Voici la suite.
Formule commune, que l'on soit de jour ou de nuit.

En B18 :
=SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B10<C10);B12;SI(ET(SOMMEPROD(NB.SI(D4;Feries))>0;B10>C10);C10;+SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B10>C10);1-B10;0)))

Raphaël, le fichier que tu as mis sur le forum est en lien avec la table des fériés qui est chez toi.

 

[Vériland]

Arf Monique.....Jour.....nuit

Loool

 

[Raphael]

Salut à tous,

Merci Monique pour ton aide.

Tes formules jour et nuit fonctionnent trés bien mais la formule jour/nuit ne fonctionne pas bien. Peux tu m'aider encore un peu ? Ci-joint le fichier avec tes formule collée au bonne place. Une feuille pour la formule jour/nuit et une feuille pour les formules jour et formule nuit.

Oui, ces demandes répétées son pour mon projet de planning.

Merci à tous.

 

[Monique]

Bonsoir,

Raphaël, la formule que tu as dans ton classeur n'est pas bonne du tout.
Je pense que tu as travaillé de trop, du genre 12 heures de nuit (pas malin) puis 12 heures de jour sans transition (pas malin non plus). Bref...

Il te manque le début de la formule, c'est-à-dire carrément la première condition.
Ta première condition, qui était la deuxième, n'est pas bonne : elle a un B100, sorti de je sais où et qui fausse tout.
Je crois que le > est devenu < ou l'inverse.
Bref, on reprend la formule qui te donne le temps travaillé un jour férié, que tu sois de jour ou de nuit, que le férié soit le jour-même ou le lendemain.

Formule en B18 :
=SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B10<C10);B12;SI(ET(SOMMEPROD(NB.SI(D4;Feries))>0;B10>C10);C10;+SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B10>C10);1-B10;0)))

Première partie de la formule
SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B10<C10);B12;
Si B4, (c'àd le jour-même), est férié et si l'heure de début est inférieure à l'heure de fin (c'àd si tu travailles de jour)
la formule inscrit B12, c'àd la durée totale de la journée

Deuxième partie de la formule
SI(ET(SOMMEPROD(NB.SI(D4;Feries))>0;B10>C10);C10;
Si D4 (c'àd le lendemain) est férié et si l'heure de début est supérieure à l'heure de fin (c'ad si tu travailles de nuit)
la formule inscrit C10, donc l'heure de fin, c'est-à-dire le temps travaillé après minuit
(si le lendemain n'est pas férié, résultat = 0)

Troisième partie de la formule
+SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B10>C10);1-B10;0)))
Si B4 (c'àd le jour-même) est férié et si l'heure de début est supérieure à l'heure de fin (c'ad si tu travailles de nuit)
la formule inscrit 1-B10, c'ad 24 heures mois l'heure de début,
elle inscrit donc le temps travaillé avant minuit.

Les 2è et 3è parties s'additionnent si le jour-même et le lendemain sont tous les 2 des jours fériés. (tu n'oublies pas le + avant la 3è partie)

 

[Monique]

Re

Non, les conditions 2 et 3 ne s'additionnent pas et il faut intercaller une autre condition entre la 1ère et la 2è.

Au total, ça donne en B18 :
=SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B10<C10);B12;SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;SOMMEPROD(NB.SI(D4;Feries))>0;B10>C10);1-B10+C10;SI(ET(SOMMEPROD(NB.SI(D4;Feries))>0;B10>C10);C10;SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B10>C10);1-B10;0))))

Mais... il manque tout de même quelque chose à ton planning.
Si le lendemain du dernier jour du mois est férié ?

Il faudrait ajouter une ligne (masquée) au-dessus des dates
disant si cette date est ou non la veille d'un férié.
Excel n'accepte pas =SOMMEPROD(NB.SI(B4+1;Feries)) pour savoir si le lendemain de B4 est férié.
En C3, on peut mettre =SI(ESTNUM(B4);B4+1;"")
(formule conditionnelle, au moins à partir du 29 si toutes tes feuilles sont semblables)
En B3 =SOMMEPROD(NB.SI(C3;Feries))

Au lieu de prendre en compte la cellule du lendemain, la formule prendrait en compte la valeur de B3.

Du coup, ça donne en B18 :
=SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B10<C10);B12;SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B3>0;B10>C10);1-B10+C10;SI(ET(B3>0;B10>C10);C10;SI(ET(SOMMEPROD(NB.SI(B4;Feries))>0;B10>C10);1-B10;0))))
Un tout petit peu plus court.

 

[Raphaël]

C'est OK

Merci Beaucoup Monique