Formule de différence entre une date donnée et AUJOURDHUI
Bonsoir,
J'aurais besoin de vos lumières sur excel s'il vous plait
Voila je m'explique, j'essaie de calculer la date entre une date A et la date avec la fonction AUJOURDHUI, de plus j'aimerai que le résultat me soit donnée suivant les critères suivants : si la date ou '' durée '' entre A et AUJOURDHUI est > 1 année , alors '' renouveler '' et dans le cas contraire m'indiquer le nombre de jours et de mois. Voyez vous j'essaie depuis ce matin pour trouver une mise en forme conditionnelle ou avec les fonctions si pour trouver une solution, mais je n'y arrive pas je reste bloqué sur les débuts a savoir =SI(E2>AUJOURDHUI();"oui";"non".
Voila merci pour votre aide
Arselane
NB: je joins le fichier afin que cela soit plus clair.
Bonsoir,
Petite proposition (attention c'est long...) à copier dans la case C2
=SI(ESTERREUR(DATEDIF(B2;AUJOURDHUI();"Y"));"Erreur : la date de signature est antérieure à la date du jour";SI(DATEDIF(B2;AUJOURDHUI();"Y")>=1;"A RENOUVELER";SI(DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"m")=0;CONCATENER("Reste ";DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d");SI(DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d")=1;" jour avant renouvellement";" jours avant renouvellement"));SI(DATEDIF(DATE(ANNEE(B2)+1;MOIS(B2);JOUR(AUJOURDHUI()));DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d")=0;CONCATENER("Reste ";DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"m");" mois avant renouvellement");CONCATENER("Reste ";DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"m");" mois et ";DATEDIF(DATE(ANNEE(B2)+1;MOIS(B2);JOUR(AUJOURDHUI()));DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d");SI(DATEDIF(DATE(ANNEE(B2)+1;MOIS(B2);JOUR(AUJOURDHUI()));DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d")=1;" jour avant renouvellement";" jours avant renouvellement"))))))
J'aurais pu faire plus court, mais j'aime bien chipoter pour que la grammaire soit respectée...
J'espère que cela vous sera utile.
Morgane
Bonsoir a vous,
Merci pour votre réponse je vais tester tout cela, il va me faloir du temps afin de tout comprendre hahaha
Merci d'avoir pris le temps de répondre
Arslane
Pas de soucis. Si vous voulez une explication en "français", je vous fais ça demain une fois de retour devant un vrai clavier (là je répond depuis mon smartphone...)
Bonne soirée
Bonsoir ,
Alors vue que vous le proposez, j'aimerai bien s'il vous plait hahaha ,je n'arrive pas a trop déchifrer cela, j'attends votre retour demain alors
Bonne nuit
Arselane
Il y a la fonction Excel DATEDIF pour calculer la période entre 2 dates ca doit pouvoir simplifier un peu
http://www.mdf-xlpages.com/modules/publisher/item.php?itemid=76
de là en intégrant le Si >365jours ca donnerait :
=SI(ABS(AUJOURDHUI()-A2)>=365;"renouveler";(DATEDIF(MIN(A2;AUJOURDHUI());MAX(A2;AUJOURDHUI());"ym"))&" mois et "&DATEDIF(MIN(A2;AUJOURDHUI());MAX(A2;AUJOURDHUI());"md")&" jours")(je n'ai pu la tester qu'en anglais mais ca a l'air de marcher
=IF(ABS(TODAY()-A2)>=365,"renouveler",(DATEDIF(MIN(A2,TODAY()),MAX(A2,TODAY()),"ym"))&" mois et "&DATEDIF(MIN(A2,TODAY()),MAX(A2,TODAY()),"md")&" jours"))
Par contre vous êtes sur qu'il faut 'renouveler' uniquement si la date est dans plus d'un an, et juste indiquer le nombre mois si elle était supposée arriver le mois dernier?
Bon, en voulant vous expliquer la formule, je me suis rendue compte d'une erreur...
La voici corrigée (oui, elle est encore plus longue)
=SI(ESTERREUR(DATEDIF(B2;AUJOURDHUI();"Y"));"Erreur : la date de signature est antérieure à la date du jour";SI(DATEDIF(B2;AUJOURDHUI();"Y")>=1;"A RENOUVELER";SI(DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"m")=0;CONCATENER("Reste ";DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d");SI(DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d")=1;" jour avant renouvellement";" jours avant renouvellement"));SI(JOUR(AUJOURDHUI())-JOUR(B2)<=0;SI(DATEDIF(DATE(ANNEE(B2)+1;MOIS(B2);JOUR(AUJOURDHUI()));DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d")=0;CONCATENER("Reste ";DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"m");" mois avant renouvellement");CONCATENER("Reste ";DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"m");" mois et ";DATEDIF(DATE(ANNEE(B2)+1;MOIS(B2);JOUR(AUJOURDHUI()));DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d");SI(DATEDIF(DATE(ANNEE(B2)+1;MOIS(B2);JOUR(AUJOURDHUI()));DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d")=1;" jour avant renouvellement";" jours avant renouvellement")));SI(DATEDIF(DATE(ANNEE(B2)+1;MOIS(B2)-1;JOUR(AUJOURDHUI()));DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d")=0;CONCATENER("Reste ";DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"m");" mois avant renouvellement");CONCATENER("Reste ";DATEDIF(AUJOURDHUI();DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"m");" mois et ";DATEDIF(DATE(ANNEE(B2)+1;MOIS(B2)-1;JOUR(AUJOURDHUI()));DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d");SI(DATEDIF(DATE(ANNEE(B2)+1;MOIS(B2)-1;JOUR(AUJOURDHUI()));DATE(ANNEE(B2)+1;MOIS(B2);JOUR(B2));"d")=1;" jour avant renouvellement";" jours avant renouvellement")))))))
Comme promis, des explications:
Voici les différentes formules utilisées :
SI(test;valeur si vrai; valeur si faux) = il semble que vous la connaissez donc je m'attarde pas dessus
AUJOURDHUI() = renvoie la date du jour
DATEDIF(date de départ;date d'arrivée;valeur qu'on veut récupérer) = permet de calculer la différence entre 2 dates et nous renvoie au choix le nombre d'années entières, le nombre de mois entiers ou le nombre de jours.
DATE(annee;mois;jour) = permet de renvoyer sous le format date une date qu'on aura décomposée. Par exemple, DATE(19;09;10) renvoie 10/09/19.
ANNEE(date) = renvoie l'année d'une date qu'on lui donne. Exemple ANNEE(aujourdhui()) = 2019
MOIS(date) = idem mais pour le mois
JOUR(date)= idem mais pour le jour
CONCATENER(texte1;texte2;texte3) = permet d'écrire. Exemple, si je veux écrire "Aujourd'hui nous somme en 2019" alors je vais faire CONCATENER("Aujourd'hui nous sommes en ";ANNEE(AUJOURDHUI()))
ESTERREUR(formule) = vérifie si une formule qu'on entre renvoie un erreur Exemple ESTERREUR(75/0)=vrai
Maintenant, on procède par étape
1) la formule DATEDIF ne fonctionne que si on lui donne une date de départ antérieure à la date d'arrivée (logique). Pour éviter un joli #VALEUR, je vais tester si ma formule renvoie une erreur, ce qui veut dire qu'il y a eu une erreur de saisie dans la colonne "date de signature" et que la date de signature est postérieure à aujourd'hui. =>SI(ESTERREUR(DATEDIF(B2;AUJOURDHUI();"Y"));"Erreur : la date de signature est antérieure à la date du jour"; Si j'ai une erreur, alors j'écris "Erreur: la date de signature est antérieure à la date du jour"
2) Si il n'y a pas d'erreur, alors je regarde en premier si il y a 1 an ou plus entre aujourd'hui et la date de signature => SI(DATEDIF(B2;AUJOURDHUI();"Y")>=1 Si la différence en nombre d'années entre aujourd'hui et la date de signature est supérieur ou = 1, le "Y" renvoie ici le nombre d'années entières entre les 2 dates
3) Si il y a 1 an ou plus, alors j'écris "A RENOUVELER" => ;"A RENOUVELER";
4) Sinon, alors je vais vouloir afficher le nombre de mois et de jours qu'il reste avant le renouvellement. C'est à cause de ça que la formule est longue parce que je chipote... En effet, voici les différents cas de figures :
- le plus courant, il "Reste x mois et x jours"
- "Reste x mois et 1 jour" => je ne veux pas qu'il y ai de s à jour, puisqu'il n'en reste qu'un
- "Reste x mois" => reste un nombre pile de mois donc je ne veux pas qu'il m'écrive un truc du genre "Reste 3 mois et 0 jours"
- "Reste x jours" => s'il reste moins d'un mois, je ne veux pas qu'il m'écrive un truc du genre "Reste 0 mois et 25 jours"
- "Reste 1 jour" => pareil, je veux pas qu'il y ai de s à jour....
VOILA, maintenant, reste plus qu'à faire le détail...en imbriquant les différents SI qui vont vérifier toutes les conditions pour savoir comment je veux écrire le résultat.
La difficulté est de calculer le nombre de mois et jours entre la date de signature et aujourd'hui. En effet, si je fais DATEDIF(date de signature;aujourdhui();"d"), il va me donner le nombre de jours totaux. OR, moi je veux qu'il me donne le nombre de mois + le nombre de jours. Pour ça, je fais le calcul en 2 temps :
- je calcul le nombre de mois entiers, c'est facile => DATEDIF(date siganture;aujourdhui();"m")
- je calcul le nombre de jour entre aujourd'hui dans x mois et la date anniversaire. Exemple pour comprendre.
Pour écrire ça, j'utilise le fameux DATE (annee;mois;jour)
Du coup, la date anniversaire de la signature c'est le même jour, le même mois et l'année+1 ! donc je fais
DATE(annee(B2)+1;mois(B2);jour(B2))
Et aujourd'hui mais le même mois que la date anniversaire c'est DATE(annee(B2)+1;mois(B2);jour(aujourdhui()))
Je me doute que c'est pas simple expliqué sur papier mais j'espère que vous avez découverts quelques formules et surtout que ça fonctionne
Le calcul en se basant sur une comparaison avec 365 jours est bien aussi mais pas totalement exacte puisque toutes les années ne font pas 365 jours... La encore, je chipote
Bonjour Rrradassse
=SI(ABS(AUJOURDHUI()-A2)>=365;"renouveler";(DATEDIF(MIN(A2;AUJOURDHUI());MAX(A2;AUJOURDHUI());"ym"))&" mois et "&DATEDIF(MIN(A2;AUJOURDHUI());MAX(A2;AUJOURDHUI());"md")&" jours")
Attention à l'emploi de "md" ... https://forum.excel-pratique.com/viewtopic.php?p=794647#p794647 + proposition de Jean-Eric qui suit ...
Bonjour a vous tous,
Un grand merci a vous pour votre aide et pour le temps que vous prenez pour répondre et expliquer votre façon de d'utiliser ces formules.
Cela me sera d'une très grande aide !
Merci encore
Bonne journée
NB: je reviens vers vous dés que j'aurais tout assimilé haha
Bonsoir,
Bon la formule est parfaite et fonctionne a merveille ! Merci encore pour ton aide Bikifairie, malgrés que je pense pas que je pourrais la refaire par moi même hahaha !
Merci aussi a Steelson et a Rrradassse pour avoir contribué a la solution de mon problème.
Bonne soirée a vous