Opérations sur les dates avec Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Calculs sur les dates avec Excel

Nous avons déjà abordé le calcul sur les heures par le biais d'une formation Excel. Nous y avons notamment vu comment sommer les heures de travail lorsque le total dépassait 24 heures. Ici, au travers d'un exercice, nous proposons d'aborder l'ensemble des fonctions Excel qui permettent de manipuler les dates et de réaliser des opérations. Et si vous ne le saviez pas encore, vous saurez désormais quel jour de la semaine vous êtes né(e) à partir de votre date de naissance.

Le classeur

• Pour réaliser les manipulations, télécharger le classeur 24-calculs-et-formules-dates-excel.xlsx,en cliquant sur son lien,
• Puis, l'ouvrir dans Excel,

Cette feuille, à partir de la date du jour et d'une date de naissance propose d'obtenir bon nombre d'informations à l'aide des fonctions de calcul Excel sur les dates. A l'issue, le tableau renverra votre âge exact et sera même en mesure d'afficher le jour de la semaine de votre naissance.

La date du jour
C'est la première information demandée par la feuille en cellule D5. Il existe deux possibilités pour afficher la date du jour. Vous pouvez par exemple réaliser le raccourci clavier CTRL+ ;. La date du jour s'inscrit automatiquement dans la cellule sélectionnée. Le support de formation Excel sur les raccourcis clavier vous enseigne de nombreuses astuces au clavier. Cependant, cette date est statique. Cela signifie que si vous rouvrez votre classeur demain, la date n'aura pas changé. Pour obtenir une date dynamique dans Excel, il faut utiliser la fonction aujourdhui().

• Sélectionner la cellule D5,
• Taper la formule =AUJOURDHUI(),
• Valider la formule par CTRL + Entrée.

Ce raccourci permet de valider la fonction en conservant la cellule active. Vous auriez aussi pu valider par Entrée de manière à activer, par défaut, la cellule du dessous. La fonction Aujourdhui doit se saisir sans l'apostrophe. Il s'agit d'un nom de fonction donc tous les caractères spéciaux, comme les accents notamment, sont proscrits. La casse est indifférente. Vous pourriez saisir le nom de la fonction en minuscules, Excel la convertirait automatiquement en majuscules. Les deux parenthèses sont obligatoires. Toute fonction possède des parenthèses, c'est une norme. Elles permettent à une fonction d'indiquer des paramètres pour le calcul, comme une plage de cellules pour la somme automatique. La fonction Aujourdhui() n'a besoin d'aucun paramètre, donc nous ouvrons et fermons la parenthèse. Enfin, la fonction Aujourdhui() renvoie bien la date du jour dynamique. Si vous rouvrez votre classeur demain, la date en D5 se sera actualisée automatiquement. La cellule suivante D7 attend votre date de naissance. Pour qu'une saisie soit reconnue comme une date et que des opérations puissent être réalisées dessus, il faut l'écrire au format date soit jj/mm/aaaa. Les slash (/) sont importants.

• Taper votre date de naissance au format date en D7,

Les fonctions d'information sur les dates
Les cellules qui suivent dans la colonne D demande d'extraire de l'information à partir de la date de naissance. En D9, rien de plus simple, la fonction Annee() avec en paramètre la cellule de la date, permet de retourner uniquement l'année d'une date.

• En D9, saisir la formule =ANNEE(D7),

De la même façon, la fonction Mois() avec en paramètre la date de laquelle le mois doit être extrait, renvoie le mois de la date mais sous forme de nombre.

• En D11, taper la formule =MOIS(D7),

Dans notre cas, pour la date 27/04/1990, la fonction Mois() retourne la valeur 4. Le mois d'Avril est bien le quatrième mois de l'année. Cependant, dans la cellule du dessous, en D13, nous souhaiterions afficher ce mois en texte, soit Avril dans notre cas. Or il n'existe pas de fonction Excel pour ce faire. Mais nous pouvons utiliser une astuce. Il existe des colonnes masquées dans ce classeur. En effet, les lettres des étiquettes de colonne sautent de H à M sur la droite du tableau. Cela signifie que les colonnes I, J, K et L sont masquées. Elles contiennent les informations utiles pour retourner l'information du mois en texte selon un nombre.

• Sélectionner les colonnes H et M par leur étiquette comme l'illustre la figure,
• Puis réaliser un clic avec le bouton droit de la souris sur la sélection,
• Dans le menu contextuel, choisir Afficher,

Les colonnes apparaissent listant dans un premier tableau tous les mois de l'année avec leur numéro et dans un second, tous les jours de la semaine avec leur numéro. C'est une recherche du numéro du mois, calculé en D11, dans le tableau des mois de l'année qui va nous permettre de récupérer automatiquement, le mois en texte correspondant. Et c'est la fonction RechercheV() qui est exposée dans un support de formation sur le site qui permet de réaliser cette prouesse. Elle a besoin de quatre paramètres. Tout d'abord, il faut lui indiquer l'élément recherché pour récupérer l'information, ici le numéro de mois soit D11. En deuxième paramètre, il faut lui indiquer dans quel tableau doit être effectuée la recherche soit I4:J15 ici. Puis il faut préciser le numéro de colonne dans laquelle se trouve l'information correspondante à l'élément recherché. Le mois en texte se situe en colonne 2. Enfin le dernier paramètre se règle à Faux pour indiquer à la fonction de ne pas tenter de se rapprocher de la valeur si la référence cherchée n'est pas trouvée. Vrai au contraire lui permettrait. Ce qui donne =recherchev(numero_mois ;tableau_des_mois ;numero_colonne ;Faux).

• En cellule D13, taper la formule =RECHERCHEV(D11;I4:J15;2;FAUX),

Après validation, vous obtenez bien le mois en texte correspondant au numéro retourné par la fonction Mois() sur la date de naissance. Si vous changez la date de naissance en D7, vous remarquez que tous les calculs se mettent automatiquement à jour y compris celui permettant de récupérer l'information du mois de l'année en texte. En cellule D15, nous allons utiliser la fonction Excel Jour() qui permet d'extraire en nombre, le jour du mois d'une date.

• En cellule D15, taper la formule =JOUR(D7),

Plus intéressant, la cellule D17 propose d'afficher en chiffre, le jour de la semaine correspondant. Vous l'avez compris, ce chiffre permettra ensuite par le biais d'une rechercheV sur le second tableau, d'extraire le jour en texte.

Vous pourrez ainsi connaître votre jour de naissance ou retrouver facilement le jour de la semaine d'une date importante. La fonction permettant de retourner le chiffre entre 1 et 7 correspondant au jour de la semaine est la fonction Joursem(). Comme les autres, elle demande en paramètre, la date.

• En D17, taper le calcul =JOURSEM(D7),
• En D19, taper la formule pour récupérer le jour en texte soit : =RECHERCHEV(D17;K4:L10;2;FAUX)

Nous recherchons la valeur du jour de la semaine (D17) dans le tableau K4:L10 et récupérons la valeur correspondant en texte issue de la colonne 2. Comme vous le remarquez, ce tableau considère que le premier jour de la semaine est le Dimanche. C'est ainsi chez les anglo-saxons. Et c'est donc ainsi que doit être conçu le tableau de recherche afin que le retour du jour de la semaine ne soit pas erroné.


Tous les calculs sur les cellules de la partie gauche de la feuille sont réalisés. Les informations qu'ils retournent sont parfois intéressantes.

• Taper par exemple la date 01/01/2000 en D7 et valider par Entrée,

Nous voyons ainsi que le premier jour de l'année 2000 était un Samedi. Tous les calculs se mettent à jour automatiquement. Si vous tapez le 06/06/1944, vous apprenez que le jour du débarquement de la seconde guerre mondiale était un Mardi. Mais le calendrier Excel permet aussi de connaître le futur. Si vous tapez par exemple 25/12/2024, vous apprenez que Noël tombera un Mercredi en 2024.


Nous allons de nouveau masquer les colonnes qui ont servi à extraire les informations de date. Elles ne doivent surtout pas être supprimées, sinon les calculs qui se basent dessus, ne fonctionneront plus.

• Sélectionner les colonnes I, J, K et L par leurs étiquettes,
• Cliquer avec le bouton droit de la souris sur la sélection,
• Dans le menu contextuel, choisir Masquer.

Opérations sur les dates
La dernière partie de la feuille sur la droite consiste à calculer l'âge exact de la personne en années, mois et jours. Il s'agit de faire une différence entre chacune de ces informations avec les fonctions annee(), mois() et jour().

• En G7, saisir la formule =ANNEE(D5)-ANNEE(D7),
• En G9, saisir la formule =MOIS(D5)-MOIS(D7),
• En G11, saisir la formule =JOUR(D5)-JOUR(D7),

Vous obtenez bien l'âge ou encore l'écart entre la date du jour et la date que vous avez saisie. Cette décomposition donne l'écart en années, mois et jours. Si bien que si la date saisie intervient pour le mois, après celui de la date du jour, vous obtiendrez une année de plus avec une différence négative sur les mois, mais le résultat est juste. La remarque est similaire pour les jours sur lesquels nous pouvons obtenir une différence négative.


Conventionnellement, vous ajoutez une année au compteur tant que la date anniversaire n'est pas arrivée. Ainsi si nous considérons que nous sommes le 28/09/2016, une personne née le 29/09/1986 a encore 29 ans en années civiles et non 30. Nous souhaitons donc afficher l'âge de la personne en années civiles en tenant compte de cette remarque. Pour ce faire :

• Ajouter le titre Age civil fusionné sur les deux cellules G14 et H14,
• Prévoir la cellule du résultat en G15,
• Et taper le texte ans en H15 comme le propose la capture ci-dessous,

Il est possible de réaliser la différence entre la date du jour et la date saisie, soit la différence entre aujourdhui() et la date en D7. Excel renvoie alors le résultat sous forme du nombre de jours qui sépare les deux dates. Comme il y a 365 jours dans une année, ou plus précisément 365,25 pour tenir compte des années bissextiles, nous pouvons diviser cette différence par ce nombre. Il s'agira d'un nombre réel dont nous pouvons extraire la partie entière, soit le nombre d'années, grâce à la fonction Ent() d'Excel. La fonction Ent() arrondit un nombre à l'entier directement inférieur, c'est exactement ce que nous cherchons. Le calcul est donc le suivant : =ENT((aujourdhui()-date_saisie)/365,25). La division est bien sûr réalisée sur la différence des dates entre parenthèses à l'intérieur de la fonction Ent().

• En G15, réaliser le calcul de l'âge civil,

Les formats de date
Une date peut avoir des apparences différentes selon son format que vous pouvez personnaliser. Par défaut le format d'une date dans une cellule Excel est jj/mm/aaaa. On parle aussi de date courte. Ce format peut très facilement être transformé en date longue, c'est-à-dire avec le jour de la semaine en texte suivi du jour du mois, du mois en texte et enfin de l'année. Pour ce faire :

• Sélectionner la cellule de la date de naissance D7,
• Dans le ruban Accueil, cliquer sur la flèche des formats de nombres de la section Nombre,
• Dans la liste, choisir Date longue,

Comme l'illustre la capture ci-dessus, la date s'affiche avec beaucoup plus de détail. Donc toutes les manipulations que nous avons faites jusqu'alors peuvent s'obtenir sans calcul, en un clic. Ces fonctions restent néanmoins fort utiles dans de nombreux contextes. Ce format peut aussi se personnaliser. Ainsi nous pourrions choisir d'afficher notre date simplement sous la forme 27 octobre 1990 ou encore 27 oct 90. Pour cela, la cellule D7 étant sélectionnée :

• Cliquer sur la flèche des formats de nombres dans le ruban,
• Tout en bas de la liste, choisir Autres formats numériques,

Une boîte de dialogue apparaît sur la catégorie active Date. Au centre, tous les formats personnalisés sont disponibles. Mais vous pouvez le créer vous-même. Pour cela :

• Sélectionner la catégorie Personnalisée,
• Dans la zone type, remplacer le contenu par jj dans un premier temps,

L'aperçu affiche 27 dans notre cas, soit le jour du mois de notre date.

• Taper à la suite un espace suivi de jjjj,

L'aperçu affiche cette fois 27 samedi. Le fait d'augmenter le nombre de j, reconnu comme un symbole pour le format date par Excel, force à afficher le jour en texte, soit avec plus d'information.

• Remplacer le jj jjjj par l'inverse soit jjjj jj,
• Ajouter un tiret à la suite (-) pour faire office de séparateur,
• Taper mm,

Vous obtenez Samedi 27-10. Donc, selon la même remarque, si nous augmentons le nombre de m, nous améliorerons l'information.

• Ajouter à la suite, sans espace mm,

Cette fois le résultat est Samedi 27-octobre. De la même manière, si vous ajoutez -aa, vous obtenez Samedi 27-octobre-90 et Samedi 27-octobre-1990 avec jjjjjj-mmmm-aaaa. Le tiret peut être remplacé par n'importe quel séparateur de votre choix. Il n'est pas reconnu et donc pas interprété dans les formats date.