XL 2019 Générer liste des jours de classe (L-M-J-V) en retirant vacances et jours fériés

[FredMa]

Bonjour,

Je cherche à générer la liste des jours de classes d'une année scolaire en retirant :
- les mercredis, samedis, dimanches,
- les jours de vacances,
- les jours fériés.
J'arrive avec la formule =SERIE.JOUR.OUVRE à exclure les we et jours fériés...
=SERIE.JOUR.OUVRE(B2;1;A2:A10)
ou la plage A2:A10 correspond à la liste des jours fériés.

Mercredi-Samedi-Dimanche : OU(JOURSEM(H3)=4;JOURSEM(H3)=7;JOURSEM(H3)=1)

Par contre, je ne vois pas comment exclure en plus les mercredis et zones de vacances.
J'avais pensé utiliser JOURSEM() et RECHERCHEV() mais je n'arrive à rien ;(

Un petit fichier pour être plus clair.

Merci de votre aide
Fred

 

[sylvanu]

Bonsoir,
Voir PJ.

 

[FredMa]

Bonsoir,
Extra, votre macro fonctionne parfaitement et a été intégrée. Il va me falloir un peu de temps pour bien en comprendre le fonctionnement
Dernière étape, je cherche maintenant à extraire les dates de vacances en colonne L.
Je pensais à 3 pistes :
- une macro qui parcourt la colonne G, à la recherche de la valeur "Vacances" pour en extraire le jour correspondant de la colonne F,
- des formules du type RECHERCHEV,
- des requêtes sur les variables DVac (début vacances) et FVac (fin vacances) extraites de l'onglet BD_CAL.

 

[FredMa]

J'ai 2 variables : Dvac( colonne contenant les débuts de vacances) et Fvac (colonne contenant les fins de vacances). Je ne sais pas comment rechercher et extraire les données dans ces variables…
Cela devrait être possible d'isoler les dates de début et fin de vacances :
Vacances de Toussaint : Dvac toujours en octobre de l'année An - Fvac est sur la même ligne
Vacances de Noël : Dvac est toujours en décembre de l'année- Fvac est sur la même ligne
Vacances d'Hiver: Dvac est toujours en février(02) de l'année An+1 - Fvac est sur la même ligne
Vacances de Printemps : Dvac est toujours en avril (04) de l'année An+1 - Fvac est sur la même ligne

Autre essai , j'ai tenté de faire une recherche de date avec Index/Equiv :
Je recherche la date du 19/10/2019 dans la colonne des débuts de vacances :
=SIERREUR(INDEX(B:B; EQUIV(DATE(2019;10;19); B:B; 0)); "Pas trouvé")
Cela fonctionne par contre je n'arrive pas à faire une recherche juste avec le mois et l'année :
=SIERREUR(INDEX(B:B;EQUIV(DATE(2019;10;*);TEXTE(B:B;"dd/mm/yyyy");0));"Pas trouvé")

 

[FredMa]

J'ai réussi même si c'est un peu lourdingue avec une formule du type où je recherche les dates correspondant à un mois de vacances (octobre par exemple) et une année donnée :
TEXTE(INDEX(B:B; EQUIV(10&An; MOIS(B:B)&ANNEE(B:B); 0));"j mmmm aaaa")

Seuls hic :
- les vacances d'hiver ne finissent pas toujours en février mais parfois en mars
- les vacances de printemps ne finissent pas toujours en avril ou mai
Du coup je fais une formule du type :
SIERREUR(TEXTE(INDEX(C:C; EQUIV(2&An+1; MOIS(C:C)&ANNEE(C:C); 0));"j mmmm aaaa");TEXTE(INDEX(C:C; EQUIV(3&An+1; MOIS(C:C)&ANNEE(C:C); 0));"j mmmm aaaa"))

Merci à @sylvanu pour ses nombreuses aides !

Fred

 

[JHA]

Bonjour à tous,

Un début avec power query

JHA

 

[danielco]

Bonjour,

Si la demande est toujours la demande initiale, avec Excel 365, en G2 :

=LET(tbl;BYROW(SEQUENCE(C8-C2-2;1;C2+1);LAMBDA(x;SI(ET(ESTNA(EQUIVX(x;C2:C8));ET(JOURSEM(x)>1;JOURSEM(x)<7;JOURSEM(x)<>4);SOMME((B2:B8<x)*(C2:C8>x))=0);x;"")));FILTRE(tbl;tbl<>""))

Daniel

 

[JHA]

Bonjour à tous,

Ajout des fériés dans la liste des vacances et une liste des jours sans les week-end et les vacances.

JHA

 

[danielco]

Bonjour,
Avec le classeur :

 

[JHA]

Bonjour à tous,

Bravo @danielco pour cette démonstration de formules sous Excel 365.

Ne possédant pas cette version, je reposte avec un ajout d'une formule ne comprenant pas les mercredis.

=SERIE.JOUR.OUVRE.INTL(H2;1;"0010011";VacancesFériés[Date])

JHA

 

[FredMa]

Merci de ces différentes possibilités.
En voici une autre avec une macro.
Y a juste que je n'arrive pas à lancer la macro automatiquement au changement de la liste déroulante et un petit souci lors de format de la date lorsque je rajoute du texte dans la cellule ("du " ou "au )" :
Range("G6").Value = "du " & DateDebutVacancesAutomne -> me renvoie du 20/10/2018
Range("G7").Value = "DateDebutVacancesAutomne) -> me renvoie samedi 20 octobre 2018.

Moi je voudrais afficher "du samedi 20 octobre" (ajout de "du" et suppression de l'année).
J'ai tenté Range("G7").Value = "du " &Format(DateDebutVacancesAutomne, "jjjj j mmmm aaaa")
mais ne marche pas !

 

[FredMa]

Voici le projet enfin bouclé et à priori sans erreur.
Merci pour les nombreuses aides !

 

[Efgé]

Bonjour à tous
Après la bataille...
Etrangement, la proposition de @danielco affiche les 01/04/2024; 09/05/2024 et 20/05/2024 qui sont dans la liste des fériés
Je propose une autre version.

=LET(
LesDates;LIGNE(INDIRECT(MIN(B2:B8)&":"&MAX(C2:C8)));
LeJour;JOURSEM(LesDates;2);
FILTRE(LesDates;(LeJour<>3)*(LeJour<6)*(NB.SI(D2:D13;LesDates)=0)*(NB.SI.ENS(B2:B8;"<="&LesDates;C2:C8;">="&LesDates)=0);"")
)

Cordialement

 

[danielco]

Bonjour à tous,
Merci à Efgé d'avoir signalé mon erreur. Voici ma formule corrigée :

=LET(tbl;BYROW(SEQUENCE(C8-C2-2;1;C2+1);LAMBDA(x;SI(ET(ESTNA(EQUIVX(x;D2:D13));ET(JOURSEM(x)>1;JOURSEM(x)<7;JOURSEM(x)<>4);SOMME((B2:B8<x)*(C2:C8>x))=0);x;"")));FILTRE(tbl;tbl<>""))

(utilisation de C2:C8 au lieu de D213)
mea culpa