XL 2016 Planning congés annuel

[Noé71410]

Bonjour à tous,

Voilà, j'ai créée un tableau simple pour pouvoir y noter différentes choses pour les agents (Congé, maladie, accident, RC).
Je dois aussi trouver le moyen de calculer les jours de fractionnements (Jours de congés supplémentaire accordés suivant certaines règles...).
La base est de deux jours de fractionnement possible maximum. La règle: Si 5,6 ou 7 jours de congés posé entre le 1er janvier et le 30 avril et entre le 1er novembre et 31décembre alors 1jrs de fractionnement. Si 8jrs ou plus posés entre ces mêmes dates alors 2jrs de fractionnement.


Merci de votre aide, heureusement que vous êtes là pour la galérien comme moi.

PS: si vous avez une astuce aussi pour que les colonnes des jours fériés se teinte automatiquement Merci.

 

[AtTheOne]

Bonjour à toutes & à tous, bonjour @Noé71410
Regarde si le fichier joint te convient,
Des explications et les jours fériés après le repas ...
amicalement
Alain

 

[Noé71410]

Au top merci,
J'avais trouver une solution aussi avec si si imbriqué mais la votre demande moins de boulot (mais plus de connaissance ça c'est sur).

Encore merci pour votre aide a toutes et à tous!

 

[AtTheOne]

Bonjour à toutes & à tous, Bonjour @Noé71410
Le même fichier qu'au post#2 avec les jours fériés et adaptation des formats du planning pour les années bissextiles.
Pour une année bissextile, le planning s'allonge d'un jour, les limites de mois >= Février se décalent d'une colonne à droite (formats bordures + formats conditionnels)
Les jours fériés dans un tableau avec Pâques calculée avec une formule valable de 1900 à 2204 (parmi celles que l'on trouve sur le Net).
Outre le tableau structuré de l'onglet "Recap par agents" nommé "Tb_Recap_Agents" et celui de l'onglet "Tables" nommé "JoursFériés",
le classeur utilise les noms suivant : (l'objectif étant de simplifier la lecture et l'écriture des formules)

Nom défini	Formule	Commentaire
Année	='Planning 2022'!$A$1	Année suivie
Liste_Agents	='Planning 2022'!$A$5:$A$34	Liste des agents dans la feuille Planning
DatesArrêts	='Planning 2022'!$G$4:$NH$4	Liste des dates du planning
Planning	='Planning 2022'!$G$5:$NH$34	Plage de la feuille planning où l'on pose les absences
Plage_Agent	=INDEX(Planning;EQUIV(Tb_Recap_Agents[[#Cette ligne];[Agents]];Liste_Agents;0)	Ligne du planning correspondant à l'agent courant dans le tableau Tb_Recap_Agents.
Avril30	=Tables!$B$2	30 avril de l'année suivie
Novembre1	=Tables!$B$3	1er novembre de l'année suivie
Pâques	=SI(ET(Année>=1900;Année<=2204);DATE(Année;3;29,56+0,979*MOD(204-11*MOD(Année;19);30)- JOURSEM(DATE(Année;3;28,56+0,979*MOD(204-11*MOD(Année;19);30))));"Hors domaine formule")	Date de Pâques de l'année suivie
DatesFériées	=JoursFériés[Date]	Dates des jour fériés (dans le tableau JoursFériés)

La formule pour renvoyer le nombre de congés supplémentaires de fractionnement est la suivante :

=CHOISIR(EQUIV(NB.SI.ENS(DatesArrêts;"<="&Avril30;Plage_Agent;"CA")+NB.SI.ENS(DatesArrêts;">="&Novembre1;Plage_Agent;"CA");{0;5;8};1);0;1;2)

Où
NB.SI.ENS(DatesArrêts;"<="&Avril30;Plage_Agent;"CA") renvoie, pour les dates antérieures ou égale au 30 avril de l'année suivie, le nombre de jours ="CA" dans Plage_Agent.

NB.SI.ENS(DatesArrêts;">="&Novembre1;Plage_Agent;"CA") renvoie, pour les dates égales ou postérieures au 1er novembre de l'année suivie, le nombre de jours = "CA" dans Plage_Agent.

On fait la somme des 2 et l'on recherche l'équivalent de cette somme dans la matrice {0;5;8}à l'aide de la fonction
EQUIV(NB.SI.ENS(DatesArrêts;"<="&Avril30;Plage_Agent;"CA")+NB.SI.ENS(DatesArrêts;">="&Novembre1;Plage_Agent;"CA");{0;5;8};1)
Qui va renvoyer selon la somme trouvée 1, 2 ou 3 (1 pour les sommes >=0 et <5 ; 2 pour les sommes >=5 et <8 ; 3 pour les sommes >=8)

La fonction choisir renvoie; selon que ce résultat est 1, 2 ou 3, la valeur 0 1 ou 2 :
CHOISIR(EQUIV(NB.SI.ENS(DatesArrêts;"<="&Avril30;Plage_Agent;"CA")+NB.SI.ENS(DatesArrêts;">="&Novembre1;Plage_Agent;"CA");{0;5;8};1);0;1;2)

Voilà pour les explications promises au post #2.

Merci, lorsqu'un post répond à la question posée, de le marquer comme solution.

Amicalement
Alain

 

[Noé71410]

Super,
Merci beaucoup pour les explications.