Travailler avec les dates dans Excel

Pas toujours facile de travailler avec les dates dans Excel...

Comment changer le format des dates et heures ?  Comment effectuer des calculs simples, comme la différence entre deux dates, connaître le nombre de jours ouvrés, trouver le premier jour ou le dernier jour du mois ? Comment comparer des dates avec la fonction SI ?

Planète Excel vous dit tout sur les dates !

Comment sont stockées les dates et heures dans Excel ?

Pour Excel, les dates et heures sont des nombres, rien que des nombres !

En effet, les dates sont stockées sous forme de numéros de série. Le calendrier d’Excel commence le 01/01/1900, et cette date porte donc le n°1, le 02/01/1900 porte le n°2, etc… :

1	01/01/1900 0:00
2	02/01/1900 0:00
3	03/01/1900 0:00
...	...
45004	19/03/2023 0:00
45005	20/03/2023 0:00
45006	21/03/2023 0:00

Les heures sont stockées dans la partie décimale. Ainsi, 1,5 correspond au 01/01/1900 à midi :

1,5	01/01/1900 12:00
2,5	02/01/1900 12:00
3,5	03/01/1900 12:00
...	...
45004,5	19/03/2023 12:00
45005,5	20/03/2023 12:00
45006,5	21/03/2023 12:00

Excel gère directement les dates dès qu’il détecte que l’on saisit une donnée respectant le format date. Par exemple : « 10/01/2020 », ou « 10 janvier 2020 », ou « vendredi 10 janvier 2020 ».
Le n° de série est donc généralement une donnée interne, sur laquelle nous n’avons pas à travailler.

Il faut retenir que pour Excel, une date est un nombre. Comme tout nombre, il est aligné à droite par défaut. Une saisie alignée à gauche montre que la donnée a été interprétée comme du texte, et non comme une date.

Format des dates et heures

Excel interprète votre saisie de date pour lui attribuer le format le plus adapté.

Par exemple, saisissez 01/01/2023 : cela correspond au format appelé date courte. Mais vous pouvez choisir un format plus adapté en cliquant sur la liste déroulante disponible dans le groupe Nombre (onglet Accueil) dans le ruban :

La boîte de dialogue de Format de cellule (accessible via le lanceur du groupe Nombre) vous offre un large choix de possibilités :

Pour en savoir plus, vous trouverez dans ce cours consacré aux formats tout ce qu'il faut connaître sur les formats applicables aux dates et heures.

Pour répondre à un besoin particulier, vous pouvez également définir un format personnalisé pour les dates et heures.

Réaliser des calculs sur les dates

Quel est le numéro du jour dans le mois ?

JOUR(date) indique le n° du jour du mois. Par exemple, si la cellule A1 contient 15/02/2023, =JOUR(A1) donne 15.

Quel est le jour suivant une date ?

Pour obtenir le jour suivant une date, il suffit d’ajouter 1 à cette date.
Par exemple, pour une date 01/01/2023 contenu dans la cellule A1, la formule =A1 + 1 donne 02/01/2023.

Comment connaître le numéro du jour dans la semaine ?

JOURSEM(date, 2) indique le n° du jour de la semaine. Par exemple, si A1 contient 15/02/2023, =JOURSEM(A1, 2) donne 3 (mercredi).

Quel est le n° du mois d'une date ?

MOIS(date) indique le n° du mois. Par exemple, si A1 contient 15/02/2023, =MOIS(A1) donne 2.

Comment obtenir l’année d'une date ?

ANNEE(date) indique le millésime. Par exemple, si A1 contient 15/02/2023, =ANNEE(A1) donne 2023

Comment obtenir le premier jour / le dernier jour du mois ?

Excel dispose de la fonction FIN.MOIS pour connaître le dernier jour du mois d’une date donnée. Dans cette fonction, le premier paramètre correspond à la date, le second à un décalage à opérer (en nombre de mois).

Par exemple, =FIN.MOIS(A1;0) donne le dernier jour du mois de la date contenue dans la cellule A1 (le 0 indique que l’on ne souhaite pas décaler le mois).

En revanche, il n’existe pas de formule pour connaître le premier jour du mois d’une date donnée. Mais on peut utiliser les fonctions DATE, ANNEE et MOIS pour reconstituer la date. La fonction DATE(année ; mois ; jour) permet de reconstituer une date à partir des éléments jour/mois/année séparés.
Ainsi, =DATE(ANNEE(A1);MOIS(A1);1) donne le premier jour du mois de la date contenue dans la cellule A1.

On peut également utiliser la fonction FIN.MOIS pour y arriver, en se décalant d’un mois : si =FIN.MOIS(A1;-1) donne le dernier jour du mois précédent, il suffit d’ajouter un jour pour obtenir le premier jour du mois : =FIN.MOIS(A1;-1)+1.

Comment obtenir le premier jour du mois qui suit ?

On peut utiliser les fonctions DATE, ANNE et MOIS. Ainsi, =DATE(ANNEE(A1);MOIS(A1)+1;1) donne le premier jour du mois qui suit la date contenue dans la cellule A1.
On peut également utiliser la fonction FIN.MOIS sur la date concernée, et ajouter un jour : =FIN.MOIS(A1 ;0)+1.

Quel est le jour ouvré suivant ?

Pour obtenir le jour ouvré suivant une date, on utilise la fonction SERIE.JOUR.OUVRE.
Par exemple, pour une date 17/02/2023 contenu dans la cellule A1, la formule =SERIE.JOUR.OUVRE(A1;1) donne 20/02/2023.

Attention : la fonction SERIE.JOUR.OUVRE ne prend en compte les jours fériés que si ceux-ci sont fournis en 3e paramètre...

=SERIE.JOUR.OUVRE(A1;1;A2:A6) vous permet d'obtenir le jour ouvré suivant, en tenant compte des dates de jours fériés stockées dans les cellules A2 à A6.

Comment obtenir le nombre de jours ouvrés d’un mois ?

La fonction NB.JOURS.OUVRES(date début ; date fin) donne le nombre de jours ouvrés entre deux dates.
La date de début est facile à fournir à Excel, mais la date de fin est variable en fonction du nombre de jours du mois.
Pour la connaître, la fonction MOIS.DECALER permet de décaler une date donnée du nombre de mois indiqués en paramètre. Par exemple, =MOIS.DECALER(A1 ;1) permet d’obtenir la date contenue dans la cellule A1 décalée d’un mois.
Supposons que la cellule A1 contienne la date du 01/01/2023. =MOIS.DECALER(A1 ;1) donnera 01/02/2023. Il suffit de retrancher un jour pour obtenir le dernier jour du mois précédent : =MOIS.DECALER(A1 ;1)-1.
Il faut donc  combiner ces deux fonctions pour connaître le nombre de jours ouvrés du mois de la date contenue en A1 :

=NB.JOURS.OUVRES(A1;MOIS.DECALER(A1;1)-1)

Attention : la fonction NB.JOUR.OUVRE ne prend en compte les jours fériés que si ceux-ci sont fournis en 3e paramètre...

Comment calculer la différence entre 2 dates ?

Différence entre 2 dates en jours

C'est très simple, il suffit d’effectuer une soustraction ! Dans cet exemple, nous calculons la différence entre la date stockée dans la cellule B1 et la date stockée dans la cellule A1, avec la formule =B1-A1 :

On peut également utiliser la fonction DATEDIF. Sa syntaxe est la suivante :

=DATEDIF( date_debut; date_fin; opérateur )

Voici les opérateurs que vous pouvez utiliser avec cette fonction :

« y » : différence en années (year)
« m » : différence en mois (month)
« d » : différence en jours (day)
« ym » : différence en mois, après retrait des années
« yd » : différence en jours, après retrait des années
« md » : différence en jours, après retrait des années et des mois

Dans notre exemple, où l’on cherche une différence en jours, la formule serait donc =DATEDIF( A1; B1;"d") :

Différence entre deux dates en années, mois, jours

Pour connaître un âge en année, mois et jours, il faut cumuler plusieurs fonctions DATEDIF : une pour l’écart en année (paramètre "y"), une pour l’écart en mois en soustrayant les années déjà comptabilisées (paramètre "ym"), une pour l’écart en jours en soustrayant les années et les mois déjà comptabilisés (paramètre "md").

Voici la formule :

=DATEDIF(A1;B1;"y") & " ans "
& DATEDIF(A1 ;B1;"ym") & " mois "
& DATEDIF(A1;B1;"md") & " jours"

Comment calculer l'âge à la date d'aujourd'hui ?

C’est la fonction AUJOURDHUI() qui nous donne la date courante.

Pour calculer un âge par rapport à la date du jour, il faut utiliser la fonction AUJOURDHUI, couplée à DATEDIF :

=DATEDIF(A1;AUJOURDHUI();"y")

Comparer des dates avec une fonction SI

On peut comparer des dates contenues dans des cellules avec la fonction SI. Par exemple :

=SI(A1>B1;"délai dépassé ;"délai en cours")

Il est possible de comparer également une date saisie directement dans la formule. Dans ce cas, il faut saisir la date avec les fonctions DATEVAL ou DATE, de la manière suivante :

=SI(A1>DATEVAL("10/10/2023");"délai dépassé ;"délai en cours")
=SI(A1>DATE(2023;10;10);"délai dépassé ;"délai en cours")

Attention, la fonction suivante ne fonctionnerait pas :

=SI("22/01/2023"> "10/10/2023");"délai dépassé ;"délai en cours")

En effet, Excel comprend qu’il faut comparer deux chaînes de caractères, et non pas des dates !

Comment reconstituer une date à partir d’un nombre de type "10122023" ?

On peut reconstituer une date par concaténation. Si ce nombre est contenu dans la cellule A1, on peut construire la formule suivante dans la cellule B1 :

=GAUCHE(A1;2) & "/" & STXT(A1;3;2) & "/" & DROITE(A1;4)

Cette solution a une limite si le 0 du début n’apparaît pas pour une date de type 03072019.
Dans ce cas, on va réaliser un calcul intermédiaire pour tester d’abord la longueur de la date en nombre de caractères, grâce aux fonctions SI et NBCAR. Si cette longueur est inférieure à 8, il faut ajouter un 0 devant la date. On saisit donc la formule suivante en B1 :

=SI(NBCAR(A1)<8 ; "0" & A1 ;A1)

Puis on reconstitue la date dans la cellule C1 avec la formule :

=GAUCHE(B1;2) & "/" & STXT(B1;3;2) & "/" & DROITE(B1;4)