Excel et les dates (Partie 1)

Premier article de cette série traitant de la gestion et manipulation des dates avec Excel.

Il sera aussi complété au fur et à mesure par des exemples de formules travaillant sur les dates.

La 2ème partie traitant des heures est ici (Excel et les dates (Partie 2))

Comment sont gérés les dates

Les dates sont en réalité (débarrassées du maquillage du format de nombre) des nombres entiers (appelé "numéros de série") représentant un nombre de jours.

• Le début de ce décompte est le 0/01/1900. Oui c'est bien le zéro janvier ! On pourrait l'interpréter comme le 31/12/1899.
  • Le 0/01/1900 correspond donc à 0, le 1er février à 32 (les 31 jours de janvier + le 1er février).
• Ce système n'accepte pas l'affichage des valeurs négatives.
  • Ainsi la valeur -5 (soit le 26/12/1899) s'affichera sous forme d'une série de # si on lui applique un formatage Date.
  • Ces valeurs affichées "#####" ne seront pas exploitables via les fonctions standard de manipulation de date d'Excel mais seront disponibles pour toutes opérations d'addition ou de soustraction (puisque que les cellules contiennent bien un nombre entier).
  • Si l'on saisit une date inférieure au 0/01/1900, Excel la conservera sous forme d'une chaîne de texte (non exploitables via les fonctions standard de manipulation de date d'Excel). Voir l'article Manipuler des dates avant 1900, c'est possible !
• La limite maximale est le 31/12/9999 soit la valeur 2 958 465. Pour les dates suivantes, on obtiendra, ici aussi, une erreur d'affichage sous forme de #.

• Excel connait presque correctement les années bissextiles. Comment ça "presque" ?
  • Pastille anecdote historique : Excel, par soucis de compatibilité a copié un bug du leader du marché des tableurs d'il y a 30 ans (Lotus 1.2.3) !
    Il a ajouté un mercredi 29 février à l'année 1900 qui n'était pourtant pas une année bissextile.
  • Cette "erreur" à peu de chance d'impacter des calculs vu l'éloignement de cette plage de dates.
  • Par exemple :
    • Excel indique que le 28/02/1900 était un mardi alors que c'était un mercredi en réalité (j'ai une bonne mémoire, non ?).
    • Le calcul du nombre de jours entre 2 dates à cheval sur ce 29/02/1900 aura un jour de plus.

Exemple de calcul illustrant cette notion de numéros de série

Trouver le nombre de jours entre 2 dates

B2 : 20/09/2024
C2 : 23/09/2024

= C2 - B2 => 3

La fonction JOURS est donc inutile (je suis preneur d'un cas d'utilisation).

Sur d'ancienne version d'Excel, la cellule résultat était formatée en date et on obtenait à l'écran le 03/01/1900. Il suffisait de la repasser la cellule en format Standard par exemple.

Saisir des dates dans Excel

A la validation, Excel va convertir la saisie en numéros de série puis appliquer un format d'affichage.

Quelques exemples :

Information : Pour saisir la date du jour (date système), il y a le raccourcis ctrl + ;

Formatage des cellules en forme de dates

On utilise les codes "j", "m" et "a".

Ainsi avec la valeur 400 (correspondant au Dimanche 3 février 1901), le format "jj-mmmm-aa (jjjj)" affichera "03-février-01 (Dimanche)".

À noter :

• Le format "mmmmm" est peu connu.
• Le code, non référencé, "b" affiche 43 en 1900, 44 en 1901... (une idée de ce que c'est ?).

Génération de séries de dates

On peut incrémenter les jours, les jours ouvrés (supprime simplement les samedi et dimanche), les mois, les années.

Cela peut être fait notamment par une de ces 2 manipulations.

Après recopie via la balise active

Après le glissé via un menu si le glissé a été fait avec le bouton droit de la souris

Fonctions liées aux dates

Obtenir la date du jour

= AUJOURDHUI() => La date du jour
= MAINTENANT() => La date du jour et lʹheure

Ces fonctions sont "volatile", elles se mettent à jour à chaque saisie dans Excel et par effet dominos toutes les cellules qui en dépendes. Elles sont donc gourmandes en temps de calculs. Par optimisation, préférer AUJOURDHUI à MAINTENANT et si une grande colonne de formules utilise AUJOURDHUI, placer AUJOURDHUI dans une cellule et faite pointer les formules dessus.

Décomposer une date

L'intérêts de ces fonctions est de faire un test sur une partie de la date (dans des fonctions SI, RECHERCHEX, FILTRE... par exemple) ou d'en modifier une partie (fonction DATE).

B2 : 20/11/2024 ou "20/11/2024" '(une date ou du texte représentant une date)

= ANNEE(B2) => 2024
= MOIS(B2)  =>   11
= JOUR(B2)  =>   20

Le texte "20/11/2024" voir même "3 janvier 2024" est utilisable mais pas "mercredi 3 janvier 2024".

Composer une date

Exemple : Décaler une date de 3 mois et 1 jour.

B2 : 20/11/2024

= DATE( ANNEE(B2) ; MOIS(B2) +3 ; JOUR(B2) +1 ) => 21/02/2025

On peut remarquer dans l'exemple que le calcul du mois : MOIS(B2) + 3 soit 14 provoque un changement d'année (2+1 an).

Décaler une date de plusieurs mois

Dans ce cas particulier, Excel nous met à disposition la fonction MOIS.DECALER.

B2 : 20/11/2024

= MOIS.DECALER(B2 ; 3) => 21/02/2025

• Le décalage peut être en négatif.
• On peut générer des décalages de plusieurs années avec des valeurs multiples de 12.
• Si le jour résultat n'existe pas (par exemple 29/02, 30/02, 31/04...), le jour inférieur le plus proche sera retourné.
• On est limité par les bornes de validités des dates Excel.

Calcul de la fin du mois

Encore un décalage de date.
Par exemple calculer la date pour un paiement à "60 jours, fin de mois".

B2 : 20/11/2024

= FIN.MOIS(B2 ; 2) => 31/01/2025

Autre formule (quand FIN.MOIS nʹexistait pas)
= DATE(ANNEE(B2) ; MOIS(B2) + 3 ; 1 ) - 1

L'ancienne formule reste intéressante et peut être adapté à des cas plus complexes.
Son principe est de renvoyer le 1er du mois suivant puis de "reculer" d'un jour (-1 à la fin).

Calculer le numéro de semaine

Le mode de calcul du numéro de semaine peut varier d'un pays à l'autre. Pour la France la fonction NO.SEMAINE.ISO renvoie la bonne valeur.

Voir : Numérotation ISO des semaines — Wikipédia

= NO.SEMAINE.ISO(B2)

La fonction NO.SEMAINE permet d'accéder à d'autres modes de calcul pouvant donner des valeurs différentes de NO.SEMAINE.ISO.

Calculer un écart entre 2 dates

Si c'est un écart en jours, la simple soustraction des dates est suffisante.

Sinon on peut utiliser la fonction DATEDIF (voir l'article Des fonctions de calculs cachées d'Excel).

Variante : Utilisation de la fonction FRACTION.ANNEE.

B2 : 25/12/2014
B3 : 25/12/2024
Écart en années = ENT(FRACTION.ANNEE(B2 ; B3))
Écart en mois   = ENT(FRACTION.ANNEE(B2 ; B3)) * 12

Déterminer le jour de la semaine (lundi à dimanche)

On a à notre disposition la fonction JOURSEM.

Exemple : Trouver le prochain mercredi par rapport à une date.

'B2 : une date

= LET(DateDep;B2;
      JourS;3;
      JS;JOURSEM(DateDep;2);
      DateDep+JourS-JS+7*(JS>JourS) )

• DateDep : Date de départ.
• JourS : Le numéros du jour à trouver (1 pour lundi, 7 pour dimanche), ici 3 (mercredi).
• JS : Le numéros du jour de la date de départ (DateDep). La valeur 2 indique de commencer la semaine au lundi (valeur 1).
• Formule finale :
  • On décale la date de DateDep de la valeur de l'écart entre le jour de la semaine de DateDep et la valeur de JourS.
    =>Si DateDep est un lundi soit la valeur 1, on doit décaler DateDep de 2 jours (JourS - JS = 3-1) pour atteindre JourS (le mercredi).
  • Si DateDep a dépassé le mercredi, on ajoute 7 jours en plus (JourS - JS sera négatif faisant reculer la date).
    =>7*(JS>JourS) est une syntaxe rapide pour éviter une fonction Si (JS>JourS renvoie VRAI soit 1 ou FAUX soit 0).

Autres fonctions disponibles

• NB.JOURS.OUVRES et NB.JOURS.OUVRES.INTL permettent de compter le nombre de jour ouvrées entre 2 dates en excluant certains jours considérés comme chômés.
  • NB.JOURS.OUVRES considère 2 jours toujours chômés (SAMEDI et DIMANCHE) ainsi qu'une liste de jours à préciser.
  • NB.JOURS.OUVRES.INTL permet de choisir les jours toujours chômés (Aucun, que les DIMANCHE, les DIMANCHE et les MERCREDI...) ainsi qu'une liste de jours à préciser.

B2 : Lun 16/12/2024
B3 : Jeu 26/12/2024
B5 : Mer 25/12/2024 ' Date à exclure
B6 : Lun 30/12/2024 ' Date à exclure
 
= NB.JOURS.OUVRES.INTL(B2 ; B3 ;      11 ; B5:B6) = 9
 ' valeur 11 = dimanche uniquement (LMMJVS LM J)
 ' B6 inutile car hors plage de dates B2/B3
= NB.JOURS.OUVRES.INTL(B2 ; B3 ;"0010001";B5:B6) = 8
 ' "0010001" 0 pour ouvré, 1 pour chômé, selon l'ordre du lundi au dimanche (LM JVS LM J)
 ' B5 à la fois un mercredi et dans la liste d'exclusion

• SERIE.JOUR.OUVRE et SERIE.JOUR.OUVRE.INTL permettent de décaler une date de plusieurs jours (en avant ou en arrière) en renvoyant une date ouvrée selon la même logique que NB.JOURS.OUVRES et NB.JOURS.OUVRES.INTL.

B2 : Lun 16/12/2024
= SERIE.JOUR.OUVRE( B2 ; 4 ) = Ven 20/12/2024
= SERIE.JOUR.OUVRE( B2 ; 5 ) = Lun 23/12/2024
 ' On tombe sur un samedi donc on passe au lundi suivant

= SERIE.JOUR.OUVRE( B2 ; -5 ) = Lun 09/12/2024
= SERIE.JOUR.OUVRE( B2 ; -6 ) = Ven 06/12/2024
 ' On tombe sur un dimanche donc on passe au vendredi précédent 

Il reste :

• JOUR360 : Je la laisse aux banquiers.
• DATEVAL : Je n'ai jamais trouvé un intérêt à cette fonction (Toutes les fonctions de la catégorie Date font la conversion automatiquement et si nécessaire, il suffit de faire valeur * 1).

Les dates des jours fériés en France

Pour simplifier je vais ignorer les cas spécifiques régionaux.
Rien à dire sur les fêtes à date fixe (01/01, 01/05, 08/05, 14/07, 15/08, 1/11, 11/11, 25/12), ce qui nous intéresse ce sont les fêtes à dates mobiles.
Les lundis de Pâques, Ascension (Pâques + 40 jours), lundi de Pentecôte (Pâques + 50 jours) dépendent donc de la date de Pâques (dimanche). Celle-ci définie comme étant le 1er dimanche qui suit le 1ere pleine lune du printemps. Il faut donc s'en remettre aux astres !

Chez les copains LibreOffice, ils sont gentils et offrent la fonction DIMANCHEDEPAQUES à leurs utilisateurs mais nous on a droit à rien dans Excel ! 😭

On trouve de nombreuses formules sur internet pour déterminer cette date.

A1 : 2024

= PLANCHER(JOUR(MINUTE(A1/38)/2+56)&"/5/"&A1;7)-34
= PLAFOND(("17/4/"&A1)-TRONQUE(MOD(11*MOD(A1;19)+5;30)- 1,5);7)+1 
= TRONQUE(DATE(A1;3;MOD(349*MOD(A1/19;1)-6;29))/7)*7+29
= TRONQUE(DATE(A1;7;-CODE(STXT("NYdQ\JT_LWbOZeR]KU`";MOD(A1;19)+1;1)))/7)*7+8
= TRONQUE(365,25*A1-693894)+SI(OU (A1=1954;A1=1981;A1=2049;A1=2076);;7)+MOD(24-11*MOD (A1;19);30)-MOD(A1+TRONQUE(A1/4)+MOD(24-11*MOD(A1;19);30) +1;7) 

J'en ai une vingtaine dans ma collection 😊. Les fonctions employées sont variées et leur interprétation en est difficile. Elles ont une plage de validité qui n'est pas forcément indiquée.

Le problème c'est que je n'ai pas la source de ces formules. Sur quelle base de raisonnement se fondent elle (Méthode de Gauss, Conway, Meeus, doigt mouillé ...) ?

Je vous propose cette formule basée sur l'algorithme de Gauss pour des années >= 1583 (calendrier Grégorien) basé sur Calcul de la date de Pâques — Wikipédia (wikipedia.org).

=LET(y;D134;
     k;ENT(y/100);
     q;ENT(k/4);
     M;MOD(15-ENT((13+8*k)/25)+k-q;30);
     N;MOD(4+k-q;7);d;MOD((19*MOD(y;19)+M);30);
     e;MOD(2*MOD(y;4)+4*MOD(y;7)+6*d+N;7);
     H;22+d+e;
     DATE(y;3;H)-(e=6)*(OU(d=29;(d=28)*(MOD(11*M+11;30)<19)))*7)

Ou cette formule basée sur l'algorithme de Butcher-Meeus pour des années >= 1583 (calendrier Grégorien) basé sur Calcul de la date de Pâques — Wikipédia (wikipedia.org).

=LET(a;A1;
     n;MOD(a;19);
     c;ENT(a/100);
     u;MOD(a;100);
     s;ENT(c/4);
     t;MOD(c;4);
     p;ENT((c+8)/25);
     q;ENT((c-p+1)/3);
     e;MOD(19*n+c-s-q+15;30);
     b;ENT(u/4);
     d;MOD(u;4);
     L;MOD(2*t+2*b-e-d+32;7);
     h;ENT((n+11*e+22*L)/451);
     z;e+L-7*h+114;
     m;ENT(z/31);
     j;MOD(z;31);
     DATE(a;m;j+1))

Suite de l'article : Excel et les dates (Partie 2)

Merci pour votre attention bienveillante.