Casse tête pour roi des formules ;)

[almas]

Bonjour le forum

on viens de me soulever un probleme dans un des mes classeurs de gestion de personnel

suite à une date de début et une date de fin j 'ai une formule qui déduit les jours fériées et une autre qui calcule les ABS des vendredi après midi 1 semaine sur 2.

Ca marche bien...Mais voila!....il ne faut pas que les jours féries tombe un vendredi 😛 et le 15 aout était un vendredi 🙁

du coup il faudrait que je trouve le moyen d 'intégrer la déduction des jours féries dans la formule du calcul des vendredi après midi
j 'ai testé quelque truc sans succès
j 'espère tomber sur un ROI de la formule qui trouverai une solution

bien sur un petit classeur test pour bien comprendre le problème.....

merci et bonne journée

 

[vgendron]

Re : Casse tête pour roi des formules 😉

Bonjour,

Je vois que dans ta formule tu utilises déjà du Sommeprod et du joursem
mais bon. je ne comprend pas trop la formule et n'ai surtout pas trop le temps de creuser;
cela dit une piste:
à ton calcul, tu retires le nombre d'heures (3h50) si un des jours fériés est un vendredi
=SOMMEPROD((JOURSEM(I7:I17)=6)*1)*TEMPS(3;50;0)

 

[ROGER2327]

Re : Casse tête pour roi des formules 😉

Bonsoir à tous.

Premièrement, simplification de la formule existante :​

=SI(C6=0;E6*cent-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A6&":"&B6))-2)/7)+3/5;52+5/28))+1;2)>0)*(JOURSEM(LIGNE(INDIRECT(A6&":"&B6));2)=5))*(cent-4,25);C6+((E6-1)*cent))

(172 caractères au lieu de 297)

Deuxièmement, une proposition à tester soigneusement (car je ne suis pas certain d'avoir tout compris sur un seul exemple) :​

=SI(C6=0;E6*cent-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A6&":"&B6))-2)/7)+3/5;52+5/28))+1;2)>0)*(JOURSEM(LIGNE(INDIRECT(A6&":"&B6));2)=5))*(1-(NB.JOURS.OUVRES(LIGNE(INDIRECT(A6&":"&B6));LIGNE(INDIRECT(A6&":"&B6));feriés)<>0))*(cent-4,25);C6+((E6-1)*cent))

Bonne soirée.


ℝOGER2327
#7577

Lundi 16 Absolu 142 (Saint Ibicrate le Géomètre, pataphysicien - fête Suprême Quarte)
2 Vendémiaire An CCXXIII, 7,1897h - safran
2014-W39-2T17:15:19Z


P.s. : je n'ai rien d'un roi. En général, j'aurais plutôt tendance à ne pas trop aimer l'espèce...

 

[almas]

Re : Casse tête pour roi des formules 😉

re

merci à tous les 2

Roger la simplification de formule Nikel (comme d'ab^^) et je l 'est transformé pour fonctionner les semaines paire sans problème

par contre l 'essais de formule pour le cas particulier marche partiellement
il règle bien le probleme des vendredi férie mais ne marche plus les vendredi non férié

deplus j 'avais oublié une chose, c 'est pas la conjoncture des 2 événements mais 3 qui créé le problème: un vendredi jour férié et que l 'agent sois de petite semaine ce jour la(donc ne travail pas le vendredi après midi)

j 'ai modifier mon fichier test pour que ça sois plus claire

 

[JBOBO]

Re : Casse tête pour roi des formules 😉

Bonjour,

Un peu moins pro, mais voir si fichier joint repond aux attentes.

 

[almas]

Re : Casse tête pour roi des formules 😉

bonjour jbobo
si je comprend bien tu calcul le n° de semaine des jour fériée ainsi que leur jour et si c 'est un vendredi tu affecte la valeur 3.50

par contre j 'ai pas pue tester ta formule car il me manque des noms? tu fait référence a une insertion de noms?

 

[JBOBO]

Re : Casse tête pour roi des formules 😉

re,

Je n'avais pas vu que tu étais sous 2003, il me semble que la formule NO.SEMAINE n'existe pas sur cette version.
J'essai de voir si je trouve une astuce.

A tester en G6 :

=SI(C6=0;E6*cent-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A6&":"&B6))-2)/7)+3/5;52+5/28))+1;2)>0)*(JOURSEM(LIGNE(INDIRECT(A6&":"&B6));2)=5))*(cent-4.25);C6+((E6-1)*cent))+SIERREUR(RECHERCHEV((B6-("1/1/"&$R$6)*1);$S$7:$U$17;3;0);0)

et en S7, à tirer vers le bas :

=ARRONDI((R7-("1/1/"&$R$6)*1)/7+1;0)

 

[almas]

Re : Casse tête pour roi des formules 😉

No.semaine avais l 'ère de bien marcher pourtant même résultat qu 'avec ta formule arrondi

l autre formule contenait une erreurs (un point au lieu d 'une virgule sur le 4,25), mais même corrigé toujours pareil : "#nom?"

es que ça ne pourrai pas venir du "SIERREUR"? car je vois pas cette fx dans 2003

 

[JBOBO]

Re : Casse tête pour roi des formules 😉

re,

Et oui Sierreur n'existe pas sous 2003, j'aurais du y penser ! donc normalement on doit pouvoir modifier comme ceci (avec NO.SEMAINE et si(esterreur()... ) :

=SI(C6=0;E6*cent-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A6&":"&B6))-2)/7)+3/5;52+5/28))+1;2)>0)*(JOURSEM(LIGNE(INDIRECT(A6&":"&B6));2)=5))*(cent-4.25);C6+((E6-1)*cent))+SI(ESTERREUR(RECHERCHEV(NO.SEMAINE(B6);$S$7:$U$17;3;0));0;RECHERCHEV(NO.SEMAINE(B6);$S$7:$U$17;3;0))

ou encore,

=SI(C6=0;E6*cent-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A6&":"&B6))-2)/7)+3/5;52+5/28))+1;2)>0)*(JOURSEM(LIGNE(INDIRECT(A6&":"&B6));2)=5))*(cent-4.25);C6+((E6-1)*cent))+SI(ET(JOURSEM(B6;2)=5;EST.IMPAIR(NO.SEMAINE(B6));NON(ESTERREUR(EQUIV(B6;R:R;0))));3.5;0)

qui permet de s'affranchir des colonnes supplementaires à coté des jours fériés.

 

[almas]

Re : Casse tête pour roi des formules 😉

Merci beaucoup jbobo

c 'est presque gagner!😀

ta formule marche bien par contre je n 'est pas su la transformer pour les semaines paire(j 'ai transformer le >0 en =0 pour le calcul de base mais pas sus empêcher l 'ajout des 3h50)

du coup il me rajoute les 3h50 aussi en trop cette fois

et il faut bien sur que ça sois réversible si on tombe sur un vendredi féries de semaine paire cette fois

Petit fichier joint

 

[JBOBO]

Re : Casse tête pour roi des formules 😉

Bonjour,

Je ne comprends pas, tu dis que ça ne fonctionne pas pour les semaines paires, et dans ton exemple, tu montres une cellule où il s'agit s'une semaine impaire (semaine 33, vendredi 15 août) . Ou bien je ne comprends pas la notion de semaine paire et impaire.
Peux tu nous eclaircir sur ce point.

 

[almas]

Re : Casse tête pour roi des formules 😉

1)cette année le cas particulier est tomber une semaine impaire mais il pourrai arriver qu 'un jour férié tombe un vendredi de semaine paire

2)ta formule fonctionne parfaitement quand un agent qui ne travail pas les vendredi après midi de semaine impaire
mais l 'autre moitié des agents travail les semaine impaire (on les appel les agents de semaine paire)
donc je transforme la formule pour que les calcul se fasse sur les semaine paire pour eux (= au lieux de >)mais la dans le cas particulier d' un vendredi férié impaire çà leurs rajoute 3h50

en faite ta formule marche ( une fois transformé) pour les paire ou impaire uniquement si le vendredi férié est de la même semaine que l 'agent (paire ou impaire)

en faite pour l 'agent de semaine inverse au jour vendredi férie il faudrait juste prendre en compte la colonne NB jour qui a déjà déduit le jour férié

😕😕😕😕c 'est simple non😛😉

au final dans le fichier exemple3 les agent paire ou impaire doivent avoir seulement 7h75 de déduit (mais le calcule es différent pour arriver au même résultat)

 

[JBOBO]

Re : Casse tête pour roi des formules 😉

re,

Pas sur d'avoir compris le schmilblick, mais à tester :
Pour les semaines impaires :

=SI(C6=0;E6*cent-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A6&":"& B6))-2)/7)+3/5;52+5/28))+1;2)>0)*(JOURSEM(LIGNE(INDIRECT(A6&":"&B6));2 )=5))*(cent-4.25);C6+((E6-1)*cent))+SI(ET(JOURSEM(B6;2)=5;EST.IMPAIR(NO.SEMAINE(B6));NON(ESTERREUR(EQUIV(B6;R:R;0))));3.5;0)

Pour les semaines paires :

=SI(C22=0;E22*cent-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A22&":"& B22))-2)/7)+3/5;52+5/28))+1;2)=0)*(JOURSEM(LIGNE(INDIRECT(A22&":"&B22));2 )=5))*(cent-4.25);C22+((E22-1)*cent))+SI(ET(JOURSEM(B22;2)=5;EST.PAIR(NO.SEMAINE(B22));NON(ESTERREUR(EQUIV(B22;R:R;0))));3.5;0)

 

[ROGER2327]

Re : Casse tête pour roi des formules 😉

Bonsoir à tous.

(...) Pas sur d'avoir compris le schmilblick (...)

Moi non plus !

Si ça peut aider à fournir des exemples variés de résultats à obtenir, voici une tripotée de vendredis fériés, avec le numéro de semaine correspondant :

​

[table="width: 650", class: grid, align: center"]
[tr]
[td]Date[/td]
[td]Célébration[/td]
[td]Semaine
(impaire)[/td]
[td][/td]
[td]Date[/td]
[td]Célébration[/td]
[td]Semaine
(paire)[/td]
[/tr]
[tr]
[td]15-08-1986[/td]
[td]Assomption[/td]
[td]33[/td]
[td][/td]
[td]01-11-1985[/td]
[td]Toussaint[/td]
[td]44[/td]
[/tr]
[tr]
[td]08-05-1987[/td]
[td]Victoire 1945[/td]
[td]19[/td]
[td][/td]
[td]01-05-1987[/td]
[td]Fête du Travail[/td]
[td]18[/td]
[/tr]
[tr]
[td]01-01-1988[/td]
[td]Nouvel an[/td]
[td]53[/td]
[td][/td]
[td]25-12-1987[/td]
[td]Noël[/td]
[td]52[/td]
[/tr]
[tr]
[td]11-11-1988[/td]
[td]Armistice 1918[/td]
[td]45[/td]
[td][/td]
[td]14-07-1989[/td]
[td]Fête nationale[/td]
[td]28[/td]
[/tr]
[tr]
[td]08-05-1992[/td]
[td]Victoire 1945[/td]
[td]19[/td]
[td][/td]
[td]01-11-1991[/td]
[td]Toussaint[/td]
[td]44[/td]
[/tr]
[tr]
[td]01-01-1993[/td]
[td]Nouvel an[/td]
[td]53[/td]
[td][/td]
[td]01-05-1992[/td]
[td]Fête du Travail[/td]
[td]18[/td]
[/tr]
[tr]
[td]11-11-1994[/td]
[td]Armistice 1918[/td]
[td]45[/td]
[td][/td]
[td]25-12-1992[/td]
[td]Noël[/td]
[td]52[/td]
[/tr]
[tr]
[td]15-08-1997[/td]
[td]Assomption[/td]
[td]33[/td]
[td][/td]
[td]14-07-1995[/td]
[td]Fête nationale[/td]
[td]28[/td]
[/tr]
[tr]
[td]08-05-1998[/td]
[td]Victoire 1945[/td]
[td]19[/td]
[td][/td]
[td]01-11-1996[/td]
[td]Toussaint[/td]
[td]44[/td]
[/tr]
[tr]
[td]01-01-1999[/td]
[td]Nouvel an[/td]
[td]53[/td]
[td][/td]
[td]01-05-1998[/td]
[td]Fête du Travail[/td]
[td]18[/td]
[/tr]
[tr]
[td]15-08-2003[/td]
[td]Assomption[/td]
[td]33[/td]
[td][/td]
[td]25-12-1998[/td]
[td]Noël[/td]
[td]52[/td]
[/tr]
[tr]
[td]11-11-2005[/td]
[td]Armistice 1918[/td]
[td]45[/td]
[td][/td]
[td]14-07-2000[/td]
[td]Fête nationale[/td]
[td]28[/td]
[/tr]
[tr]
[td]15-08-2008[/td]
[td]Assomption[/td]
[td]33[/td]
[td][/td]
[td]01-11-2002[/td]
[td]Toussaint[/td]
[td]44[/td]
[/tr]
[tr]
[td]08-05-2009[/td]
[td]Victoire 1945[/td]
[td]19[/td]
[td][/td]
[td]14-07-2006[/td]
[td]Fête nationale[/td]
[td]28[/td]
[/tr]
[tr]
[td]01-01-2010[/td]
[td]Nouvel an[/td]
[td]53[/td]
[td][/td]
[td]01-05-2009[/td]
[td]Fête du Travail[/td]
[td]18[/td]
[/tr]
[tr]
[td]11-11-2011[/td]
[td]Armistice 1918[/td]
[td]45[/td]
[td][/td]
[td]25-12-2009[/td]
[td]Noël[/td]
[td]52[/td]
[/tr]
[tr]
[td]15-08-2014[/td]
[td]Assomption[/td]
[td]33[/td]
[td][/td]
[td]01-11-2013[/td]
[td]Toussaint[/td]
[td]44[/td]
[/tr]
[tr]
[td]08-05-2015[/td]
[td]Victoire 1945[/td]
[td]19[/td]
[td][/td]
[td]01-05-2015[/td]
[td]Fête du Travail[/td]
[td]18[/td]
[/tr]
[tr]
[td]01-01-2016[/td]
[td]Nouvel an[/td]
[td]53[/td]
[td][/td]
[td]25-12-2015[/td]
[td]Noël[/td]
[td]52[/td]
[/tr]
[tr]
[td]11-11-2016[/td]
[td]Armistice 1918[/td]
[td]45[/td]
[td][/td]
[td]14-07-2017[/td]
[td]Fête nationale[/td]
[td]28[/td]
[/tr]
[tr]
[td]08-05-2020[/td]
[td]Victoire 1945[/td]
[td]19[/td]
[td][/td]
[td]01-11-2019[/td]
[td]Toussaint[/td]
[td]44[/td]
[/tr]
[tr]
[td]01-01-2021[/td]
[td]Nouvel an[/td]
[td]53[/td]
[td][/td]
[td]01-05-2020[/td]
[td]Fête du Travail[/td]
[td]18[/td]
[/tr]
[tr]
[td]11-11-2022[/td]
[td]Armistice 1918[/td]
[td]45[/td]
[td][/td]
[td]25-12-2020[/td]
[td]Noël[/td]
[td]52[/td]
[/tr]
[tr]
[td]15-08-2025[/td]
[td]Assomption[/td]
[td]33[/td]
[td][/td]
[td]14-07-2023[/td]
[td]Fête nationale[/td]
[td]28[/td]
[/tr]
[tr]
[td]08-05-2026[/td]
[td]Victoire 1945[/td]
[td]19[/td]
[td][/td]
[td]01-11-2024[/td]
[td]Toussaint[/td]
[td]44[/td]
[/tr]
[tr]
[td]01-01-2027[/td]
[td]Nouvel an[/td]
[td]53[/td]
[td][/td]
[td]01-05-2026[/td]
[td]Fête du Travail[/td]
[td]18[/td]
[/tr]
[tr]
[td]15-08-2031[/td]
[td]Assomption[/td]
[td]33[/td]
[td][/td]
[td]25-12-2026[/td]
[td]Noël[/td]
[td]52[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]14-07-2028[/td]
[td]Fête nationale[/td]
[td]28[/td]
[/tr]
[tr]
[td][/td]
[td][/td]
[td][/td]
[td][/td]
[td]01-11-2030[/td]
[td]Toussaint[/td]
[td]44[/td]
[/tr]
[/table]


Bon courage.


ℝOGER2327
#7573

Mercredi 18 Absolu 142 (Flûtes de Pan - Vacuation)
4 Vendémiaire An CCXXIII, 6,6933h - colchique
2014-W39-4T16:03:50Z

 

[almas]

Re : Casse tête pour roi des formules 😉

😀bonjour a tous

Merci jbobo pour ton implication
je me suis un peut arracher les cheveux car entre l 'excel avec lequel tu travail et le mien il doit avoir des différence car j 'ai des point a la place des virgules et des blanc dans les fonctions. j 'ai d 'ailleurs pas trouver l 'erreur dans la 2eme formule et due réutilisé la 1er et la modifier(pair au lieux de impaire et = a la place de >)

mais après correction ça marche parfaitement reste à l'applique au classeur original

Jbobo Grand merci tu a gagner la couronne du roi des formules 😛😉 et Rogers toi tu est deja un roi 🙄merci pour le tableau ça m'a permis de tester un vendredi férié paire (le 1er mai 2015).... et de réserver mon We de la Toussaint 2030 MDR

petit fichier final pour ceux que ça intéresserai