Calcul d’un âge avec Excel

Calcul d’un âge avec Excel

Bienvenue sur Exceller avec la Bureautique ! Si vous êtes nouveau ici, vous voudrez sans doute lire mon livre PDF qui vous aide à gagner du temps avec Excel cliquez ici pour télécharger le Livre en PDF gratuitement !.

Le calcul de l’âge avec Excel est très utile. L’un des premier usage qui en est fait est de calculer la date du prochain anniversaire. On prend par exemple un tableau avec toutes les dates de naissance et on souhaite calculer l’âge et la date du prochain anniversaire. Si en plus, on met les noms des personnes, on pourrait déterminer automatiquement la prochaine personne dont ce sera l’anniversaire.

En explorant la notion de calcul de l’âge avec Excel, j’ai eu l’occasion d’approfondir mes connaissances sur le traitement des dates avec Excel. Et je vais m’empresser de vous les partager !

Le premier jour d’Excel

Excel ne sait gérer les dates qu’à partir du 1er janvier 1900 et jusqu’au 31 décembre 9999. D’ailleurs, Excel considère les dates comme des nombres. Vous pouvez faire la simple expérience de saisir le chiffre 1 et de placer le format de la cellule comme une date. Excel indiquera alors le 1er janvier 1900.

Ensuite, chaque valeur indiquera le nombre de jour écoulés après ce 1er jour. La valeur 2 représentera le 2 janvier 1900 et ainsi de suite. Comme nous sommes plus de 100 ans après cette date, le 1er janvier 2000 correspond à environ 10*365 + le nombre d’années bissextiles entre 1900 et 2000 + 1. En l’occurrence, le 1er janvier 2000 correspond à la valeur 36’526. Or, si vous vérifiez la règle qui détermine les années bissextiles (multiple de 4 + pour les centenaires, le nombre de centaine doit également être un multiple de 4), vous ne devriez pas trouver le même résultat qu’Excel, mais 1 de mois !

Un bug officiel d’Excel est qu’il considère l’année 1900 comme une année bissextile !

https://docs.microsoft.com/fr-fr/office/troubleshoot/excel/determine-a-leap-year

Pour en revenir au traitement des dates par Excel, si vous saisissez 1 dans une cellule et que vous changez le formatage de la cellule en date, Excel vous affichera le 1/1/1900 !

Calcul âge Excel - Format date Excel
Calcul âge Excel – Format date Excel

Ainsi, lorsque vous faites des opérations entre deux dates, Excel réalise une opération numérique entre les deux valeurs.

Calculer la différence entre deux dates

Intuitivement, on se dit qu’en soustrayant deux dates, on trouvera le nombre de jour. Or, la première fois que vous faites cela, vous vous retrouvez avec ceci :

Calcul différence entre deux dates - Format date
Calcul de la différence entre deux dates – Format date

La soustraction entre deux dates vous renvoie une date ! Comportement étrange, n’est-ce pas ?

Finalement, ce n’est qu’une question de format d’affichage de la cellule. Il vous suffit de placer le format d’affichage de la cellule au format “Standard” pour que tout rentre dans l’ordre.

Calcul différence entre deux dates - Format standard
Calcul la différence entre deux dates – Format standard

Ce calcul pourrait être la base pour calculer l’âge de quelqu’un à l’aide d’Excel. Par exemple, vous pourriez tenter de déterminer le quotient d’une division par 365.

Toutefois, je vous propose d’explorer une fonction cachée d’Excel = DATEDIFF().

DATEDIFF() – La fonction cachée d’Excel pour calculer l’âge

Vous pouvez faire le test, si vous commencez à saisir cette fonction dans la zone de formule, Excel ne vous la proposera pas.

Les personnes qui ont cet article ont aussi lu :  Le calendrier Excel 2021 est là !

Une fois saisie, vous n’aurez pas plus d’assistance. À vous de vous débrouiller pour trouver les paramètres, leur signification et les options.

Pourtant, Microsoft propose bien une page détaillant cette fonction. Je vous partage la même mise en garde, en théorie, cette fonction n’est pas supportée et peut retourner des résultats incorrects.

Toutefois, pour un usage basique et avec une acceptation sur les risques d’erreurs (le cas particulier est détaillé sur l’article de Microsoft), vous pouvez tout à fait utiliser cette fonction.

La syntaxe de la fonction DATEDIF()

DATEDIF(date_début,date_fin,unité)

Pour résumé cette fonction, DATEDIF() calcul un écart entre deux dates constituant votre intervalle de temps. Cet écart peut représenter un nombre de jours, de mois ou d’années selon ce que vous saisirez dans le dernier paramètre. Quelques options supplémentaires permettent de calculer des différences “spéciales”.

Par exemple, il sera possible de calculer un écart en nombre de jours entre deux dates en ignorant les valeurs des années, un nombre de mois entre deux dates en ignorant les jours et les années ou encore un nombre de jours entre deux dates en ignorant la valeur des années.

Détail des attributs

date_début représente le début de l’intervalle. Cet attribut doit être une valeur soit au format date via DATEVAL(jj/mm/aaaa) soit une valeur de date telle qu’expliqué précédemment (=nombre de jour écoulé depuis le 1er janvier 1900).

date_fin représente la fin de l’intervalle et les mêmes contraintes de format s’appliquent.

unité détermine le type d’écart que vous souhaitez calculer. Les options sont les suivantes (et n’ont pas été traduites comme le laisse prétendre la page d’aide de Microsoft. Il faut donc bien utiliser les valeurs ci-dessous).

Les différentes options de l’attribut unité

  • “Y” : Nombre d’année complètes entre les deux dates.
  • “M” : Nombre de mois complets entre les deux dates.
  • “D” : Nombre de jours complets entre les deux dates.

Avant de vous donner les autres options possibles, j’insiste sur le mot “complet” qui est mis en gras. En effet, si vous calculez l’écart entre le 1er janvier et le 31 décembre de la même année, vous trouverez un jour de moins qu’on ne pourrait l’imaginer !

Prenez l’écart entre le 1er et le 2 janvier (1er jour et 2e jour), il n’y a qu’un jour d’écart (=2-1). Ainsi, entre le 1er jour et le 365e de la même année (pour les années qui ne sont pas bissextile), il n’y a que 364 jours d’écart (=365-1). Il faut tenir compte de cet élément pour tous les types d’écart que vous calculerez (nombre jours, nombre de mois ou nombre d’année).

Les autres options à votre disposition sont :

  • “MD” : Différence entre les valeurs du jour des deux dates (les mois et les années sont ignorées).
  • “YM” : Différence entre les valeurs de mois des deux dates (les jours et les années sont ignorées).
  • “Yd” : Différence en nombre de jours entre les deux dates en ignorant l’année. Ce n’est totalement vrai.. l’année de la date de fin est considérée comme étant égale à l’année de la date de début.
    • Entre le 1er janvier 2020 et le 1er mars 2020 il y aura un jour de plus qu’entre 1er janvier 2021 et le 1er mars 2022.
    • En effet, l’année 2020 étant bissextile, le résultat sera 31 + 29 = 60, alors que dans le second cas, il sera de 31 + 28 = 59.

Calculer un âge et le nombre de jour jusqu’au prochain anniversaire avec Excel

Calculer un âge avec Excel à partir d’une date de naissance est désormais à votre portée ! Plus besoin de se soucier de créer une fonction, ou pire, une macro (j’ai un avis argumenté sur la question des macros) pour déterminer l’âge de votre collègue.

Les personnes qui ont cet article ont aussi lu :  Calendrier Excel 2020

Mieux, je vous propose à la fin de cet article de récupérer un modèle de fichier où vous n’aurez qu’à saisir le nom et la date de naissance pour que tout soit automatiquement calculé !

Première partie du classeur Excel de calcul de l’âge

Les deux premières colonnes sont les seules à éditer. Vous n’avez qu’à y saisir le nom et la date de naissance et tout le reste se fera tout seul.

J’ai pour habitude de placer en italique tous les champs qui sont calculés. Ainsi, je ne me demande pas très longtemps si je dois saisir une donnée ou non.

Calcul âge Excel - Première partie
Calcul âge Excel – Première partie

Une fois les donnée saisies, cette première partie facile se met à jour.

Deuxième partie du classeur Excel de calcul de l’âge

Ici, cela devient plus intéressant. Je vous propose un champ calculé avec une mise en forme conditionnelle qui vous indique d’un coup d’œil les anniversaires qui ont déjà été fêtés et en vert ceux qui sont à venir.

Afin de vous donner une chance de vous rattraper, je vous propose le nombre de jours depuis lesquels l’anniversaire a eu lieu. Si par chance c’était le week-end dernier…

Calcul âge Excel - Deuxième partie
Calcul âge Excel – Deuxième partie

Chacune de ces colonnes utilise bien sûr la fonction DATEDIF().

Troisième partie du classeur Excel de calcul de l’âge

Au cas où vous souhaitez organiser une fête particulière pour une dizaine, je vous propose également le décompte du nombre de jours de la prochaine dizaine.

Calcul âge Excel - Troisième partie
Calcul âge Excel – Troisième partie

Premier bonus : Déterminer le prochain anniversaire

Cela peut paraître simple a priori. Je vous invite toutefois à considérer le point suivant :

Comment déterminer que le prochain anniversaire correspond à une personne née les premiers jours de janvier ?

Vous comprendrez maintenant que la colonne nombre de jours restant avant le prochain anniversaire est très utile. Combinée à la colonne “Déjà passé ?”, il est possible de savoir si c’est la date de cette année ou celle de l’année prochaine qu’il faut prendre en compte dans les calculs.

Partie bonus
Calcul âge Excel – Partie bonus

De plus, afin d’éviter de manquer une personne qui serait née le même jour, j’ai configuré une mise en forme conditionnelle dans le tableau pour mettre en avant que plusieurs personnes aient la même date d’anniversaire.

Partie bonus 2
Calcul âge Excel – Partie bonus

PS : J’ai changé la date de naissance de Jeanne pour illustrer la situation.

Deuxième bonus : Changer la date de référence du calcul de l’âge

D’abord, expliquons la signification de cette fonctionnalité. Parfois, nous faisons des hypothèses : “Combien de jours lui restera-t-il avant son anniversaire lorsque nous serons à Noël ?”.

C’est pour cette raison (et une plus technique expliquée à la fin) que j’ai paramétré la date de référence sur une cellule et non sur la fonction AUJOURD’HUI().

J’ai nommé la cellule Q1 en DATEDUJOUR pour pouvoir l’utiliser dans toutes les fonctions ou mises en forme conditionnelles.

Calculer âge Excel - Dans le futur ou le passé
Calculer âge Excel – Dans le futur ou le passé

Ainsi, en vous plaçant sur cette cellule, vous pouvez soit saisir une date soit simplement réaliser une addition ou soustraction du nombre de jour souhaité.

Vous pouvez maintenant télécharger le fichier Excel de calcul de l’âge.

Partager l'article
  •  
  •  
  •  
  •  
  •  

Laisser un commentaire

Votre adresse de messagerie 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.