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 FredMa,
Un essai en PJ en deux steps:
1- Liste globale des jours où on repère les jours à supprimer ( colonne G ) avec :

=SI(OU(SI(OU(JOURSEM(F2;2)=3;JOURSEM(F2;2)>=6;NB.SI($D$2:$D$32;F2)>0);1;0);SI(OU(ET(F2>=$B$2;F2<=$C$2);ET(F2>=$B$3;F2<=$C$3);ET(F2>=$B$4;F2<=$C$4);ET(F2>=$B$5;F2<=$C$5);ET(F2>=$B$6;F2<=$C$6);ET(F2>=$B$7;F2<=$C$7);ET(F2>=$B$8;F2<=$C$8));1;0))=FAUX;MAX($G$1:G1)+1;"")

2- La liste des jours de classe avec :

=SIERREUR(INDEX(F:F;EQUIV(LIGNE()-1;G:G;0));"")

 

[ALS35]

Bonjour à tous,
Hello sylvanu,
Un essai en développant les listes des vacances scolaires (colonnes D à I) et en utilisant SERIE.JOUR.OUVRE.INTL en M3 :

=SERIE.JOUR.OUVRE.INTL(M2;1;"0010011";$D$2:$J$17)

et à recopier vers le bas
Cordialement

 

[FredMa]

Merci à vous deux pour ces solutions !
Celle de Sylvanu me semble plus facile à utiliser car pas besoin de développer les listes des vacances.
En tout cas elle devrait plus facilement s'intégrer à ce que je veux faire :

XL 2019 - Calendrier scolaire

Bonjour, Je souhaiterais générer un calendriers scolaire et différents plannings à partir d'un import ICAL des vacances scolaires (https://www.data.gouv.fr/fr/datasets/le-calendrier-scolaire/) Je pensais partir d'un script créé par @leakim...

excel-downloads.com

 

[FredMa]

1- Liste globale des jours où on repère les jours à supprimer ( colonne G ) avec :

=SI(OU(SI(OU(JOURSEM(F2;2)=3;JOURSEM(F2;2)>=6;NB.SI($D$2:$D$32;F2)>0);1;0);SI(OU(ET(F2>=$B$2;F2<=$C$2);ET(F2>=$B$3;F2<=$C$3);ET(F2>=$B$4;F2<=$C$4);ET(F2>=$B$5;F2<=$C$5);ET(F2>=$B$6;F2<=$C$6);ET(F2>=$B$7;F2<=$C$7);ET(F2>=$B$8;F2<=$C$8));1;0))=FAUX;MAX($G$1:G1)+1;"")

Le test de l'encadrement des vacances se fait ligne à ligne dans ta proposition :
ET(F2>=$B$2;F2<=$C$2)
ET(F2>=$B$3;F2<=$C$3)
ET(F2>=$B$4;F2<=$C$4)
or, ce nombre de lignes peut être important (dates sur plusieurs années) car fichier ical importé.
Y a pas moyen de faire une cherche récurrente jusqu'à trouver une ligne vide (fin des données) ?

Fred

 

[sylvanu]

Bonsoir,
Acceptez vous le VBA ? Ce serait plus souple et plus simple.

 

[FredMa]

Oui oui. C'est ce que je suggérais ici : https://excel-downloads.com/threads/calendrier-scolaire.20077523/page-2#posts mais je n'en avais pas les compérences ;(

 

[sylvanu]

Bonsoir,
Un essai en PJ. Le nombre de jours fériés, et le nombre de plage de vacances ne sont plus limités.
J'ai conservé le planning global et le planning des jours de classe. Cela permet une vérification.
Le planning est reconstruit par appui sur le bouton.

 

[FredMa]

Merci Sylvanu... Je vais essayer de comprendre ta macro.
Cerise sur le gâteau, il faudrait que je puisse définir la valeur la première journée de classe en fonction de l'année scolaire choisie (dans la cellule L2).
La valeur JoursTravail(1, 1) serait alors recherchée sur la ligne "Vacances d'Été (prérentrée Enseignants )" de l'année choisie... Ou alors en 2 étapes pour n'extraire que les dates de vacances correspondant à l'année scolaire choisie.

 

[sylvanu]

Bonjour,
Voir PJ.
Attention au piège. Avec l'import la chaine "Vacances d'Été (prérentrée Enseignants )" est suivi d'un "caractère parasite" qui est CHR(13) ( retour à la ligne ), je m'en suis débarrassé avec :

If Mid(PlanningVac(i, 1), 1, 40) = TagDébut Then

 

[FredMa]

C'est top !
A voir s'il serait possible dans cette macro :
1- de prendre en compte le pont de l'ascension : l'ascension est toujours un jeudi, il faudrait donc supprimer le vendredi d'après. Pas évident ?
2- afficher les jours de classe avec découpage par semaine et période.
(un test dans la macro ConstruitCalendrier pour ajouter une ligne Période x dès que l'on est en fin de vacances ?)
3- regrouper les jours de classe par semaine : pas facile car ne commence pas toujours par un lundi et ne finit pas toujours par un vendredi
4- voir comment afficher les dates de vacances début (colonne B) et reprise des cours (colonne C+1) de l'année scolaire (le point 2 permet peut-être de mettre dans des variables le début et la fin de chaque période et de les exploiter pour le point 4)
- lancer la macro ConstruirePlanning lors du changement d'année : modification de la valeur de la variable An.
(lancement de la macro lors du clic sur le module de contrôle ?)
Voici mes modifs.

Fred

 

[sylvanu]

Bonjour FredMa,
J'ai l'impression que les demandes vont se succéder à l'infini.
Je laisse à d'autres le plaisir de continuer.

 

[FredMa]

Là on arrivait au bout mais merci beaucoup Sylvanu pour ce beau coup de pouce !

 

[sylvanu]

1- de prendre en compte le pont de l'ascension : l'ascension est toujours un jeudi, il faudrait donc supprimer le vendredi d'après. Pas évident ?

Dans vos jours fériés vous savez calculer le jour de l'ascension, en B9.
Donc le vendredi de l'ascension peut être considéré comme férié, vous le rajoutez : en B30 : =B9+1

 

[FredMa]

Une demande de moins