Google Sheets : projet planning de tâches 1/3 : créer le calendrier perpétuel

2

Dans un projet, l’organisation des tâches à réaliser dans le temps et la communication sur les évolutions des délais à chaque membre de l’équipe sont primordiales. De nombreux outils de gestion de projets existent et permettent de répondre à ce besoin. Pour autant, je vous propose de prendre en main quelques fonctions clés de Google Sheets pour construire un planning perpétuel et y faire figurer des dates de réalisations de tâches.

objectif final du projet, suivre étape par étape la réalisation

Sommaire du projet :

  • Créer le calendrier perpétuel
  • Repérer les week-ends et les lignes de tâches (publication à venir)
  • Cacher les colonnes inutiles (publication à venir)

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 validation 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 des 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éé 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.

Probleme : 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-end ? 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 !


Vous avez un projet autour de G Suite
et vous souhaitez nous solliciter :

formation | développement | paramétrage G Suite | interventions | …


Si vous avez trouvé une faute d’orthographe, veuillez nous en informer en sélectionnant le texte en question et en appuyant sur Ctrl + Entrée .

2 Commentaires

  1. 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.

  2. 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 !

LAISSER UN COMMENTAIRE

S'il vous plaît entrez votre commentaire!
S'il vous plaît entrez votre nom ici

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.