Calendrier Autotmatique

6
1908

Nous nous approchons de la fin de l’année c’est pourquoi nous allons créer un calendrier Automatique.
C’est-à-dire que nos weekends et jours fériés changeront automatiquement selon l’année. Ainsi que s’il s’agit d’une année bissextile alors le 29 février s’affichera.
Nous allons donc beaucoup travailler avec les mises en forme conditionnel et nous allons aussi voire comment faire en sorte que notre calendrier teint sur une seule page lors de l’impression.



Et donc dans cette vidéo nous allons crée un calendrier que nous allons pouvoir modifier selon nos envies. Mais la plus grande particularité c’est que dans ça forme de base vous allez pouvoir l’utiliser tout le temps. Car il vous suffira de changer l’année et pouf notre calendrier se met à jour tout seul. un peut comme celui de notre gestion du personnel. 

Les jours fériés.

Avant de commencer nous allons crée un tableau pour nos jour férié. Et pour ne pas trop se prendre la tête nous allons sur le site http://joursferies-be.be/ et vous pouvez choisir votre pays bien sûr.
De là nous allons copier les jours fériés et les coller dans notre fichier Excel.
Ici nous fessons un clic droit de la souris et chosions collage spéciale (respecter la mise en forme du destinataire.)
Désormais nous allons donner un titre au-dessus comme suite.
Jour Férié, Date, Jour, Semaine, Pays
Bien sur la seule chose qui nous intéresse c’est le nom et la date.
On sélectionner le tout et puis nous allons insérer un tableau.
Important : normalement la date est automatiquement reconnue en tant que format date. Pour contrôler si c’est le cas, il faut sélectionner toutes les dates et puis le changer en format date courte. Si le format à changer par exemple 01-janv-17 en 01/01/2017 c’est que c’est bon. Par contre si le format n’a pas changé alors le format n’est pas en date et il faudra le changer manuellement.
Il ne nous reste plus qu’à sélectionner les date et de renommer la plage en liste

La page Calendrier.

Désormais nous allons crée une nouvelle page que nous allons nommée calendrier.

Changer automatiquement l’année

Ici l’objectif c’est de faire en sorte que les jours et les mois reste le même sauf que l’année devrais changer.
Et donc dans la cellule B2 nous allons indiquer un texte + l’année. Ce qui devrais nous donner le résultat suivant. Calendrier 2018

ATTENTION : ne surtout pas placer un espace après l’année sinon il affichera l’année 1918 si vous placez une espace après 2018 car cette information serra repris dans une formule.
Désormais sur la cellule B7 nous allons afficher la formule suivante.

=DATE(DROITE($B$2;4);1;1)

Et le résultat serra 01/01/2018 et nous changeons le texte dans B2 en Calendrier 2019 alors automatiquement le résultat en B7 changera en 01/01/2019.
Alors je vous expliquer ce que nous fessons dans cette formule.

Nous demandons à Excel d’afficher une date avec la formule Date() et dans la formule date il faut indiquer l’année le moi et le jour.
Pour l’année nous avons dit qu’il faut la chercher dans le texte qui se trouve en B2 et nous avons indiqué où la trouver. Notamment de prendre les 4 caractère qui se trouve à droite du texte.

Pour le mois nous avons dit que nous voulons le premier mois donc janvier, si nous changeons cela en 2 nous aurons le mois de février, en 3 le mois de mars, etc..
Et puis le jour en 1 car nous voulons commencer le premier jour du mois.

Ajouter un jour si le mois n’est pas encore fini.

Désormais que nous avons notre jour de départ nous allons ajouter un jour de plus jusqu’à arriver à la fin du mois. Sauf que nous voulons que ça sarrette quand le mois et fini.
Et ça nous allons le faire avec la formule suivante.

=SI(FIN.MOIS(B7;0)>B7;B7+1;””)

Et ça nous allons l’étirer jusque à ce que nous voyons le 31/01 s’afficher.
Alors dans cette formule nous avons demander de contrôler si la fin du mois qui se trouve dans la cellule B7 est plus grande que la date qui se trouve dans la cellule du dessus. Si c’est le cas alors nous ajoutons 1 jour sinon si la date de fin du mois n’est pas supérieure nous ajouter rien.

Afficher le jour de la semaine.

Maintenant que nous avons la date nous allons pouvoir rechercher le jour et l’afficher avec la formule suivante dans la cellule C7.

=SIERREUR(TEXTE(JOURSEM(B7);”jjj”) ;””)

ici nous avons combiné 3 fonction,

avec la fonction JOURSEM() nous allons afficher de quel jour de la semaine il s’agit, le résultat sera en chiffre de 1 à 7. mais comme nous voulons l’afficher en texte nous allons l’introduire dans al fonction TEXTE() et ainsi ajouter le format “jjj” pour afficher les 3 première lettre.

S’il ne trouve pas de jour car il n’y a pas de date la formule nous affichera une erreur. Et pour évite d’afficher cette erreur nous allons placer le tout dans la fonction SIERREUR().

Afficher le Nr du jour.

Pour afficher le numéro du jour nous allons placer la formule suivante dans la cellule D7.

=SIERREUR(JOUR(B7);””)

Ici la fonction JOUR() permet d’afficher uniquement le jour d’un date.

Les jours fériés.

La dernière chose à faire c’est d’afficher les jours fériés. Et cela nous allons le faire avec une recherche INDEX et EQUIV dans la cellule E7.

=SIERREUR(INDEX(Tableau1[Nom];EQUIV(Calendrier!B7;liste;0));””)

Avec la fonction index nous allons demander d’afficher une donnée qui se trouve dans notre tableau nom et la fonction indexe nous demande ou ce trouve la donnée rechercher en indiquer la lige et la colonne.

Donc pour afficher la ligne nous allons la chercher avec la fonction EQUIV et là nous allons dire qu’il faut recherche la date qui se trouve en B7 dans notre liste  c’est la plage de donnée avec les date. Et que le résultat doit être 100% exacte c’est pourquoi nous affichons un 0.
S’il trouve une correspondance avec la recherche alors la fonction EQUIV va nous indiquer sur quelle ligne elle se trouve.

Mise en forme des weekend.

Sélectionner tous les dates et puis dans la mise en forme conditionnel crée une nouvelle règle, utiliser une formule (tout en bas) et placer la formule suivante.

=OU(C7=”dim”;C7=”sam”)

Puis changer le format comme voulu. Ici nous avons demander à la mise en forme si le texte sam ou dim est afficher dans la cellule C7 alors il faut appliquer la mise en forme voulu si ce n’est pas le cas alors ne rien faire.
Dans notre cas le font en bleu, le texte en blanc et lune ligne grise en entre les cellule.

Afficher une ligne en dessous.

Pour afficher automatiquement une ligne quand il y a des données. Sélectionner la date le jour de la semaine et le numéro du jour.
Appliquer une mise en forme uniquement aux cellules qui contiennent et la valeur de la cellule différente de = « »
La mise en forme un ligne grise.

Pour placer une ligne sous l’information nous allons placer une mise en forme avec une formule.

=C7<>””

Ici nous allons changer le format en ajoutant une ligne grise + la police en rouge

Afficher le numéro du jour en rouge pour les jours fériés.

Sélectionner les nr du jour et dans la mise en forme nous allons contrôler s’il s’agit d’un jour férié ou pas. Nous allons faire cela avec la fonction EQUIV.

=EQUIV(B7 ;liste ;0)

Ici nous avons demander de voir si la date qui se trouve dans la cellule B7 est aussi dans la plage de de date que nous avons nommer liste.
Si c’est le cas alors le résultat est vrais donc il va mettre la mise en forme choisi par contre si ce n’est pas le cas alors c’est faux et il ne fait rien.
Si nous voulons aussi afficher le jour en rouge il suffit de faire la même chose.

Les 12 mois et la mise en forme.

Voila notre mois de janvier est prêt et il nous reste plus qua copier le tout et de le coller pour chaque mois donc en total 12.
Dans mon exemple je lèse une colonne vide entre chaque mois.

Ha oui n’oublier pas de changer le numéro du mois sur la première date.

Une fois cela fait nous allons ajouter un titre au-dessus de chaque mois, donc on sélectionne la cellule B5 a E5 et nous allons les fusionner et puis indiquer le mois
Mettez la dans la couleur et la police que vous souhaitez et faite la même chose pour les autres mois.

Désormais nous allons fusionner la cellule B2 à BH2 et mettre notre titre en forme.

Masquer les ligne de date.

Sélectionner toutes les colonnes avec des dates faite un clic droit et masquer les.
Réduire l’écart entre les dates.

sélectionner toutes les colonnes qui sont vide et réduisez la taille selon souhaiter.

Réduire la taille des colonnes de jour et numéro de jour.

Sélectionner toutes les colonnes des jours et numéro de jour, aligner les et puis faite un double clic entre deux colonne pour automatiquement les mettre à la bonne taille.
Ajuster la taille des jours fériés.
Sélectionner tout le calendrier (la partie avec les dates) et cliquer sur renvoyer à la ligne automatique.

Imprimer sur 1 page.

Pour imprimer le tout sur 1 page il suffit de faire CTRL+P qui va afficher la page d’impression puis Orientation changer en Paysage.
Dans mise en page cocher Ajuster 1 page en largeur sur 1 en hauteur. Puis dans l’onglet Marges changer le gauche en 1 et la droite en 0.8 puis cliquer sur OK.
Désormais vous pouvez soit l’imprimer soit l’enregistrer en tant que PDF.

sur se petit liens vous allez pouvoir retrouver le fichier qui contient le calendrier (le lien revient bientôt)

Voilà ce petit cour se termine et vous êtes désormais capable de crée votre propre calendrier Automatique. Vous pouvez toujours l’améliorer en ajoutant les date d’anniversaire, indiquer le numéro de semaine.

Et comme toujours si vous avez des questions ou des suggestions surtout n’hésiter pas de le poster dans les commentaires et je ferais mon possible de vous répondre au plus vite.
Et d’ici la je vous vois dans une prochaine vidéo.

6 Commentaires

  1. Votre le tuto m’intéressais grandement car je voulais créer un calendrier scolaire (septembre à juillet de l’année suivante). J’ai donc fais en même temps le travail sur mon ordi pour comprendre le codage mais je suis restée bloquer sur la mise en page conditionnelle : Impossible de l’appliquer. Si je fais une colonne ou j’ai écrit manuellement les jours de la semaine, la mise en forme s’applique. J’ai fais alors une copie de mise en forme sur la colonne jour du calendrier et elle a fonctionné. Par contre, elle ne fonctionne ni sur la colonne des jours jj/mm/année.
    Je ne vois pas non plus comment télécharger votre calendrier

    • bonjour Nadine.

      le liens de téléchargement se trouve ici http://eepurl.com/dezJob vous devez vous inscrire à la liste d’info et puis vous recevez le fichier par mail.

      il faut faire attention au signe $ dans la mise en page conditionnel afin de bien bloquer les lignes. sinon ça ne marche pas.

      vous pouvez toujours m’envoyer votre fichier à excelderien@gmail.com pour que je vérifier le problème

      merci

  2. Bonjour
    Je suis très heureux d’avoir découvrir votre site internet via YouTube
    je suis en stage pré-embauche département RH , mon DRH ma demandé de réorganiser la feuille générer par la pointeuse de sorte à avoir une feuille récapitulative se forme de tableaux de bord qui affiche le temps de retard ainsi , le directeur voudrais savoir l’état de chaque salarié durant la semaine cad taux de retard et absences et nombre d’heures travaillées .
    Merci infiniment pour votre aide

LAISSER UN COMMENTAIRE

Please enter your comment!
Please enter your name here

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