Excel 2016 : suivre les congés qui chevauchent deux mois (SUMPRODUCT, Power Query, TCD)

Votre modèle gratuit « Employee Leave Tracker » sous Excel 2016 ne ventile pas les congés qui chevauchent deux mois ? Voici trois méthodes concrètes (formule, Power Query + TCD, colonne d’aide) pour totaliser correctement par mois et par année, avec pas‑à‑pas et formules prêtes à coller.

Sommaire

Problématique

Le modèle Employee Leave Tracker fournit une table d’absences avec notamment :

LeaveTracker[Employee Name] (nom de l’employé),
LeaveTracker[Start Date] (date de début),
LeaveTracker[End Date] (date de fin),
LeaveTracker[Type of Leave] (type d’absence),
LeaveTracker[Days] (nombre de jours saisi/calculé pour l’absence).

La formule classique suivante — ou toute variante à base de SUMIFS — somme seulement les absences entièrement comprises dans le mois :

=SUMIFS(LeaveTracker[Days], LeaveTracker[Employee Name], B4,
        LeaveTracker[Start Date], "&gt;="&amp;DATE(2024,1,1),
        LeaveTracker[End Date],   "&lt;"&amp;DATE(2024,2,1),
        LeaveTracker[Type of Leave], 'Leave Types'!$B$5)

Conséquence : un congé du 26 janvier au 4 février n’est pas compté pour janvier, car son End Date (4 février) sort des bornes du mois. Le total mensuel devient donc faux, alors que l’annuel reste plausible.

Pourquoi `SUMIFS` échoue sur les chevauchements

SUMIFS additionne les lignes répondant simultanément à des critères. Ici, vous filtrez des lignes dont Start Date ≥ 1^er janvier ET End Date < 1^er février. Or une absence qui chevauche le mois possède un End Date au‑delà du dernier jour du mois : elle est exclue, même si une partie des jours appartient bien au mois. Il faut donc passer d’une logique « tout ou rien » à une logique « combien de jours tombent dans la fenêtre ? ».

Solutions éprouvées et améliorées

Approche	Principe	Avantages	Limites / Points d’attention
A. Formule SUMPRODUCT « chevauchement »	Pour chaque absence, calcule le nombre de jours du mois via `MIN`/`MAX` puis additionne : `=SUMPRODUCT( (Emp=$B$4) * (Type='Leave Types'!$B$5) * (MIN(FinMois, [End]) - MAX(DébutMois, [Start]) + 1) * ([End]>=DébutMois) * ([Start]<=FinMois) )`	100 % compatible Excel 2016. Aucun objet ajouté. Résultat instantané pour un mois ou une année.	Peut devenir lourd sur des milliers d’absences (mais reste acceptable tant qu’on évite les références de colonne entière). Bien fixer les références absolues (`$`).
B. Power Query + Modèle de données + TCD	Déploie chaque absence en lignes journalières puis agrège dans un Tableau croisé dynamique (Année/Mois, Employé, Type…).	Mise à jour par simple Actualiser. Filtrage multi‑critères natif. Très performant et maintenable à grande échelle.	Nécessite de Débloquer le fichier, le placer dans un emplacement approuvé et activer le contenu pour rafraîchir. Demande un minimum de prise en main de Power Query.
C. Colonne d’aide « Jours dans le mois »	Ajoute dans la table une colonne qui calcule, pour le mois demandé, la part de chaque absence tombant dans ce mois. Les totaux se font ensuite par `SUMIFS` classiquement.	Très lisible pour les non‑experts. Ne requiert ni TCD ni Power Query. Idéal si l’IT bloque les compléments.	Allonge la table (1 colonne de plus) et, pour piloter 12 mois, implique soit 12 colonnes, soit un paramètre de mois.

Approche A — Formule `SUMPRODUCT` « chevauchement de périodes »

Principe

Pour un mois donné, on borne la période par DébutMois = 1^er jour et FinMois = dernier jour. Pour chaque ligne, la portion appartenant au mois vaut :

max(0; min(FinMois; EndDate) − max(DébutMois; StartDate) + 1)

On somme ensuite cette quantité pour les lignes correspondant à l’employé et au type choisis.

Préparer une zone de paramètres (recommandé)

B2 : année (ex. 2024),
B3 : mois (1 à 12),
B4 : employé (même orthographe que dans [Employee Name]),
‘Leave Types’!B5 : type de congé à analyser (ex. Vacation).

Ajoutez en C2 la date du 1^er du mois :

=DATE($B$2; $B$3; 1)

Et définissez Fin du mois via EOMONTH :

=EOMONTH($C$2; 0)

Astuce séparateurs : si votre Excel utilise la virgule « , », remplacez les « ; » par des « , » dans les formules ci‑dessous.

Formule mensuelle prête à l’emploi

=SUMPRODUCT(
  (LeaveTracker[Employee Name]=$B$4) *
  (LeaveTracker[Type of Leave]='Leave Types'!$B$5) *
  (LeaveTracker[End Date]>=$C$2) *
  (LeaveTracker[Start Date]<=EOMONTH($C$2;0)) *
  (MIN(LeaveTracker[End Date]; EOMONTH($C$2;0)) - MAX(LeaveTracker[Start Date]; $C$2) + 1)
)

Ce que fait la formule :

(Emp=...)*(Type=...) garde uniquement l’employé et le type ciblés.
(End>=DébutMois)*(Start<=FinMois) élimine les lignes sans chevauchement avec le mois.
MIN(...)-MAX(...)+1 retourne le nombre de jours qui tombent dans le mois pour chaque absence retenue.

Test rapide : pour un congé du 26/01/2024 au 04/02/2024, la formule renvoie 6 en janvier et, en changeant C2 vers 01/02/2024, renvoie 4 en février. Le total annuel reste 10.

Formule annuelle (ventiler sur toute l’année civile)

Pour totaliser la part tombant dans l’année B2, remplacez la fenêtre par 1/1/année → 31/12/année :

=SUMPRODUCT(
  (LeaveTracker[Employee Name]=$B$4) *
  (LeaveTracker[Type of Leave]='Leave Types'!$B$5) *
  (LeaveTracker[End Date]>=DATE($B$2;1;1)) *
  (LeaveTracker[Start Date]<=DATE($B$2;12;31)) *
  (MIN(LeaveTracker[End Date]; DATE($B$2;12;31)) - MAX(LeaveTracker[Start Date]; DATE($B$2;1;1)) + 1)
)

Variantes utiles

Sans filtrer le type : supprimez la condition sur [Type of Leave].
Tous employés : retirez la condition [Employee Name]=$B$4 pour obtenir un total global du mois.
Fin manquante (congé en cours) : coiffez [End Date] par IF([@[End Date]]=""; TODAY(); [@[End Date]]) dans la formule, ou imposez la saisie d’une fin provisoire.
Jours ouvrés seulement : préférez l’approche B (calendrier des jours ouvrés) ou, en approche C, utilisez NETWORKDAYS.INTL dans la colonne d’aide (cf. ci‑dessous). Évitez d’appeler NETWORKDAYS.INTL en vecteur dans SUMPRODUCT sous Excel 2016.

Performance & fiabilité

Références : ciblez la table structurée (LeaveTracker[Start Date]) et non des colonnes entières (A:A).
Types : assurez‑vous que Start/End Date sont bien au format Date.
Inclusif : le +1 rend la borne de fin incluse. Sans lui, un congé d’un seul jour compterait 0.

Approche B — Power Query + Modèle de données + TCD (recommandée pour le reporting)

Vue d’ensemble

On transforme chaque absence en autant de lignes qu’il y a de dates dans l’intervalle Start→End. Cette table quotidienne est reliée à un Calendrier (dimension Date). Un TCD (Pivot Table) basé sur le Modèle de données agrège alors par Année/Mois/Employé/Type sans la moindre ambiguïté.

Étape 1 — Débloquer et approuver le fichier

Clic droit sur le fichier → Propriétés → cochez Débloquer → OK.
Déplacez le fichier dans un emplacement approuvé (Fichier → Options → Centre de gestion de la confidentialité → Paramètres → Emplacements approuvés).
Rouvrez le fichier et cliquez sur Activer le contenu si demandé.

Étape 2 — Charger la table des congés dans Power Query

Placez le curseur dans la table LeaveTracker (elle est déjà « Table » dans le modèle).
Données → À partir d’un tableau/plage (ou Obtenir et transformer selon votre Excel).
Vérifiez les types de colonnes (Date pour Start/End).

Étape 3 — Déployer chaque absence en lignes journalières

Ajouter une colonne → Colonne personnalisée :

= List.Dates([Start Date], Duration.Days([End Date] - [Start Date]) + 1, #duration(1,0,0,0))

Cliquez sur l’icône d’extension à droite de la colonne créée et choisissez Développer en nouvelles lignes : vous obtenez une ligne par jour de congé.
Renommez cette colonne en Date et forcez le type Date.
Conservez au minimum : Employee Name, Type of Leave, Date, Start Date, End Date (les dates d’origine aident au contrôle).

Étape 4 — Créer une table Calendrier (dimension Date)

Dans Power Query : Accueil → Nouvelle source → Autre source → Requête vide. Éditez la formule et collez :

let
    // Ajustez les bornes à votre historique
    Start = #date(2020,1,1),
    End   = #date(2030,12,31),
    Lst   = List.Dates(Start, Duration.Days(End - Start) + 1, #duration(1,0,0,0)),
    Tbl   = Table.FromList(Lst, Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
    Typ   = Table.TransformColumnTypes(Tbl,{{"Date", type date}}),
    AddY  = Table.AddColumn(Typ, "Année", each Date.Year([Date]), Int64.Type),
    AddM  = Table.AddColumn(AddY, "MoisN", each Date.Month([Date]), Int64.Type),
    AddMm = Table.AddColumn(AddM, "Mois", each Date.ToText([Date], "[$-fr-FR]mmmm"), type text),
    AddD  = Table.AddColumn(AddMm, "JourSemaineN", each Date.DayOfWeek([Date], Day.Sunday), Int64.Type),
    AddW  = Table.AddColumn(AddD, "EstWeekend", each [JourSemaineN] = 0 or [JourSemaineN] = 6, type logical)
in
    AddW

Optionnel : importez une table Holidays (jours fériés) et marquez EstFerie via une jointure, puis calculez EstOuvré = NOT EstWeekend AND NOT EstFerie.

Étape 5 — Charger au Modèle de données et relier

Pour la table journalière (congés déployés) : Charger vers… → cochez Ajouter ces données au Modèle de données (pas besoin de l’afficher en feuille si vous préférez).
Faites de même pour Calendrier.
Dans Données → Relations, créez une relation : Conges[Date] → Calendrier[Date].

Étape 6 — Créer le TCD

Insertion → Tableau croisé dynamique → Utiliser ce classeur Modèle de données.
Dans les champs du TCD :
- Lignes : Calendrier[Année], puis Calendrier[MoisN] (triez croissant) et Calendrier[Mois] (pour l’intitulé lisible).
- Valeurs : Conges[Date] en Nombre (compte de lignes = jours).
- Filtres/Slicers : Employee Name, Type of Leave, éventuellement EstOuvré.

Bonus : ajoutez une segmentation (Slicer) sur Employé, Année et Type pour explorer les données d’un clic.

Atouts de l’approche B

Exactitude : chaque jour correspond à une ligne ; aucune ambiguïté sur les chevauchements.
Flexibilité : vous pouvez exclure week‑ends et/ou fériés en filtrant EstOuvré.
Performance : le déploiement se fait une fois au rafraîchissement ; les TCD sont très rapides.

Approche C — Colonne d’aide « Jours dans le mois »

Principe

On ajoute une colonne calculée qui, pour un mois paramétré en C2 (1^er du mois), renvoie 0 si l’absence ne touche pas le mois, sinon le nombre de jours du mois couverts par la ligne. Les totaux s’obtiennent ensuite par SUMIFS.

Colonne d’aide (calendrier civil)

Dans la table LeaveTracker, créez la colonne DaysInMonth (ou « JoursDansMois ») avec la formule suivante :

=IF(
  AND([@[End Date]]>=$C$2; [@[Start Date]]<=EOMONTH($C$2;0));
  MIN([@[End Date]]; EOMONTH($C$2;0)) - MAX([@[Start Date]]; $C$2) + 1;
  0
)

Le total mensuel par employé et type devient trivial :

=SUMIFS(
  LeaveTracker[DaysInMonth];
  LeaveTracker[Employee Name]; $B$4;
  LeaveTracker[Type of Leave]; 'Leave Types'!$B$5
)

Variante « jours ouvrés » (week‑ends/feriés exclus)

Comme la colonne d’aide s’évalue ligne par ligne, on peut employer NETWORKDAYS.INTL (qui n’est pas pratique en vecteur). Supposez une table Holidays avec la colonne [Date] listant vos fériés :

=MAX(0;
  NETWORKDAYS.INTL(
    MAX([@[Start Date]]; $C$2);
    MIN([@[End Date]];   EOMONTH($C$2;0));
    1;                    // 1 = sam-dim week-end
    Holidays[Date]
  )
)

Pour compter tous les jours (24/7/365), utilisez le masque "0000000" (aucun jour week‑end) ou conservez la première version à base de MIN/MAX.

Fenêtre annuelle avec la colonne d’aide

Définissez E2 = début d’année : =DATE($B$2;1;1) et F2 = fin d’année : =DATE($B$2;12;31). Ajoutez une colonne DaysInYear avec :

=IF(
  AND([@[End Date]]>=$E$2; [@[Start Date]]<=$F$2);
  MIN([@[End Date]]; $F$2) - MAX([@[Start Date]]; $E$2) + 1;
  0
)

Puis un simple SUMIFS sur DaysInYear pour l’employé et le type visés.

Guide de mise en œuvre

Choisir l’approche

Besoins simples & effectif limité : Approche A (formule SUMPRODUCT), la plus rapide à déployer.
Reporting avancé / volumétrie élevée : Approche B (Power Query + Modèle + TCD), la plus robuste.
Environnement verrouillé (sans PQ) : Approche C (colonne d’aide) est la plus « IT‑friendly ».

Sécurité des fichiers (Approche B)

Clic droit → Propriétés → Débloquer.
Copiez le fichier dans un emplacement approuvé (Fichier → Options → Centre de gestion de la confidentialité → Emplacements approuvés).
Ouvrez et Activez le contenu pour permettre les rafraîchissements.

Paramétrage du calendrier (Approche B)

La table Calendrier doit couvrir toutes les années utiles (passé et futur proche).
Pour un service 24/7/365, ne filtrez pas EstWeekend ni EstFerie dans le TCD.
Pour les jours ouvrés, cochez le filtre EstOuvré dans le TCD (ou créez un segment).

Vérifications rapides

Créez un congé test du 26/01 au 04/02. Vous devez obtenir 6 jours en janvier et 4 en février (total annuel inchangé).
Testez un congé sur 31/12 → 02/01 : 1 jour en décembre et 2 en janvier.
Vérifiez qu’aucune ligne ne retourne de valeur négative dans la colonne d’aide (sinon, la condition d’inclusion est manquante).

FAQ opérationnelle

Dois‑je utiliser `[Days]` ou recalculer les jours ?

[Days] est souvent saisi ou calculé par absence (et parfois en jours ouvrés). Pour ventiler mensuellement, il vaut mieux recalculer la part du mois via MIN/MAX (Approches A et C) ou via le déploiement journalier (Approche B). Ainsi, les chevauchements sont gérés correctement et de façon homogène.

Comment exclure les week‑ends et fériés ?

Deux voies simples :

Approche C : utilisez NETWORKDAYS.INTL dans la colonne d’aide (voir plus haut) avec un masque adapté et une table Holidays.
Approche B : marquez EstWeekend/EstFerie dans la table Calendrier et filtrez EstOuvré dans le TCD.

Peut‑on obtenir un tableau par Employé × Mois sur 12 colonnes ?

Oui. Créez une matrice avec 12 cellules mois paramètre (C2 = jan., D2 = fév., …) et copiez la formule de l’Approche A en pointant la cellule du mois correspondant. Sinon, l’Approche B produit ce visuel en deux clics via un TCD.

Et si un congé n’a pas d’End Date ?

Standardisez la saisie (Start=End pour une journée) ou coiffez [End Date] par TODAY() dans la formule. L’idéal est de valider les données pour éviter les blancs sur la date de fin.

Erreurs fréquentes et comment les corriger

Symptôme	Cause probable	Correction
Total mensuel = 0 alors qu’il y a des congés	Start/End au format texte ou cellule paramètre (C2) hors du mois attendu	Convertissez les colonnes en Date et vérifiez C2 / EOMONTH(C2;0)
Valeurs négatives dans la colonne d’aide	Oubli du test d’inclusion (`End ≥ DébutMois` et `Start ≤ FinMois`)	Ajoutez les tests OU utilisez la version avec `IF(...;...;0)`
Ordre des mois incorrect dans le TCD	Tri alphabétique sur « janvier, février, … »	Ajoutez `MoisN` (1–12) dans la table Calendrier et triez sur ce champ
Message « Impossible de grouper la sélection »	Dates non contiguës ou champ Date en texte dans le TCD	Utilisez la dimension Calendrier et ses colonnes Année/Mois plutôt que le groupement à la volée
TCD ne se rafraîchit pas	Fichier non approuvé / contenu désactivé	Débloquez le fichier, placez‑le en emplacement approuvé, puis Actualiser

Bonnes pratiques SEO & maintenance (spécifique à ce cas d’usage)

Nommez clairement vos zones (ex. Paramètres_Annee, Paramètres_Mois), commentez les cellules qui portent une date de période (C2 : « 1er jour du mois »).
Évitez les noms d’employé ambigus ; utilisez une liste déroulante basée sur les valeurs distinctes de [Employee Name].
Ne mélangez pas les unités : si vous passez aux jours ouvrés, harmonisez tout le classeur (formules, contrôles, TCD).
Documentez la logique (1 page « README » dans le classeur) et conservez un onglet de tests avec les cas de chevauchement typiques.

Exemples concrets (à copier/coller)

Somme mensuelle par employé et type (Approche A)

=SUMPRODUCT(
 (LeaveTracker[Employee Name]=$B$4) *
 (LeaveTracker[Type of Leave]='Leave Types'!$B$5) *
 (LeaveTracker[End Date]>=$C$2) *
 (LeaveTracker[Start Date]<=EOMONTH($C$2;0)) *
 (MIN(LeaveTracker[End Date];EOMONTH($C$2;0)) - MAX(LeaveTracker[Start Date];$C$2) + 1)
)

Colonne d’aide « JoursDansMois » (Approche C)

=IF(
 AND([@[End Date]]>=$C$2; [@[Start Date]]<=EOMONTH($C$2;0));
 MIN([@[End Date]]; EOMONTH($C$2;0)) - MAX([@[Start Date]]; $C$2) + 1;
 0
)

Power Query — Déploiement journalier (Approche B)

= List.Dates([Start Date], Duration.Days([End Date] - [Start Date]) + 1, #duration(1,0,0,0))

Recommandation finale

Vous voulez corriger le modèle sans dépendre d’outils additionnels : implémentez la formule SUMPRODUCT (Approche A). Elle gère parfaitement les chevauchements mensuels et annuels avec Excel 2016.
Vous souhaitez automatiser des rapports mensuels/annuels, par type, équipe, ou jours ouvrés : adoptez Power Query + Modèle de données + TCD (Approche B). Suivez la procédure de déblocage pour permettre le rafraîchissement.
Contrainte IT ou besoin de transparence pédagogique : la colonne d’aide (Approche C) est un excellent compromis.

Dans tous les cas, vos congés s’étalant sur deux mois seront correctement ventilés ; vos totaux mensuels deviendront fiables, comparables à l’année et exploitables pour un reporting propre.

Checklist de déploiement rapide

Créez la cellule C2 = =DATE(Année;Mois;1) et vérifiez EOMONTH(C2;0).
Collez la formule Approche A dans la cellule de total mensuel.
Testez avec deux absences de chevauchement (jan→fév et déc→jan).
Si besoin de filtres avancés (équipe, manager, jour ouvré) : mettez en place Approche B.
Documentez et verrouillez les plages de paramètres.

Modèle de tableau de bord mensuel (idée de mise en forme)

Sur une feuille « Synthèse », construisez une grille :

Employé	Jan	Fév	Mar	Avr	Mai	Juin	Juil	Août	Sep	Oct	Nov	Déc	Total Année
Nom	Utilisez la formule Approche A en pointant la cellule « 1er du mois » correspondante												Somme des 12 mois

Ajoutez une validation de données sur le Type de congé (liste basée sur 'Leave Types'!B:B) et mettez à jour toutes les formules par liaison à cette cellule unique.

En résumé : remplacez la logique « tout ou rien » par un calcul de chevauchement de dates. La formule SUMPRODUCT (Approche A) suffit dans la majorité des cas et reste compatible Excel 2016. Pour l’analytique à grande échelle, Power Query + Modèle de données + TCD (Approche B) vous donnera un reporting robuste, maintenable et extensible (jours ouvrés, fériés, équipes, etc.). La colonne d’aide (Approche C) offre un excellent compromis lorsque l’environnement est restrictif.

Excel 2016 : suivre les congés qui chevauchent deux mois (SUMPRODUCT, Power Query, TCD)

Problématique

Pourquoi SUMIFS échoue sur les chevauchements

Solutions éprouvées et améliorées

Approche A — Formule SUMPRODUCT « chevauchement de périodes »

Principe

Préparer une zone de paramètres (recommandé)

Formule mensuelle prête à l’emploi

Formule annuelle (ventiler sur toute l’année civile)

Variantes utiles

Performance & fiabilité

Approche B — Power Query + Modèle de données + TCD (recommandée pour le reporting)

Vue d’ensemble

Étape 1 — Débloquer et approuver le fichier

Étape 2 — Charger la table des congés dans Power Query

Étape 3 — Déployer chaque absence en lignes journalières

Étape 4 — Créer une table Calendrier (dimension Date)

Étape 5 — Charger au Modèle de données et relier

Étape 6 — Créer le TCD

Atouts de l’approche B

Approche C — Colonne d’aide « Jours dans le mois »

Principe

Colonne d’aide (calendrier civil)

Variante « jours ouvrés » (week‑ends/feriés exclus)

Fenêtre annuelle avec la colonne d’aide

Guide de mise en œuvre

Choisir l’approche

Sécurité des fichiers (Approche B)

Paramétrage du calendrier (Approche B)

Vérifications rapides

FAQ opérationnelle

Dois‑je utiliser [Days] ou recalculer les jours ?

Comment exclure les week‑ends et fériés ?

Peut‑on obtenir un tableau par Employé × Mois sur 12 colonnes ?

Et si un congé n’a pas d’End Date ?

Erreurs fréquentes et comment les corriger

Bonnes pratiques SEO & maintenance (spécifique à ce cas d’usage)

Exemples concrets (à copier/coller)

Somme mensuelle par employé et type (Approche A)

Colonne d’aide « JoursDansMois » (Approche C)

Power Query — Déploiement journalier (Approche B)

Recommandation finale

Checklist de déploiement rapide

Modèle de tableau de bord mensuel (idée de mise en forme)

Excel 2016 : suivre les congés qui chevauchent deux mois (SUMPRODUCT, Power Query, TCD)

Pourquoi `SUMIFS` échoue sur les chevauchements

Approche A — Formule `SUMPRODUCT` « chevauchement de périodes »

Approche B — Power Query + Modèle de données + TCD (recommandée pour le reporting)

Étape 2 — Charger la table des congés dans Power Query

Approche C — Colonne d’aide « Jours dans le mois »

Variante « jours ouvrés » (week‑ends/feriés exclus)

Sécurité des fichiers (Approche B)

Paramétrage du calendrier (Approche B)

Dois‑je utiliser `[Days]` ou recalculer les jours ?

Comment exclure les week‑ends et fériés ?

Peut‑on obtenir un tableau par Employé × Mois sur 12 colonnes ?

Et si un congé n’a pas d’End Date ?

Somme mensuelle par employé et type (Approche A)

Colonne d’aide « JoursDansMois » (Approche C)

Power Query — Déploiement journalier (Approche B)