Demande de l'utilisateur du forum
Calculer le nombre de mois entre 2 dates, certaines dates pouvant être avant 1900.
Problématique
Excel est limité à la date du 0/1/1900 (j'ai bien mis 0 janvier !).
Toute date avant cette date limite sera considérée comme du texte et ne pourra donc par être exploitée/manipulée comme les autres dates.
Solution proposée
Principe
On va créer une fonction qui va décaler les dates de 800 ans (un multiple de 400, voir Année bissextile Wikipedia) dans le futur et ainsi pouvoir bénéficier des outils d'Excel de manipulation de dates.
Création de la fonction DateP800
On va définir le nom DateP800 ainsi (Via le ruban Formules>Définir un nom).
=LAMBDA(DateE;
LET(DateT;TEXTE(DateE;"jj/mm/aaaa");
MDt ;FRACTIONNER.TEXTE(DateT;"/");
--JOINDRE.TEXTE("/";;MDt+{0.0.800})))Quelques explications :
- DateT : Conversion de toutes les dates en texte avec le format attendu (fonction TEXTE). Les dates déjà en "texte" ne seront pas modifiées.
- MDt : Matrice de 3 valeurs correspondant à la date (Jour, Mois, Année).
- Résultat LAMBDA : Concaténation des valeurs de la matrices dont la 3eme valeurs (Année) à été augmenté de 800 et conversion en numérique (-- avant JOINDRE.TEXTE).
- Il faudra formater la cellule contenant le résultat en format de nombre Date pour le voir affiché comme une date dans la cellule.
Voir aussi : Les bases de la fonction LAMBDA - Bienvenue à 1forme.fr
Exemples d'utilisation
Nombre d années : =DATEDIF(DateP800(A2);DateP800(B2);"d")
Nombre de mois : =DATEDIF(DateP800(A2);DateP800(B2);"m")
Nombre de jours : =DATEDIF(DateP800(A2);DateP800(B2);"y")
N° de Semaine : =NO.SEMAINE.ISO(DateP800(A2))
Allons plus loin
Problématique
Certains calculs ne pourront pas être réalisés, ceux devant renvoyer une date avant 1900 !
Solution proposée
Principe
Créer une fonction pour "revenir" à une date 800 ans en arrière.
Création de la fonction DateM800
On va définir le nom DateM800 ainsi (Via le ruban Formules>Définir un nom).
=LAMBDA(DateE;
LET(DateT;TEXTE(DateE;"jj/mm/aaaa");
MDt ;FRACTIONNER.TEXTE(DateT;"/");
JOINDRE.TEXTE("/";;MDt+{0.0.-800})))Quelques explications :
- La 3eme valeur de la matrice passe à -800.
- Le résultat de la fonction LAMBDA n'est plus converti en numérique (plus de -- avant JOINDRE.TEXTE).
Exemples d'utilisation
Décaler la date de 12 mois : =DateM800(MOIS.DECALER(DateP800(A2);12))Remarque :
On peut créer une fonction Mois.Decaler2 par exemple pour simplifier cette formule.
=LAMBDA(Date1;NbMois;DateM800(MOIS.DECALER(DateP800(Date1);NbMois)))On aura ainsi cette formule pour décaler de 12 mois une date.
=Mois.Decaler2(A2;12)Une version "anciennes" fonctions Excel
Basée sur une formule de DanielCo (Décalage de la date en B1 de 800 ans).
=DATE(DROITE(TEXTE(B1;"jj/mm/aaaa");4)+800;
STXT(TEXTE(B1;"jj/mm/aaaa");
CHERCHE("/";TEXTE(B1;"jj/mm/aaaa"))+1;
NBCAR(TEXTE(B1;"jj/mm/aaaa"))-CHERCHE("/";TEXTE(B1;"jj/mm/aaaa"))-5);
GAUCHE(TEXTE(B1;"jj/mm/aaaa");CHERCHE("/";TEXTE(B1;"jj/mm/aaaa"))-1))Version simplifiée avec un LET
=LET(Dt;TEXTE(B1;"jj/mm/aaaa");
DATE(DROITE(Dt;4)+800;
STXT(Dt;CHERCHE("/";Dt)+1;NBCAR(Dt)-CHERCHE("/";Dt)-5);
GAUCHE(Dt;CHERCHE("/";Dt)-1)))Merci pour votre attention bienveillante.