Le planning Excel reste l'outil de référence dans la majorité des entreprises françaises pour gérer les congés, organiser les projets ou suivre la production. Pourtant, la plupart des plannings sont encore construits manuellement : dates saisies à la main, mises en forme refaites chaque mois, compteurs de jours non automatisés. Résultat : du temps perdu, des erreurs, et des fichiers impossibles à maintenir.
Dans cet article, vous allez apprendre à construire un planning automatique Excel avec des formules (SEQUENCE, DATE, JOURSEM) et une macro VBA pour régénérer votre calendrier en un clic. Chaque étape est illustrée avec du code prêt à l'emploi.
Les différents types de planning Excel
Avant de construire quoi que ce soit, il est utile de distinguer les grands types de plannings que l'on rencontre en entreprise. Chacun a ses contraintes propres et ses fonctions Excel privilégiées.
Planning RH (congés et absences) — C'est le plus répandu. Il liste les salariés en lignes et les jours du mois en colonnes. Chaque cellule indique le statut : présent, congé payé, RTT, maladie, formation. Il nécessite des compteurs automatiques par salarié et par type d'absence.
Planning de projet (Gantt) — Basé sur la même structure colonne/date, il représente les tâches et leur durée. Les barres sont générées par mise en forme conditionnelle. Ce type de planning exige une gestion précise des dates de début et de fin ainsi que des dépendances entre tâches.
Planning de production — Il gère les ressources machines ou équipes sur des créneaux horaires. La granularité est souvent inférieure à la journée (demi-journée, heure). Les formules de calcul de charge et de taux d'occupation sont centrales.
Planning de permanence / astreinte — Il répartit des agents sur des plages horaires avec contraintes de rotation et de repos. VBA y est souvent indispensable pour automatiser l'affectation.
Quelle que soit la nature du planning, la structure de base est identique : une ligne d'en-tête avec les dates générées automatiquement, des lignes de données (salariés, tâches, machines), et des formules pour calculer les totaux. Maîtriser la génération automatique de la ligne de dates, c'est maîtriser 70 % du travail.
Créer un calendrier automatique avec SEQUENCE et DATE
La fonction SEQUENCE (disponible depuis Excel 2021 et Microsoft 365) est la clé de voûte d'un planning automatique. Elle génère une plage de valeurs consécutives à partir d'un point de départ — idéale pour créer une ligne de dates dynamique qui s'adapte au mois sélectionné.
Placez le mois et l'année dans deux cellules de paramètre, par exemple B1 (année) et C1 (mois). La formule suivante génère automatiquement tous les jours du mois en une seule expression :
=== Cellule de paramètre ===
B1 = 2024 (Année)
C1 = 6 (Mois)
=== En D1 : premier jour du mois ===
=DATE(B1;C1;1)
=== En E1 (étendue sur toute la ligne jusqu'à AH1) ===
=SEQUENCE(1;JOUR(DATE(B1;C1+1;0));DATE(B1;C1;1);1)
=== Formule pour afficher uniquement le numéro du jour ===
=JOUR(D1) → à copier sur toute la ligne de dates
=== Formule pour afficher le nom du jour abrégé ===
=TEXTE(D1;"jjj") → donne "lun", "mar", "mer"...
=== Nombre de jours dans le mois (pour adapter la plage) ===
=JOUR(DATE(B1;C1+1;0))La formule JOUR(DATE(B1;C1+1;0)) est une astuce classique : DATE(B1;C1+1;0) retourne le dernier jour du mois C1 en demandant le "jour 0" du mois suivant. Appliquez un format personnalisé jj sur la ligne de dates pour n'afficher que le numéro du jour, et jjj pour le nom abrégé.
Si vous utilisez une version d'Excel antérieure à 2021 (sans SEQUENCE), remplacez par :
=== En D1 : premier jour ===
=DATE($B$1;$C$1;1)
=== En E1 (formule à copier vers la droite) ===
=SI(MOIS(D1+1)=$C$1; D1+1; "")
=== Astuce : masquer les cellules vides de fin de mois ===
Mise en forme conditionnelle → règle "si la cellule est vide"
→ Police couleur blanche, bordure aucunePour un planning de juin 2024, saisir 2024 en B1 et 6 en C1. La formule SEQUENCE génère automatiquement 30 dates du 1er au 30 juin. En changeant C1 à 7, le calendrier passe immédiatement en juillet avec 31 jours. Aucune modification manuelle n'est nécessaire.
Vous souhaitez un planning Excel sur mesure pour votre équipe ?
Henrick Balon conçoit des fichiers Excel professionnels adaptés à vos processus RH, projet ou production.
Mise en forme conditionnelle : weekends, jours fériés, congés validés
Un planning lisible repose sur une mise en forme conditionnelle rigoureuse. Trois catégories de jours doivent être distinguées visuellement : les weekends (samedi et dimanche), les jours fériés et les congés ou absences validées.
La fonction JOURSEM retourne le numéro du jour de la semaine (1 = dimanche, 2 = lundi, …, 7 = samedi avec le type 1 par défaut). Pour identifier les weekends, la règle de mise en forme conditionnelle s'applique sur toute la plage de données :
=== Règle 1 : Mettre en surbrillance les samedis et dimanches ===
Plage d'application : $D$2:$AH$50
Formule : =JOURSEM(D$1;2)>=6
→ Type 2 : 1=lundi … 6=samedi, 7=dimanche
→ Remplissage : gris clair (#E8E8E8), Police grise
=== Règle 2 : Jours fériés (liste en colonne AL) ===
Plage d'application : $D$2:$AH$50
Formule : =NB.SI($AL$2:$AL$20;D$1)>0
→ Remplissage : orange clair (#FFF3CD), Police orange foncé
=== Règle 3 : Congé payé saisi (valeur "CP" dans la cellule) ===
Plage d'application : $D$2:$AH$50
Formule : =D2="CP"
→ Remplissage : vert (#D4EDDA), Police verte (#155724)
=== Règle 4 : RTT ===
Formule : =D2="RTT"
→ Remplissage : bleu clair (#D1ECF1)
=== Règle 5 : Maladie ===
Formule : =D2="MAL"
→ Remplissage : rouge clair (#F8D7DA)
=== Ordre d'application des règles (priorité haute → basse) ===
1. Maladie
2. Congé payé
3. RTT
4. Jours fériés
5. WeekendsPour la liste des jours fériés, créez un onglet Paramètres avec toutes les dates fériées de l'année en colonne. La formule NB.SI compare chaque date de la ligne d'en-tête à cette liste. Si la date y figure, la mise en forme s'applique. Cette approche est maintenable : il suffit de mettre à jour la liste chaque année.
Pour les jours fériés mobiles (Pâques, Lundi de Pentecôte…), calculez-les dynamiquement :
=== Calcul de la date de Pâques (algorithme de Butcher-Meeus) ===
En supposant l'année en B1 :
A = MOD(B1;19)
B = ENT(B1/100)
C = MOD(B1;100)
D = ENT(B/4)
E = MOD(B;4)
F = ENT((B+8)/25)
G = ENT((B-F+1)/3)
H = MOD(19*A+B-D-G+15;30)
I = ENT(C/4)
K = MOD(C;2)
L = MOD(32+2*E+2*I-H-K;7)
M = ENT((A+11*H+22*L)/451)
Mois_Paques = ENT((H+L-7*M+114)/31)
Jour_Paques = MOD(H+L-7*M+114;31)+1
=== Formule Excel simplifiée pour Pâques ===
(Utiliser un module VBA ou une formule composite selon votre version)
=== Jours fériés dérivés de Pâques ===
Lundi de Pâques = DATE_PAQUES + 1
Ascension = DATE_PAQUES + 39
Lundi Pentecôte = DATE_PAQUES + 50
=== Jours fériés fixes (à saisir en dur chaque année) ===
01/01 - Jour de l'An
01/05 - Fête du Travail
08/05 - Victoire 1945
14/07 - Fête Nationale
15/08 - Assomption
01/11 - Toussaint
11/11 - Armistice
25/12 - NoëlPlanning des congés et absences : suivi par salarié avec compteurs automatiques
Le planning de congés est le cas d'usage le plus fréquent en RH. L'objectif est de disposer, pour chaque salarié, d'un solde en temps réel : jours de CP posés, RTT consommés, jours restants. Ces compteurs doivent se mettre à jour automatiquement dès qu'une absence est saisie dans le planning.
La structure recommandée est la suivante :
- Colonne A : Nom du salarié
- Colonnes D à AH : Jours du mois (dates en ligne 1)
- Colonnes AJ à AN : Compteurs par type (CP posés, RTT, MAL, Formation, Solde restant)
Les compteurs se calculent avec NB.SI sur la ligne du salarié :
=== Exemple pour le salarié en ligne 5 (données en D5:AH5) ===
--- Congés payés posés ce mois ---
=NB.SI(D5:AH5;"CP")
--- RTT posés ce mois ---
=NB.SI(D5:AH5;"RTT")
--- Jours maladie ce mois ---
=NB.SI(D5:AH5;"MAL")
--- Total jours d'absence (hors weekends et fériés) ---
=NB.SI(D5:AH5;"CP")+NB.SI(D5:AH5;"RTT")+NB.SI(D5:AH5;"MAL")
+NB.SI(D5:AH5;"FOR")+NB.SI(D5:AH5;"CSS")
--- Solde CP restant (quota annuel en colonne AI) ---
=$AI5 - SOMME.SI(PlanningAnnee;A5;CP_Colonne)
=== Compteur cumulé sur l'année entière (multi-onglets) ===
=NB.SI(Janvier!D5:AH5;"CP")
+NB.SI(Février!D5:AH5;"CP")
+NB.SI(Mars!D5:AH5;"CP")
+...
=== Version dynamique avec tableau structuré (Tableau1) ===
=COUNTIFS(Tableau1[Salarié];A5;Tableau1[Type];"CP")
=== Alerte solde dépassé (mise en forme conditionnelle) ===
Règle sur la cellule de solde : =AN5<0
→ Remplissage rouge, police blanchePour un suivi annuel consolidé, créez un onglet Synthèse qui agrège les données de tous les onglets mensuels. Les fonctions INDIRECT et SOMME combinées permettent de référencer dynamiquement les 12 onglets sans saisir chaque référence à la main.
Standardisez les codes d'absence dès le départ : CP, RTT, MAL, FOR (formation), CSS (congé sans solde), PAT (paternité), MAT (maternité). Une liste déroulante de validation de données garantit la cohérence des saisies et évite les fautes de frappe qui faussent les compteurs.
Automatiser la mise à jour mensuelle avec une macro VBA
Même avec des formules dynamiques, le passage d'un mois à l'autre nécessite souvent de réinitialiser certaines zones, d'archiver le mois précédent et de préparer la feuille pour le nouveau mois. Une macro VBA permet d'automatiser toutes ces opérations en un seul clic.
La macro suivante génère un nouvel onglet mensuel à partir d'un modèle, initialise la ligne de dates et nettoie les saisies sans toucher aux formules :
Sub GenererPlanningMensuel()
' =====================================================
' Génère un onglet de planning pour le mois/année
' sélectionnés dans les cellules B1 et C1 de l'onglet
' "Modèle"
' =====================================================
Dim wsModele As Worksheet
Dim wsNew As Worksheet
Dim annee As Integer
Dim mois As Integer
Dim nomOnglet As String
Dim nbJours As Integer
Dim premierJour As Date
Dim i As Integer
' Récupération des paramètres
Set wsModele = ThisWorkbook.Sheets("Modèle")
annee = wsModele.Range("B1").Value
mois = wsModele.Range("C1").Value
' Nom de l'onglet : ex. "2024-06 Juin"
Dim nomsM(1 To 12) As String
nomsM(1) = "Janvier" : nomsM(2) = "Février" : nomsM(3) = "Mars"
nomsM(4) = "Avril" : nomsM(5) = "Mai" : nomsM(6) = "Juin"
nomsM(7) = "Juillet" : nomsM(8) = "Août" : nomsM(9) = "Septembre"
nomsM(10) = "Octobre": nomsM(11) = "Novembre" : nomsM(12) = "Décembre"
nomOnglet = annee & "-" & Format(mois, "00") & " " & nomsM(mois)
' Vérifier si l'onglet existe déjà
Dim ws As Worksheet
For Each ws In ThisWorkbook.Sheets
If ws.Name = nomOnglet Then
If MsgBox("L'onglet " & nomOnglet & " existe déjà. Le recréer ?", _
vbYesNo + vbQuestion) = vbNo Then Exit Sub
Application.DisplayAlerts = False
ws.Delete
Application.DisplayAlerts = True
Exit For
End If
Next ws
' Copier le modèle
wsModele.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
Set wsNew = ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
wsNew.Name = nomOnglet
' Mettre à jour les paramètres dans le nouvel onglet
wsNew.Range("B1").Value = annee
wsNew.Range("C1").Value = mois
' Calcul du nombre de jours dans le mois
premierJour = DateSerial(annee, mois, 1)
nbJours = Day(DateSerial(annee, mois + 1, 0))
' Générer la ligne de dates (à partir de la colonne D = colonne 4)
Dim colDebut As Integer : colDebut = 4 ' Colonne D
Dim ligneDate As Integer : ligneDate = 1
For i = 1 To 31
If i <= nbJours Then
wsNew.Cells(ligneDate, colDebut + i - 1).Value = _
DateSerial(annee, mois, i)
wsNew.Cells(ligneDate, colDebut + i - 1).NumberFormat = "dd"
Else
' Masquer les colonnes excédentaires
wsNew.Columns(colDebut + i - 1).Hidden = True
End If
Next i
' S'assurer que les colonnes des jours du mois sont visibles
For i = 1 To nbJours
wsNew.Columns(colDebut + i - 1).Hidden = False
Next i
' Nettoyer les saisies de données (lignes 2 à 50, colonnes D à AH)
' sans effacer les formules des compteurs
Dim plageData As Range
Set plageData = wsNew.Range(wsNew.Cells(2, colDebut), _
wsNew.Cells(50, colDebut + 30))
Dim cell As Range
For Each cell In plageData
If Not cell.HasFormula Then cell.ClearContents
Next cell
' Message de confirmation
MsgBox "Planning " & nomOnglet & " créé avec succès (" & _
nbJours & " jours).", vbInformation
' Activer le nouvel onglet
wsNew.Activate
wsNew.Range("D2").Select
End Sub
' =====================================================
' Bouton de lancement : associer cette macro au bouton
' "Nouveau mois" sur l'onglet Modèle
' =====================================================Pour associer cette macro à un bouton, insérez un Contrôle de formulaire (Développeur → Insérer → Bouton) sur l'onglet Modèle, puis assignez-lui la macro GenererPlanningMensuel. Chaque mois, un clic suffit pour créer un onglet propre, avec les dates correctes et les colonnes excédentaires masquées.
En janvier, saisissez 2025 en B1 et 1 en C1, puis cliquez sur "Nouveau mois". La macro crée l'onglet "2025-01 Janvier" avec 31 colonnes de dates visibles. En février, changez C1 à 2, relancez : l'onglet "2025-02 Février" est créé avec 28 colonnes, et les colonnes 29 à 31 sont automatiquement masquées.
Votre planning Excel mérite d'être automatisé
Henrick Balon conçoit des plannings Excel sur mesure : congés RH, plannings de projet Gantt, suivi de production — avec formules dynamiques, mise en forme automatique et macros VBA. Gagnez plusieurs heures par mois dès le premier déploiement.
