Excel Affiche les Anniversaires à venir

Excel Affiche les Anniversaires à venir
Dernière mise à jour le 12/03/2024
Temps de lecture : 4 minutes

Comment construire un classeur Excel pour afficher les anniversaires à venir ? La technique fonctionne aussi pour les échéances comme des dates de livraison, des dates de 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.

=AUJOURDHUI()

La fonction AUJOURDHUI() retourne la date du jour

Etape 2 : Différence sur les mois

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

Menu pour insérer les données dans un Tableau

Comme nous travaillons dans un tableau (Insertion > Tableau). Et donc, les références des colonnes sont nommées

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

Le paramètre ym de la fonction DATEDIF retourne le nombre de mois dans une année entière

Etape 3 : Différence sur les jours

Ensuite, nous allons effectuer un calcul au niveau des jours pour savoir quand l'échéance se rapproche.

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

Formule pour retourner le nombre de jours à l'intérieur d'un mois

Etape 4 : Analyser les résultats

Les résultats montrent

  • que pour les anniversaires à venir, les valeurs de l'écart en jours et mois est très élevé
  • que pour les anniversaires passés, les valeurs sont proches de 0
Comment interpréter les résultats

Dans une telle situation, la conception d'un test logique est très difficile, sauf si on introduit un délai.

Etape 5 : Ajout d'un délai avant l'anniversaire

Nous allons poser un paramètre qui va être utilisé dans tous les calculs d'écarts de mois et de jours. La référence de cette cellule doit être absolue pour rester invariablement en I2.

Ajout d'un délai d'affichage

Pour les calculs d'écart sur les mois nous avons maintenant

=DATEDIF([@[Date Naissance]]-$I$2;[@Aujourdhui];"ym")

Pour les calculs d'écart sur les jours, la formule est

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

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

Avec l'ajout du paramètre, les valeurs proches de l'anniversaire ont un mois égal à 0

Etape 6 : Tester les valeurs des 2 colonnes

Pour la colonne des mois c'est facile, la valeur doit être égale à 0.

=[@Colonne1]=0

Pour la colonne des jours, le test va porter sur la cellule qui contient le délai en jour, soit la cellule I2.

anniversaire_7

Nous allons donc nous servir de cette donnée pour écrire notre second 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)

Construction du test basé sur l'écart en jour et en mois

Etape 7 : Mise en forme conditionnelle

Nous allons nous servir du précédent test pour changer la couleur des cellules proches d'un anniversaire avec les mises en forme conditionnelle Accueil > Mise en forme conditionnelle > Nouvelle règle

Nouvelle règle de mise en forme conditionnelle
  1. Sélectionnez l'option Utiliser une formule ...
  2. Copier la formule précédente en respectant l'astuce décrite ci-dessous
  3. Cliquez sur Format pour changer le format des cellules qui répondent à la condition

Astuce : Pour que les références d'un tableau soient reconnues par les mises en forme conditionnelle, il faut écrire les références du tableau dans une fonction INDIRECT avec le nom du tableau

Formule conditionnelle avec référence à un tableau

Si cette écriture est trop compliquée, vous pouvez parfaitement l'écrire avec les références classiques d'Excel

Étape 8 : Modifier la plage d'application de la règle

Maintenant, il faut que cette règle s'applique à toute la ligne. Pour cela vous retourner dans le menu Accueil > Mise en forme conditionnelle > Gérer les règles

Menu de gestion des règles de mise en forme conditionnelle

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

Modification de la plage de données d'application de la mise en forme conditionnelle

Le résultat final est le suivant

Les anniversaires à venir s'affichent d'une couleur différente

Vidéo explicative

Vous retrouverez toutes les explications dans cette vidéo tutorielle.

7 Comments

  1. RHANNOU
    27/04/2021 @ 11:42

    Bonjour

    j'aimerai dans une colonne une fonction qui me trouve la dernière date d'anniversaire par rapport à une date et me copie la valeur de la colonne d'à coté:

    exemple:
    la date est le 01/01/2015
    dans un autre fichier j'ai une colonne du 01/01/2021 à 01/04/2021 avec des valeurs dans la colonne d'à coté

    vu que la date d'anniversaire est le 01/01/2021, j'aimerai qu'il la trouve est me mettre la valeur à coté qui est un nombre

    Merci d'avance de votre aide

    Reply

  2. Thomas et Ophelie
    28/01/2021 @ 13:53

    Bonjour,
    Dans mon tableau, j'ai une date, et je veux la mettre en rouge si cette date à plus de 3 mois.
    Je vais fans mise en forme conditionnelle
    Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
    Je tape = B12>=AUJOURDHUI()+90
    (sélectionner ma couleur)
    ma formule ne marche pas. J'ai besoin d'aide.
    Merci pour votre retour.

    Reply

  3. Julson
    15/08/2020 @ 16:11

    la formule =DATEDIF([@[Date Naissance]],[@Aujourdhui],"ym") ne fonctionne pas?
    Ce qui apparait c : #NAME?

    Reply

  4. Julson
    15/08/2020 @ 16:10

    la formule =DATEDIF([@[Date Naissance]][@Aujourdhui];"ym") ne fonctionne pas?
    Ce qui apparait c : #NAME?

    Reply

  5. demay
    16/06/2019 @ 08:45

    cordialement
    mad

    Reply

  6. demay
    16/06/2019 @ 08:44

    bonjour,
    la formule =DATEDIF([@[Date Naissance]]-$I$2;[@Aujourdhui];"ym") ne fonctionne pas?
    pourquoi?
    "le nom entré n'est pas valide…"

    Reply

    • Frédéric LE GUEN
      16/06/2019 @ 17:08

      Comme cela est indiqué à l'étape 2, les données ont été insérées dans un Tableau (insertion > Tableau)
      Ainsi, les références ne sont plus les références traditionnelles A1, A2, ... mais le nom des colonnes.
      Si vous n'avez pas créé de Tableau, alors, il faut remplacer les références nommées par les références traditionnelles A2, C4, D17, ...

      Reply

Laisser un commentaire

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

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

Excel Affiche les Anniversaires à venir

Reading time: 4 minutes
Dernière mise à jour le 12/03/2024

Comment construire un classeur Excel pour afficher les anniversaires à venir ? La technique fonctionne aussi pour les échéances comme des dates de livraison, des dates de 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.

=AUJOURDHUI()

La fonction AUJOURDHUI() retourne la date du jour

Etape 2 : Différence sur les mois

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

Menu pour insérer les données dans un Tableau

Comme nous travaillons dans un tableau (Insertion > Tableau). Et donc, les références des colonnes sont nommées

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

Le paramètre ym de la fonction DATEDIF retourne le nombre de mois dans une année entière

Etape 3 : Différence sur les jours

Ensuite, nous allons effectuer un calcul au niveau des jours pour savoir quand l'échéance se rapproche.

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

Formule pour retourner le nombre de jours à l'intérieur d'un mois

Etape 4 : Analyser les résultats

Les résultats montrent

  • que pour les anniversaires à venir, les valeurs de l'écart en jours et mois est très élevé
  • que pour les anniversaires passés, les valeurs sont proches de 0
Comment interpréter les résultats

Dans une telle situation, la conception d'un test logique est très difficile, sauf si on introduit un délai.

Etape 5 : Ajout d'un délai avant l'anniversaire

Nous allons poser un paramètre qui va être utilisé dans tous les calculs d'écarts de mois et de jours. La référence de cette cellule doit être absolue pour rester invariablement en I2.

Ajout d'un délai d'affichage

Pour les calculs d'écart sur les mois nous avons maintenant

=DATEDIF([@[Date Naissance]]-$I$2;[@Aujourdhui];"ym")

Pour les calculs d'écart sur les jours, la formule est

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

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

Avec l'ajout du paramètre, les valeurs proches de l'anniversaire ont un mois égal à 0

Etape 6 : Tester les valeurs des 2 colonnes

Pour la colonne des mois c'est facile, la valeur doit être égale à 0.

=[@Colonne1]=0

Pour la colonne des jours, le test va porter sur la cellule qui contient le délai en jour, soit la cellule I2.

anniversaire_7

Nous allons donc nous servir de cette donnée pour écrire notre second 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)

Construction du test basé sur l'écart en jour et en mois

Etape 7 : Mise en forme conditionnelle

Nous allons nous servir du précédent test pour changer la couleur des cellules proches d'un anniversaire avec les mises en forme conditionnelle Accueil > Mise en forme conditionnelle > Nouvelle règle

Nouvelle règle de mise en forme conditionnelle
  1. Sélectionnez l'option Utiliser une formule ...
  2. Copier la formule précédente en respectant l'astuce décrite ci-dessous
  3. Cliquez sur Format pour changer le format des cellules qui répondent à la condition

Astuce : Pour que les références d'un tableau soient reconnues par les mises en forme conditionnelle, il faut écrire les références du tableau dans une fonction INDIRECT avec le nom du tableau

Formule conditionnelle avec référence à un tableau

Si cette écriture est trop compliquée, vous pouvez parfaitement l'écrire avec les références classiques d'Excel

Étape 8 : Modifier la plage d'application de la règle

Maintenant, il faut que cette règle s'applique à toute la ligne. Pour cela vous retourner dans le menu Accueil > Mise en forme conditionnelle > Gérer les règles

Menu de gestion des règles de mise en forme conditionnelle

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

Modification de la plage de données d'application de la mise en forme conditionnelle

Le résultat final est le suivant

Les anniversaires à venir s'affichent d'une couleur différente

Vidéo explicative

Vous retrouverez toutes les explications dans cette vidéo tutorielle.

7 Comments

  1. RHANNOU
    27/04/2021 @ 11:42

    Bonjour

    j'aimerai dans une colonne une fonction qui me trouve la dernière date d'anniversaire par rapport à une date et me copie la valeur de la colonne d'à coté:

    exemple:
    la date est le 01/01/2015
    dans un autre fichier j'ai une colonne du 01/01/2021 à 01/04/2021 avec des valeurs dans la colonne d'à coté

    vu que la date d'anniversaire est le 01/01/2021, j'aimerai qu'il la trouve est me mettre la valeur à coté qui est un nombre

    Merci d'avance de votre aide

    Reply

  2. Thomas et Ophelie
    28/01/2021 @ 13:53

    Bonjour,
    Dans mon tableau, j'ai une date, et je veux la mettre en rouge si cette date à plus de 3 mois.
    Je vais fans mise en forme conditionnelle
    Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué.
    Je tape = B12>=AUJOURDHUI()+90
    (sélectionner ma couleur)
    ma formule ne marche pas. J'ai besoin d'aide.
    Merci pour votre retour.

    Reply

  3. Julson
    15/08/2020 @ 16:11

    la formule =DATEDIF([@[Date Naissance]],[@Aujourdhui],"ym") ne fonctionne pas?
    Ce qui apparait c : #NAME?

    Reply

  4. Julson
    15/08/2020 @ 16:10

    la formule =DATEDIF([@[Date Naissance]][@Aujourdhui];"ym") ne fonctionne pas?
    Ce qui apparait c : #NAME?

    Reply

  5. demay
    16/06/2019 @ 08:45

    cordialement
    mad

    Reply

  6. demay
    16/06/2019 @ 08:44

    bonjour,
    la formule =DATEDIF([@[Date Naissance]]-$I$2;[@Aujourdhui];"ym") ne fonctionne pas?
    pourquoi?
    "le nom entré n'est pas valide…"

    Reply

    • Frédéric LE GUEN
      16/06/2019 @ 17:08

      Comme cela est indiqué à l'étape 2, les données ont été insérées dans un Tableau (insertion > Tableau)
      Ainsi, les références ne sont plus les références traditionnelles A1, A2, ... mais le nom des colonnes.
      Si vous n'avez pas créé de Tableau, alors, il faut remplacer les références nommées par les références traditionnelles A2, C4, D17, ...

      Reply

Laisser un commentaire

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

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