Comment savoir si une date tombe pendant les vacances scolaires de ma zone dans Excel ?

Sans l’add-in

Les vacances scolaires ne se calculent pas : ce sont des dates publiées par le ministère de l’Éducation nationale. Le plus fiable est de les importer avec Power Query depuis le jeu de données officiel « Le calendrier scolaire » (nouvel onglet) :

1. Dans un nouveau classeur, renommez une feuille Vacances.
2. Données › Obtenir des données › À partir d’autres sources › Requête vide, puis Éditeur avancé, et collez la requête ci-dessous (zones A, B et C).
3. Fermer et charger dans… › Tableau › Feuille de calcul existante, cellule A1 de la feuille Vacances.

let
    Source = Csv.Document(
        Web.Contents(
            "https://data.education.gouv.fr/api/explore/v2.1/catalog/datasets/fr-en-calendrier-scolaire/exports/csv?limit=-1"
            & "&select=description,start_date,end_date,zones"
            & "&where=population%20in%20(%22-%22,%22%C3%89l%C3%A8ves%22)"
            & "%20and%20zones%20in%20(%22Zone%20A%22,%22Zone%20B%22,%22Zone%20C%22)"
        ),
        [Delimiter=";", Encoding=65001]),
    Entetes = Table.PromoteHeaders(Source),
    Vacances = Table.SelectRows(Entetes, each Text.StartsWith([description], "Vacances")),
    Dates = Table.TransformColumns(Vacances, {
        {"start_date", each Date.From(DateTimeZone.ToLocal(DateTimeZone.FromText(_))), type date},
        {"end_date",   each Date.From(DateTimeZone.ToLocal(DateTimeZone.FromText(_))), type date}}),
    Codes = Table.TransformColumns(Dates, {{"zones", each Text.Replace(_, "Zone ", ""), type text}}),
    Unique = Table.Distinct(Codes),
    Renomme = Table.RenameColumns(Unique, {{"zones", "Zone"}, {"description", "Vacances"}, {"start_date", "Début"}, {"end_date", "Reprise"}})
in
    Renomme

La feuille contient alors, de A à D : le nom des vacances, le premier jour, le jour de reprise (qui n’est plus en vacances) et la zone. Ces nettoyages sont nécessaires : le jeu de données contient une ligne par académie, des dates exprimées en UTC (décalées d’un jour sans conversion) et des lignes qui ne sont pas des vacances (ponts, journées banalisées). La requête suppose un ordinateur réglé sur l’heure de Paris.

Pour une date en A2 et une zone ("A", "B" ou "C") en E1 :

=NB.SI.ENS(
  Vacances!$B$2:$B$1000;"<="&A2;
  Vacances!$C$2:$C$1000;">"&A2;
  Vacances!$D$2:$D$1000;$E$1
)>0

Les retours à la ligne ne servent qu’à la lecture : le bouton « Copier » copie la formule sur une seule ligne, prête à coller dans une cellule (collée dans la barre de formule, la version sur plusieurs lignes est aussi acceptée par Excel).

renvoie VRAI pendant les vacances. Pour le nom des vacances :

=SIERREUR(
  INDEX(
    Vacances!$A$2:$A$1000;
    EQUIV(
      1;
      INDEX(
        (Vacances!$B$2:$B$1000<=A2)
        *(Vacances!$C$2:$C$1000>A2)
        *(Vacances!$D$2:$D$1000=$E$1);
        0
      );
      0
    )
  );
  ""
)

Pour la Corse et l’outre-mer, les libellés du jeu de données sont plus variés : la requête ci-dessus demanderait d’autres filtres.

Avec l’add-in

Calendrier Excel Add-in

=VACANCES.EST_JOUR(A2; "B")

renvoie VRAI si la date tombe pendant des vacances de la zone B, FAUX sinon. Les zones acceptées sont "A", "B", "C", "Corse", "Guadeloupe", "Guyane", "Martinique", "Mayotte" et "Reunion" (sans accent). Pour le nom des vacances (« Vacances de Noël »…), utilisez VACANCES.NOM(A2; "B"). Détails : VACANCES.EST_JOUR (nouvel onglet) et VACANCES.NOM (nouvel onglet).

Ce que l’add-in change : Les dates des vacances sont déjà nettoyées, pour neuf zones, et la fonction signale par #N/A une date qu’elle ne connaît pas. Découvrir Calendrier Excel Add-in

Comment choisir la zone dans une liste déroulante ?

Sans l’add-in

Dans une cellule (par exemple E1), ajoutez une validation de données de type « Liste » avec les valeurs A,B,C, puis utilisez les formules ci-dessus : elles lisent la zone en $E$1. Changer la zone recalcule tout le classeur. Seules les zones A, B et C sont disponibles avec la requête Power Query proposée.

Avec l’add-in

Même validation de données, avec les valeurs A,B,C,Corse,Guadeloupe,Guyane,Martinique,Mayotte,Reunion, puis :

=VACANCES.EST_JOUR(A2; $E$1)

Le même principe fonctionne dans une règle de mise en forme conditionnelle pour griser les vacances d’un planning.

Que se passe-t-il pour une date que le calendrier des vacances ne connaît pas ?

Sans l’add-in

Avec les formules ci-dessus, une date qui n’est pas dans le tableau reçoit FAUX : rien n’indique que l’information est absente, et le résultat est faux sans qu’Excel le signale. Pour le détecter, comparez la date aux bornes du tableau :

=SI(
  OU(A2<MIN(Vacances!$B$2:$B$1000);A2>=MAX(Vacances!$C$2:$C$1000));
  "date non couverte";
  NB.SI.ENS(
    Vacances!$B$2:$B$1000;"<="&A2;
    Vacances!$C$2:$C$1000;">"&A2;
    Vacances!$D$2:$D$1000;$E$1
  )>0
)

Avec l’add-in

Chaque zone a une période couverte, indiquée dans la fenêtre Données de l’add-in. Pour une date en dehors, les fonctions de vacances renvoient #N/A : elles ne répondent jamais « pas de vacances » à la place d’une date qu’elles ne connaissent pas. Pour afficher un texte à la place :

=SI.NON.DISP(VACANCES.EST_JOUR(A2; "A"); "date non couverte")

Sources

• Le calendrier scolaire – data.gouv.fr (nouvel onglet) (ministère de l’Éducation nationale, Licence Ouverte 2.0) : jeu de données source.
• Jeu de données fr-en-calendrier-scolaire – data.education.gouv.fr (nouvel onglet) : même jeu de données, interrogé par l’API utilisée dans la requête.
• Web.Contents – Power Query M – Microsoft Learn (nouvel onglet) : fonction Power Query qui lit l’adresse du jeu de données.
• Créer une liste déroulante – Support Microsoft (nouvel onglet) : liste déroulante de validation des données.