formule pour calcule jour/semaine paire

almas · 13 Novembre 2013

Bonjour à tous

J’ai besoins de vous car la je sèche un peut.
Je ne sais pas si on peut faire avec une formule ou si faut passer en macro

Voila dans le fichier joint vous trouverez une gestion simple de personnel

Mon soucis c’est que les agents ne travail pas 1 vendredi après midi sur 2

Donc dans mon calcule il faut que si il y a 1 ou plusieurs vendredi de semaine paire (par exemple) dans ma plage de dates choisis, celui ou ceux-ci doivent être comptabilisé à 4h.25 (en centième) au lieu de 7h 75 journée pleine.

J’ai toutes les données mais j arrive pas à savoir pas quel bout commencer ma formule

Dans mon exemple de dates choisi j’ai pris le pire des cas (vacance d été) 1 mois avec 2 vendredi de chaque

Récapitulatif

formule pour semaine paire -vendredi uniquement le matin
SI(C4=0;((E4-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A4&":"&B4))-2)/7)+3/5;52+5/28))+1;2)=0)*(JOURSEM(LIGNE(INDIRECT(A4&":"&B4));2)=5)))*cent)+(SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A4&":"&B4))-2)/7)+3/5;52+5/28))+1;2)=0)*(JOURSEM(LIGNE(INDIRECT(A4&":"&B4));2)=5))*4,25);C4+((E4-1)*cent))

Date début Date fin Fraction de jour TYPE Nb jours centième minutes N° semaine
vendredi 4 janvier 2013 - vendredi 4 janvier 2013 1 7,75 h 7 h 45 mn -1
vendredi 11 janvier 2013 - vendredi 11 janvier 2013 1 4,25 h 4 h 15 mn -2
vendredi 18 janvier 2013 - vendredi 18 janvier 2013 1 7,75 h 7 h 45 mn -3
vendredi 25 janvier 2013 - vendredi 25 janvier 2013 1 4,25 h 4 h 15 mn -4

formule pour semaine impaire -vendredi uniquement le matin
SI(C4=0;((E4-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A4&":"&B4))-2)/7)+3/5;52+5/28))+1;2)>0)*(JOURSEM(LIGNE(INDIRECT(A4&":"&B4));2)=5)))*cent)+(SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A4&":"&B4))-2)/7)+3/5;52+5/28))+1;2)>0)*(JOURSEM(LIGNE(INDIRECT(A4&":"&B4));2)=5))*4,25);C4+((E4-1)*cent))

Date début Date fin Fraction de jour TYPE Nb jours centième minutes N° semaine
vendredi 4 janvier 2013 - vendredi 4 janvier 2013 1 4,25 h 4 h 15 mn -1
vendredi 11 janvier 2013 - vendredi 11 janvier 2013 1 7,75 h 7 h 45 mn -2
vendredi 18 janvier 2013 - vendredi 18 janvier 2013 1 4,25 h 4 h 15 mn -3
vendredi 25 janvier 2013 - vendredi 25 janvier 2013 1 7,75 h 7 h 45 mn -4

formule pour toutes semaines -vendredi en entier
"SI(C30=0;E30*cent;C30+((E30-1)*cent))"

Date début Date fin Fraction de jour TYPE Nb jours centième minutes N° semaine
vendredi 4 janvier 2013 - vendredi 4 janvier 2013 1 7,75 h 7 h 45 mn -1
vendredi 11 janvier 2013 - vendredi 11 janvier 2013 1 7,75 h 7 h 45 mn -2
vendredi 18 janvier 2013 - vendredi 18 janvier 2013 1 7,75 h 7 h 45 mn -3
vendredi 25 janvier 2013 - vendredi 25 janvier 2013 1 7,75 h 7 h 45 mn -4

excfl · 13 Novembre 2013

Re : formule pour calcule jour/semaine paire

Bonjour almas,

Peut-être ?

C16 : =SI(EST.PAIR(A16);"4,25";"")
]

A tirer vers le bas.

Remarque : E16 : 4,25 et non 4,25h

excfl

almas · 13 Novembre 2013

Re : formule pour calcule jour/semaine paire

la je comprend pas trop excfl

la colonne C ne sert que quand les agent ne pose que des fraction de jour (1h50 par exemple) et le H et transparent car dans les préférence cellule

excfl · 13 Novembre 2013

Re : formule pour calcule jour/semaine paire

Re,

Peut-être ?

F16 : =SIERREUR(SI(C16="";E16*cent;C16+((E16-1)*cent));"")

excfl

Jocelyn · 13 Novembre 2013

Re : formule pour calcule jour/semaine paire

Bonjour le Forum,
Bonjour almas, excfl,

un essai en fichier joint

pour compter le nombre de vendredi qui tombent sur une semaine pair cette partie de la formule

Code:

SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A18&":"&B18))-2)/7)+3/5;52+5/28))+1;2)=0)*(JOURSEM(LIGNE(INDIRECT(A18&":"&B18));2)=5))

tu as aussi un vendredi en semaine pair dans la ligne 17 le vendredi 22/02 semaine 8

regardes et dis nous

Cordialement

almas · 13 Novembre 2013

Re : formule pour calcule jour/semaine paire

je dois compléter cette formule par des noms?
car la je vois pas de référence au semaine paire ni au vendredi et au nom "demicent

un truc m’échappe dsl

almas · 13 Novembre 2013

Re : formule pour calcule jour/semaine paire

Jocelyn

bravo ca marche a parement
j 'ai changer les dates sur une semaine impaire et a bien pris 5 jour plein!

me reste a vérifier le mois complet avec le jour ferier ect...
après je vais décortiquer ta formule pour essayer de la comprendre

edit:

CA marche nickel merci
par contre dans la formule j 'ai 2 questions (pour la comprendre et pouvoir la reproduire ou la modifier)
1) je pense que je peut remplacer les 4.25 par le nom "demicent" dans les opération?
2) je trouve pas ou tu fait reference au semaine pair? (car j 'ai l autre moitié des agent qui fonctionnerons avec les semaine impaire et je dois pouvoir modifier cela)

Jocelyn · 13 Novembre 2013

Re : formule pour calcule jour/semaine paire

re,

la formule est intégrée dans les cellules F16:F18 globalement elle donne :

Code:

=SI(C18=0;((E18-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A18&":"&B18))-2)/7)+3/5;52+5/28))+1;2)=0)*(JOURSEM(LIGNE(INDIRECT(A18&":"&B18));2)=5)))*cent)+(SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A18&":"&B18))-2)/7)+3/5;52+5/28))+1;2)=0)*(JOURSEM(LIGNE(INDIRECT(A18&":"&B18));2)=5))*4,25);C18+((E18-1)*cent))

a E18 on enlève les vendredi des semaines paires que l'on multiplie par cent

(E18-SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A18&":"&B18))-2)/7)+3/5;52+5/28))+1;2)=0)*(JOURSEM(LIGNE(INDIRECT(A18&":"&B18));2)=5)))*cent)

et ensuite on ajoute à ce montant les vendredi des semaine paires multipliés par demicent (dans la formule j'ai directement mis 4.25

+(SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A18&":"&B18))-2)/7)+3/5;52+5/28))+1;2)=0)*(JOURSEM(LIGNE(INDIRECT(A18&":"&B18));2)=5))*4,25)

voila regardes si les montant sont cohérent

Jocelyn · 13 Novembre 2013

Re : formule pour calcule jour/semaine paire

re

pour déterminer les vendredi des semaine impaires il faudra dans la formule

SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A18&":" &B18))-2)/7)+3/5;52+5/28))+1;2)=0)*(JOURSEM(LIGNE(INDIRECT(A18&":"&B18)) ;2)=5)))

remplacer le = en rouge par > en rouge

SOMMEPROD((MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A18&":" &B18))-2)/7)+3/5;52+5/28))+1;2)>0)*(JOURSEM(LIGNE(INDIRECT(A18&":"&B18)) ;2)=5)))

almas · 13 Novembre 2013

Re : formule pour calcule jour/semaine paire

merci beaucoup jocelyn

il va me falloir un moment pour bien décortiqué tes explications car il y a certain chiffres dons je vois pas encore a quoi il correspondes
mais je vais faire des tests pour bien la comprendre

encore merci ça marche a merveille ! Bravo

Jocelyn · 13 Novembre 2013

Re : formule pour calcule jour/semaine paire

re

pour t'aider à comprendre la formule en H18 tu =ENT(MOD(ENT((A18-2)/7)+0,6;52+5/28))+1 qui permet de dire en quelle semaine se trouve la date de la cellule A18

maintenant en modifiant cette formule et en l'intégrant dans un sommeprod (qui est une formule matricielle) de cette façon

ENT(MOD(ENT((LIGNE(INDIRECT(A18&":" &B18))-2)/7)+3/5;52+5/28))+1 la formule va pour chaque date comprises entre les dates de A18 et de B18 renvoyer le numéro de semaine

ensuite on met tout ça dans une fonction mod() cette fonction permet de renvoyer le reste d'une division

donc cela donne (MOD(ENT(MOD(ENT((LIGNE(INDIRECT(A18&":" &B18))-2)/7)+3/5;52+5/28))+1;2)=0) ici division par 2 si le reste est 0 alors semaine paire

maintenant on veut que se soit les vendredi toujours dans le sommeprod on détermine le vendredi par la fonction joursem() qui renvoi le numéro du jour dans la semaine

ce qui donne

(JOURSEM(LIGNE(INDIRECT(A18&":"&B18)) ;2)=5) par LIGNE(INDIRECT(A18&":"&B18)) on renvoi le numéro de semaine de tous les jours compris entre A18 et B18 le ;2 sert a ce que le lundi soit considéré comme le 1 si on ne met rien ici le 1 correspond au dimanche

donc le sommeprod va compter tous les joursemm renvoyant 5 pour le vendredi si le mod(numéro de semaine est egal) a 0

Voila j'espère que cela t'éclaire un peu

almas · 14 Novembre 2013

Re : formule pour calcule jour/semaine paire

merci bc jocelyn pour ces explications

almas · 5 Décembre 2013

Re : formule pour calcule jour/semaine paire

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA!!

Bonjour à tous

dsl pour le petit crie du matin mais j 'arrive pas a transformer la formule de jocelyn (même avec ces explications)

Je passe bien des vendredi après midi paire ou impaire sans problème mais maintenant il faut que je supprime cette condition car certain travail tous les vendredis!

donc il faut que tous les vendredi de l année sois compter a 7h75 qu ' ils soit paire ou impaire et la apres avoir traficoté la formule dans tous les sens j 'y suis toujours pas arrivé!!
je joint un petit fichier recap

Jocelyn · 5 Décembre 2013

Re : formule pour calcule jour/semaine paire

Bonjour le Forum,
Bonjour Almas,

Vu ta demande plus besoin de déterminer si la semaine est pair ou impaire donc si tu veux le faire suivant ma formule mettre en F30

Code:

=SI(C30=0;SOMMEPROD((JOURSEM(LIGNE(INDIRECT(A30&":"&B30));2)<6)*cent);C30+((E30-1)*cent))

maintenant je trouve que cela fait sortir l'artillerie lourde pour tuer une mouche à ta place j'utiliserais quelque chose comme cela

Code:

=SI(C30=0;E30*cent;C30+((E30-1)*cent))

enfin si j'ai bien compris ce qui n'est pas sur

Cordialement

almas · 5 Décembre 2013

Re : formule pour calcule jour/semaine paire

Merci Jocelyn

Effectivement la simplification est tous à fait suffisante ^^

encore Merci Jocelyn

ps:récapitulatif sur le 1er post

Effectuez une recherche sur Excel Downloads...

formule pour calcule jour/semaine paire

almas

XLDnaute Occasionnel

Pièces jointes

excfl

XLDnaute Barbatruc

almas

XLDnaute Occasionnel

excfl

XLDnaute Barbatruc

Jocelyn

XLDnaute Barbatruc

Pièces jointes

almas

XLDnaute Occasionnel

almas

XLDnaute Occasionnel

Jocelyn

XLDnaute Barbatruc

Jocelyn

XLDnaute Barbatruc

almas

XLDnaute Occasionnel

Jocelyn

XLDnaute Barbatruc

almas

XLDnaute Occasionnel

almas

XLDnaute Occasionnel

Pièces jointes

Jocelyn

XLDnaute Barbatruc

almas

XLDnaute Occasionnel

Discussions similaires

Membres actuellement en ligne

Partager cette page

Tutos vidéos

Statistiques des forums