Recherche formule désespérément ...

S

sofysofe

XLDnaute Junior
Bonjour,

J'essaie d'automatiser le fichier de mon chef, fichier qui sert à suivre le décompte de nos jours de congès, RTT, jour acquis par ancienneté, et fractionnement. Je n'ai pas de problème au niveau du décompte des congès RTT, etc ... mon problème est au niveau du calcul des jours acquis pour le fractionnement, je n'arrive pas à faire la formule, mon problème etant que les règles de calculs ne sont pas simples :

Base de calcul = 20 jours (25 - 5ème semaine)
Période = 1er Juin au 31 Octobre
Obligation : Avoir pris au moins 10 jours consécutifs pendant cette période
Calcul : Déduire le nombre de jours pris pendant la période, à ces 20 jours.
Si le nombre restant est >ou= à 5 ==> 2 Jours de fractionnement
Si le nombre restant est > à 2 et < à 5 ==> 1 Jour de fractionnement


Ma problématique :
Mon boss ne veut pas que la forme de son fichier change
Les WE apparaissent dans les tableaux, ce qui me complique la tâche pour compter les jours CP pris consécutivement.
les jours de Congès pris sont notés CP ou 1/2 CP pour une demi journée prise.
Le calcul se fait en haut de la sheet plage G6:G11

Merci d'avance à celui qui trouvera la formule permettant de calculer le nombre de jours acquis par fractionnement !
 

Pièces jointes

  • CP_RTT_2009.zip
    21.9 KB · Affichages: 391
Trier par date Trier par votes
A

abcd

XLDnaute Barbatruc
Re : Recherche formule désespérément ...

Bonjour,

Un début : deux simplifications de formules

D6 : =MIN(2;ENT(DATEDIF(B6;AUJOURDHUI();"y")/5))
K6 : =SOMMEPROD((A$69:A$114=A6)*((H$69:AL$114="CP")+(H$69:AL$114="1/2 CP")/2))

Pour le reste je regarde comment faire.

abcd
 
Vote positif 0 Vote négatif
ROGER2327

ROGER2327

XLDnaute Barbatruc
Re : Recherche formule désespérément ...

Mon boss ne veut pas que la forme de son fichier change
Cliquez pour agrandir...

Tous les mêmes ! Ils commencent par fusionner des cellules, mettre des belles couleurs partout, oublier les clefs uniques et tous les bons principes de construction des BDD et ils refilent le taf au grouillot de service... Je craque !
Bon courage, sofysofe...​
ROGER2327
 
Vote positif 0 Vote négatif
S

sofysofe

XLDnaute Junior
Re : Recherche formule désespérément ...

abcd à dit:
Bonjour,

Un début : deux simplifications de formules

D6 : =MIN(2;ENT(DATEDIF(B6;AUJOURDHUI();"y")/5))
K6 : =SOMMEPROD((A$69:A$114=A6)*((H$69:AL$114="CP")+(H$69:AL$114="1/2 CP")/2))

Pour le reste je regarde comment faire.

abcd
Cliquez pour agrandir...

Re-bonjour,

J'essaie de comprendre ta formule pour les jours d'ancienneté, à savoir =MIN(2,ENT(DATEDIF(B6,AUJOURDHUI(),"y")/5)) car elle est vraiment plus simple que celle que j'ai faite moi même donc je veux l'utiliser, mais j'aimerai pouvoir l'expliquer aussi ! Donc, je coimprend tout sauf la division par 5, je sais que ça doit être tout bête, mais j'y arrive pas ... :eek:

Encore merci d'avance !

Sofysofe
 
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : Recherche formule désespérément ...

Bonjour le forum :),
Abcd n'ayant pas l'air dans le coin...
La partie entière du nombre d'année depuis la date d'embauche divisé par 5 donne 2 pour 10 ans et plus, 1 pour 5 à 9, et le MIN limite à 2 pour 20 ans et plus.
Bonne journée :cool:
 
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : Recherche formule désespérément ...

Re :),
En me penchant un peu plus sur ton problème, j'ai bien peur que là où ça va pas le faire, c'est les jours consécutifs, tout au moins en formule (mais j'espère me tromper...). D'où un début de proposition en macro :
Code: 
Function CPPériode(Juin As Range, Juillet As Range, Août As Range, Septembre As Range, Octobre As Range) As Double
Dim Cellule As Range
For Each Cellule In Juin
If Cellule = "CP" Then CPPériode = CPPériode + 1
If Cellule = "1/2 CP" Then CPPériode = CPPériode + 0.5
Next
For Each Cellule In Juillet
If Cellule = "CP" Then CPPériode = CPPériode + 1
If Cellule = "1/2 CP" Then CPPériode = CPPériode + 0.5
Next
For Each Cellule In Août
If Cellule = "CP" Then CPPériode = CPPériode + 1
If Cellule = "1/2 CP" Then CPPériode = CPPériode + 0.5
Next
For Each Cellule In Septembre
If Cellule = "CP" Then CPPériode = CPPériode + 1
If Cellule = "1/2 CP" Then CPPériode = CPPériode + 0.5
Next
For Each Cellule In Octobre
If Cellule = "CP" Then CPPériode = CPPériode + 1
If Cellule = "1/2 CP" Then CPPériode = CPPériode + 0.5
Next
End Function
par contre, ton boss accepterait-il des "R" dans les repos ? Cela permetrait d'inclure un compteur dans l'analyse séquentielle.
De même, quitte à passer par de la macro, le remplissage des cellules via des macros te permettrait d'importer la mise en forme, contrairement aux listes de validation.
Bonne fin d'AM :cool:
 
Vote positif 0 Vote négatif
S

sofysofe

XLDnaute Junior
Re : Recherche formule désespérément ...

JNP à dit:
Re :),
En me penchant un peu plus sur ton problème, j'ai bien peur que là où ça va pas le faire, c'est les jours consécutifs, tout au moins en formule (mais j'espère me tromper...). D'où un début de proposition en macro :
Code: 
Function CPPériode(Juin As Range, Juillet As Range, Août As Range, Septembre As Range, Octobre As Range) As Double
Dim Cellule As Range
For Each Cellule In Juin
If Cellule = "CP" Then CPPériode = CPPériode + 1
If Cellule = "1/2 CP" Then CPPériode = CPPériode + 0.5
Next
For Each Cellule In Juillet
If Cellule = "CP" Then CPPériode = CPPériode + 1
If Cellule = "1/2 CP" Then CPPériode = CPPériode + 0.5
Next
For Each Cellule In Août
If Cellule = "CP" Then CPPériode = CPPériode + 1
If Cellule = "1/2 CP" Then CPPériode = CPPériode + 0.5
Next
For Each Cellule In Septembre
If Cellule = "CP" Then CPPériode = CPPériode + 1
If Cellule = "1/2 CP" Then CPPériode = CPPériode + 0.5
Next
For Each Cellule In Octobre
If Cellule = "CP" Then CPPériode = CPPériode + 1
If Cellule = "1/2 CP" Then CPPériode = CPPériode + 0.5
Next
End Function
par contre, ton boss accepterait-il des "R" dans les repos ? Cela permetrait d'inclure un compteur dans l'analyse séquentielle.
De même, quitte à passer par de la macro, le remplissage des cellules via des macros te permettrait d'importer la mise en forme, contrairement aux listes de validation.
Bonne fin d'AM :cool:
Cliquez pour agrandir...


Tout d'abord, encore merci !
Ensuite, concernant ta question sur des "R" dans les repos (je crois comprendre que tu parle des week-end quand tu ecris repos ?), à la limite, il n'est pas obligé de les voir (formattage) et si il veux rajouter une données (quand on a des permanences le WE) rien ne l'en empêche. Par contre, pas de "R", je m'en sert déjà pour signifier des Récup, car on a des recup quand on fait des permanences, ça peut très bien être "WE" à la place "R", non ?
Par contre, il aime bien remplir son tableau, donc pas de remplissage par macro, et j'ai fait un code VBA pour la mise en forme conditionnelle.
Sinon, je viens de tester ta fonction : très bien, mais en fait ça compte le nombre total de jours pris et nom le nombre de jours consécutifs, et c'est là qu'est l'os ... mais je place beaucoup d'espoir dans tes capacités à m'aider ;)
 
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : Recherche formule désespérément ...

Re :),
Il y a peut-être (sûrement :p) moyen de la simplifier, mais testée sur ton fichier, elle semble donner les résultats escomptés (en oubliant pas de rajouter WE, même sur les fériés)
Code: 
Function CPConsécutifs(Juin As Range, Juillet As Range, Août As Range, Septembre As Range, Octobre As Range) As Double
Dim CP As Boolean
CP = False
Dim WE As Boolean
WE = False
Dim Compteur As Integer
Compteur = 0
Dim EnCours As Integer
EnCours = 0
Dim Cellule As Range
For Each Cellule In Juin
If Cellule = "CP" Then
    EnCours = EnCours + 1
    CP = True
End If
If Cellule = "WE" And CP = True Then
    WE = True
End If
If Cellule <> "WE" And Cellule <> "CP" And CP = True Then
    If CPConsécutifs < EnCours Then CPConsécutifs = EnCours
    EnCours = 0
    WE = False
    CP = False
End If
Next
For Each Cellule In Juillet
If Cellule = "CP" Then
    EnCours = EnCours + 1
    CP = True
End If
If Cellule = "WE" And CP = True Then
    WE = True
End If
If Cellule <> "WE" And Cellule <> "CP" And CP = True Then
    If CPConsécutifs < EnCours Then CPConsécutifs = EnCours
    EnCours = 0
    WE = False
    CP = False
End If
Next
For Each Cellule In Août
If Cellule = "CP" Then
    EnCours = EnCours + 1
    CP = True
End If
If Cellule = "WE" And CP = True Then
    WE = True
End If
If Cellule <> "WE" And Cellule <> "CP" And CP = True Then
    If CPConsécutifs < EnCours Then CPConsécutifs = EnCours
    EnCours = 0
    WE = False
    CP = False
End If
Next
For Each Cellule In Septembre
If Cellule = "CP" Then
    EnCours = EnCours + 1
    CP = True
End If
If Cellule = "WE" And CP = True Then
    WE = True
End If
If Cellule <> "WE" And Cellule <> "CP" And CP = True Then
    If CPConsécutifs < EnCours Then CPConsécutifs = EnCours
    EnCours = 0
    WE = False
    CP = False
End If
Next
For Each Cellule In Octobre
If Cellule = "CP" Then
    EnCours = EnCours + 1
    CP = True
End If
If Cellule = "WE" And CP = True Then
    WE = True
End If
If Cellule <> "WE" And Cellule <> "CP" And CP = True Then
    If CPConsécutifs < EnCours Then CPConsécutifs = EnCours
    EnCours = 0
    WE = False
    CP = False
End If
If CPConsécutifs < EnCours Then CPConsécutifs = EnCours
Next
End Function
Je pense que tu t'en tireras pour la formule ensuite.
Bonne soirée :cool:
 
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : Recherche formule désespérément ...

Re :),
Pour le fun, en G6
Code: 
=SI(CPConsécutifs(H69:AK69;H79:AL79;H89:AL89;H99:AK99;H109:AL109)>=10;SI(20-CPPériode(H69:AK69;H79:AL79;H89:AL89;H99:AK99;H109:AL109)>=5;2;SI(20-CPPériode(H69:AK69;H79:AL79;H89:AL89;H99:AK99;H109:AL109)>2;1;0));0)
Bonne soirée :cool:
 
Vote positif 0 Vote négatif
A

abcd

XLDnaute Barbatruc
Re : Recherche formule désespérément ...

Bonjour,

Le calcul des CP s'avère fort difficile par formule, le problème majeur étant d'éventuelles périodes à cheval sur deux mois.
Exemple : du 28/09/2009 au 02/10/2009.

En réorganisant le calendrier (feuille 1 de la pièce jointe), ce qui peut être automatisé sans aucune autre intervention ultérieure, le calcul devient beaucoup plus simple.
Le "boss" maintient toujours son tableau tel qu'il le désire.
Les formules n'ont pas été optimalisées, ce n'est qu'un exemple de la façon de procéder.

Il y a donc un choix à faire, une macro ou un tableau supplémentaire.

abcd
 

Pièces jointes

  • sofysofe - CP_RTT_2009.zip
    21.8 KB · Affichages: 251
Vote positif 0 Vote négatif
JNP

JNP

XLDnaute Barbatruc
Re : Recherche formule désespérément ...

Bonjour le forum :),
Code: 
j'ai bien peur que là où ça va pas le faire, c'est les jours consécutifs,
tout au moins en formule (mais j'espère me tromper...).
Abcd me prouve que j'avais raison d'espérer me tromper :p. Seul le cas particulier d'un samedi ou dimanche travaillé n'est pas pris en compte, mais j'espère que ton boss ne fait pas travailler quelqu'un le samedi entre 2 semaines de vacances !!! :D
Tu n'as plus que l'embarras du choix ;)
Bonne journée :cool:
 
Vote positif 0 Vote négatif

Discussions similaires

H
  • Question
XL 2021 *RESOLU* Calcul du nombre de jours de fractionnement
Réponses
14
Affichages
1 K
hades
H
A
  • Question
XL 2021 Problème Index.equiv avec décalage fractionné
Réponses
1
Affichages
210
gbinforme
G
J
  • Résolu(e)
XL 2013 Formule si avec conditions
Réponses
13
Affichages
374
nat54
nat54
T
  • Question
XL 2016 Tenir compte que du dernier élément donné
Réponses
2
Affichages
217
telemarrk
T
K
  • Verrouillé
  • Résolu(e)
XL 2016 [Résolu]Calculer l'ancienneté en prenant en compte l'interruption
Réponses
10
Affichages
512
Kirill95
K
E
  • Question
XL 2016 Calcul cellules vides sous conditions multiples
Réponses
8
Affichages
397
DJunqueira
D
C
  • Résolu(e)
XL 2016 Nombre de jours effectués par mois
Réponses
12
Affichages
933
CathU
C
D
  • Question
XL 2021 Formulaire de calcul des jours proratisés
Réponses
0
Affichages
244
Did25
D
B
  • Question
XL 2019 calendrier horaire travail
Réponses
9
Affichages
521
Bsm
B
T
  • Question
Microsoft 365 formule pour calcul jours de conge
Réponses
4
Affichages
374
Tonio59160
T

Membres actuellement en ligne

13 membres et 152 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 753
Messages
2 091 668
Membres
105 040
dernier inscrit
PeupleVert