Sheets 
Agenda 
Forms 
Apps Script 
Gmail 
Chat 
Meet 
Contacts 
Chrome 
Sites 
Looker Studio 
Slides 
Docs 
Drive 
AppSheet 
Admin Workspace 
Android 
Chromebook 
Equipements Google 
Google Cloud Platform 
Google Photos 
Maps 
Youtube Google Sheets : projet planning de tâches 1/3 – créer le calendrier perpétuel
1/3 Créer le calendrier perpétuel L’objectif : afficher les dates quotidiennes comprises entre deux dates saisies manuellement. Créer les cellules de saisies de dates Créez une feuille de calcul. Utilisez les validations de données pour […]
Ce que vous allez découvrir
- 1/3 Créer le calendrier perpétuel
Google Sheets : projet planning de tâches 1/3 – créer le calendrier perpétuel
1/3 Créer le calendrier perpétuel
L’objectif : afficher les dates quotidiennes comprises entre deux dates saisies manuellement.
Créer les cellules de saisies de dates
Créez une feuille de calcul.
Utilisez les validations de données pour forcer l’affichage d’une date dans la cellule qui accueillera la date de début de votre calendrier.
Répétez la manipulation pour la deuxième cellule afin que celle-ci accueille une date forcément plus récente que la cellule précédente.
Prévoir 366 jours
L’astuce que je propose ici est de créer une ligne qui numérote les jours de 0 à 366 (dans le cas d’un planning pouvant accueillir au maximum 366 jours : libre à vous d’adapter ce nombre). Il faut donc créer 366 colonnes et une ligne numérotée de 0 à 366 (utilisez la poignée pour écrire rapidement la suite de nombres.)
Je crée ensuite un arrayformula qui ajoute à la date de départ la quantité de jours correspondant à la même colonne, jusqu’à atteindre la date finale.
=ArrayFormula(SI(F$4:$4<>"";SI($C$2+F$4:$4<=$E$2;$C$2+F$4:$4;);))
Voyons cette formule en détail :
| =ArrayFormula( | |
| SI(F$4:$4 <> » » | Si il existe un numéro dans la ligne 4… |
| ; | … alors… |
| SI($C$2+F$4:$4<=$E$2 | … si la somme de la date de début ($C$2) et de la valeur de la cellule de la ligne 4 de la même colonne est inférieure ou égale à la date de fin … |
| ; | … alors |
| $C$2+F$4:$4 | … écrire dans la cellule de la ligne 5 la somme de la date de début ($C$2) et de la valeur de la cellule de la ligne 4 (soit le jour de début + 0, le jour de début + 1, le jour de début + 2… etc.) jusqu’à atteindre la date finale. |
| ; ) | … sinon (si la date de fin est atteinte), ne rien faire. |
| ; ) | … sinon (si il n’y a plus de numéro dans la ligne 4), ne rien faire. |
| ) | fin du arrayformula |
Entrez des dates de début et de fin et constatez l’affichage automatique des dates (pensez à formater la ligne pour afficher les dates correctement).
Rappel : Sheets gère les dates en comptant les jours depuis le 30 décembre 1899 qui est le jour « 0 ». Le 30 août 2020 est le 43 831ème jour écoulé depuis cette date. Il suffit d’additionner 1 à une date pour trouver le lendemain.
Afficher les dates
Maintenant que les dates s’affichent de la date de début à la date de fin saisie dans les cellules, améliorons l’affichage pour gagner en visibilité comme dans un agenda classique.
L’objectif est d’écrire le mois, la première lettre du jour et le jour dans trois lignes différentes. Pour cela je vous propose de recopier la formule sur trois lignes et d’adapter l’affichage de la date de chaque ligne.
- Étirez la cellule contenant le arrayFormula sur deux nouvelles lignes.
- Commençons par le plus simple : écrire le numéro du jour dans la troisième ligne : un simple format de cellule suffit.
- Sélectionnez toute la ligne ;
- affichez le format date « jour, sans zéro devant ».
La ligne affiche uniquement le numéro du jour.
Remarque : Même si la cellule n’affiche que le numéro du jour, elle porte l’information de la date complète. Celle-ci sera la référence de notre planning dans les étapes suivantes.
- Comment écrire la première lettre du jour dans la ligne du dessus ? L, M, M, J, V, S, D …
- Le formatage ne suffit pas, il n’existe pas de format « Nom du jour sur une seule lettre. » 🙁
- Utilisons la combinaison des fonctions TEXTE() et GAUCHE() : TEXTE() pour forcer l’affichage de la date dans le format « nom du jour » avec l’argument « DDDD », et GAUCHE(cellule;1) pour ne garder que la première lettre.
=ArrayFormula(SI(F$4:$4<>"";SI($C$2+F$4:$4<=$E$2;
GAUCHE(
TEXTE($C$2+F$4:$4
;"DDDD")
;1)
;);))
Ce formatage perd l’information complète de la date du jour.
- Enfin paramétrez le format de la première ligne pour afficher seulement le nom de mois.
Problème : le mois s’écrit dans chaque cellule. Il faudrait ne l’afficher qu’une seule fois pour tout le mois. La seule astuce que j’ai trouvée est de ne l’écrire que sur la toute première cellule (si F4:4 = 0) ou à chaque premier jour du mois (si Texte(F7:7; »D »)= »1″).
=ArrayFormula(SI(F$4:$4<>"";SI($C$2+F$4:$4<=$E$2;SI(F$4:4=0;$C$2+F$4:$4;SI( TEXTE(F$7:7;"D")="1";$C$2+F$4:$4;)););))
Je n’ai pas trouvé comment écrire de façon automatique, simple et pérenne le nom du mois au centre de la plage du mois…
Peaufinez en effectuant quelques mises en forme générale : masquez le quadrillage, masquez la ligne des numéros de jour (police blanche, ou masquez la ligne), appliquez des bordures sur le planning, choisissez une police lisible et peu encombrante (les « condensed » sont bien appropriées), uniformisez la largeur des colonnes… un peu d’astuce, d’espièglerie, et c’est parti 🙂
La suite au prochain épisode ! Comment repérer les week-ends ? Comment colorier les plages de dates en fonction de dates saisies pour chaque tâche ?… N’hésitez pas à me faire part de vos remarques sur ce projet, si vous avez des suggestions ou des questions !
Besoin d'un peu plus d'aide sur Sheets ?
Des formateurs sont disponibles toute l'année pour vous accompagner et optimiser votre utilisation de Sheets, que ce soit pour votre entreprise ou pour vos besoins personnels !
Découvrir nos formations Sheets
- Articles connexes
- Plus de l'auteur
Consultant et formateur sur les outils bureautiques, j'ai intégré l'équipe de Numericoach en 2020. J'accompagne les utilisateurs de Google Workspace à trouver des solutions répondant à leurs besoins. Mes domaines de prédilections sont les outils Sheets, Docs, Slides et Google Apps Script.
Salut Antoine,
j’ai une autre méthode pour créer un calendrier perpétuel qui servira de base de données…
1° – Cellule A1 : saisir l’année (2021 par exemple)
2° – je garde la ligne 2 pour mes entêtes par exemple
3° – Cellule A3: récupérer la date relative au 1er jour de l’année avec =date(A1;1;1) qui se traduit par DATE(année, mois, jour)
4° – Cellule A4: =SI(ANNEE(A3+1)=ANNEE(A3);A3+1;) qui permet de rajouter 1 au jour
5° – Cellule A5: =SI(ANNEE(A4+1)=ANNEE(A4);A4+1;) etc. etc..jusqu’à la fin de l’année
Enfin je peux ensuite récupérer les données de mois et jour
6° Cellule B3 : =Mois(A3):fonction [MOIS(date)], te renvoie le chiffre 1 pour Janvier
7° Cellule C3 : =Jour(A3): fonction [JOUR(date)], te renvoie le chiffre 1 pour le 1er de l’an
8° Cellule D3 : =Joursem(A3): fonction [JOURSEM(date; [type])], te renvoie le chiffre 6 pour samedi (Attention ici à bien choisir le type de semaine soit le comptage débute le dimanche soit il débute le lundi c’est le [type] qui le définit)
Ne reste plus qu’à créer une petite table avec les numéros de jour, mois et leur libellé et d’y faire une rechercheV…
Et le tour est joué !
Voici mon fichier en exemple:
https://docs.google.com/spreadsheets/d/1QHDtXLuvpYIHdp_4FQEA1eg43j9XGy-0TCu_i8OvUes/Copy
Pour les Jours fériés, il existe aussi un calendrier perpétuel qui calcule les jours fériés de toutes les années à venir de Sebastien Mathivet qui est très bien fait.
Au plaisir !
Merci Joseph, tous les chemins mènent à Rome ! 🙂
L’idée de la ligne des jours permet aussi de l’afficher et d’indiquer qu’on est le jour N de la période.
Bravo les experts !