Numeriblog › Google Workspace › Google Sheets, le tableur surpuissant › Google Sheets : projet planning de tâches 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 […]

Laisser une évaluation

8 948
personnes ont consulté cet article

3 minutes

Rédigé par Antoine MARTIN - il y a 4 ans et modifié le 22/05/2024 à 11:02

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 similaires

  • Articles connexes
  • Plus de l'auteur

Rédacteur

Photo de profil de l'auteur
Antoine MARTIN

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.

S’abonner
Notification pour
3 Commentaires
Le plus ancien
Le plus récent Le plus populaire
Commentaires en ligne
Afficher tous les commentaires
D'ANDRIA Joseph

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 !

1
ANTOINE MARTIN

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.

1
Thierry

Bravo les experts !

1