«

»

Excel – Afficher les anniversaires

Dans cet article, nous allons voir comment changer la couleur des cellules pour les personnes qui ont leurs anniversaires à venir dans les 7 jours. Nous allons avoir besoin de :

Téléchargement du classeur

Toutes les manipulations et opérations se retrouvent dans le fichier téléchargeable (après paiement)

Etape 1 : Ajout de la date du jour

Tout d'abord, nous devons rajouter dans une colonne la date du jour avec la fonction AUJOURDHUI(). Nous pouvons très bien éviter d'ajouter cette colonne supplémentaire en intégrant cette fonction dans les calculs qui suivent mais pour faciliter la compréhension, c'est mieux ainsi.

anniversaire_1

Etape 2 : Différence sur les mois

Dans une nouvelle colonne, nous allons effectuer un calcul pour déterminer le nombre de mois restant à atteindre avant la date anniversaire. Ceci s'obtient avec la fonction DATEDIF et le paramètre "ym" (nombre de mois dans l'année courante).

Comme nous travaillons dans une table, les références des colonnes sont nommées

=DATEDIF([@[Date Naissance]];[@Aujourdhui];"ym")

anniversaire_2

Etape 3 : Différence sur les jours

De même nous allons effectuer un calcul au niveau des jours pour savoir quand l'échéance se rapproche.

=DATEDIF([@[Date Naissance]];[@Aujourdhui];"md")

anniversaire_3

Lorsque l'on étudie les résultats de près, on constate qu'une date anniversaire qui vient de passer à un écart de mois égal à 0 et une valeur de jour très faible, alors que des anniversaires à venir ont des valeurs très élevées.

anniversaire_4

Pour contourner ce problème nous allons introduire un paramètre correspondant au nombre de jours qui vont précéder l'alerte.

Etape 4 : Ajout d'un délai

En marge de notre tableau de données, nous allons poser un paramètre qui va être utilisé dans tous les calculs d'écarts de mois et de jours.

=DATEDIF([@[Date Naissance]]-$I$7;[@Aujourdhui];"md")

La référence de cette cellule doit être absolue pour rester invariablement en I2.

anniversaire_5

Nous constatons dès lors qu'un anniversaire à venir affiche des valeurs proches de 0.

anniversaire_6

Etape 5 : Tester les valeurs des 2 colonnes

Avant d'écrire notre test, il est important de déterminer les limites. Pour la colonne des mois c'est facile, la valeur doit être égale à 0.

=[@Colonne1]=0

Pour la colonne des jours, quelle est la plus grande valeur que nous pouvons accepter ? Pour ça nous allons modifier l'une de nos dates (ce qu'il ne faut jamais faire sur vos données réelles) pour qu'elle soit égale à la date du jour.

anniversaire_6

Si la date anniversaire est égale à la date du jour, l'écart en nombre de jours est égal au délai inscrit en cellule I2.

anniversaire_7

Nous allons donc nous servir de cette donnée pour écrire notre seconde test.

=[@Colonne2]<=$I$2

Il ne nous reste plus qu'à intégrer ces 2 tests dans une fonction ET.

=ET([@Colonne1]=0;[@Colonne2]<=$I$2)

anniversaire_8

Etape 6 : Changer les références du test

Maintenant que nous savons retourner VRAI ou FAUX pour les jours qui nous intéresse, nous allons pouvoir écrire ce test dans une mise en forme conditionnelle. Mais avant cela nous devons impérativement changer les références. En effet, les mises en forme conditionnelles n'acceptent pas les plages de cellules nommées en provenant d'un tableau.

L'écriture de votre formule va donc s'écrire maintenant de la façon suivante

=ET(E2=0;F2<=$I$2)

Mais ce n'est pas terminé, il nous faut verrouiller les colonnes. C'est indispensable sinon les tests des mises en forme conditionnelles ne seront plus nécessairement sur les colonnes E et F. Donc nous allons positionner des $ justes avant les lettres des colonnes.

=ET($E2=0;$F2<=$I$2)

Etape 7 : Formule dans une mise en forme conditionnelle

  1. Copiez la formule précédente
  2. anniversaire_9Ouvrez ensuite le menu Accueil>Mise en forme conditionnelle>Nouvelle règle

 

 

 

 

 

  1. anniversaire_10Dans la boîte de dialogue, sélectionnez Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué
  2. Là, vous copiez dans la zone de texte votre formule
  3. Cliquez sur le bouton Format
  4. Puis l'onglet Remplissage, vous choisissez une couleur qui va définir votre trame de fond quand la règle sera VRAI.

Etape 8 : Modifier la plage de colorisation

anniversaire_11Maintenant, il faut que cette règle s'applique à tout votre document. Pour cela vous retourner dans le menu Accueil>Mise en forme conditionnelle>Gérer les règles

 

 

Dans la zone S'applique à, sélectionnez tout votre tableau

anniversaire_12

Le résultat final est le suivant

anniversaire_13

Vous pouvez aussi construire votre test sans passer par les colonnes intermédiaires. Ceci nécessite de modifier vos tests des mises en forme conditionnelles. C'est cette solution que vous trouverez dans le fichier à télécharger.

 

Articles complémentaires


Voici une liste d'articles qui pourrait également vous intéresser sur le même thème.

Lien Permanent pour cet article : https://www.excel-exercice.com/excel-afficher-les-anniversaires/


Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *