Calcul d’un âge avec Excel
Le calcul de l’âge avec Excel est très utile. On prend par exemple un tableau avec toutes les dates de naissance et on calcule l’âge et la date du prochain anniversaire. Si, en plus, on indique les noms des personnes, on peut déterminer automatiquement la prochaine personne de la liste qui fêtera son 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 en saisissant le chiffre 1 et en choisissant le format date pour la cellule. 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 centaines doit également être un multiple de 4), vous ne devriez pas trouver le même résultat qu’Excel, mais 1 de moins !
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 et comme déjà expliqué, 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 !
Par conséquent, 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 jours les séparant. Or, la première fois que vous faites cela, vous vous retrouvez avec ceci :
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.
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.
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() calcule 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ée précédemment (=nombre de jours écoulés 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 bissextiles), 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és).
- « YM » : Différence entre les valeurs de mois des deux dates (les jours et les années sont ignorés).
- « Yd » : Différence en nombre de jours entre les deux dates en ignorant l’année. Ce n’est pas 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 jours 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.
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.
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…
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.
Premier bonus : Déterminer le prochain anniversaire
Cela peut paraître simple a priori.
Mais la colonne « nombre de jours restant avant le prochain anniversaire » va s’avérer indispensable. 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.
De plus, pour éviter d’oublier 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 les personnes ayant la même date d’anniversaire.
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 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.
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 jours souhaités.
Vous pouvez maintenant télécharger le fichier Excel de calcul de l’âge.
un cour excellent, merci