Excel : calcul de la différence entre 2 dates
Résolu
patrick1870
Messages postés
66
Statut
Membre
-
patrick1870 Messages postés 66 Statut Membre -
patrick1870 Messages postés 66 Statut Membre -
Bonjour,
Beaucoup d’entre vous en voyant l’objet de mon post doivent penser que le sujet a déjà été traité et résolu sur le forum.
Pourtant, j’ai bien cherché sur le forum et sur le net et ma conclusion est qu’on ne sait pas calculer l’écart entre deux dates : « il s’est écoulé X années plus Y mois et Z jours »
Calculer le nombre d’années écoulées entre deux dates n’est finalement pas très compliqué : la fonction DATEDIF(date_début ; date_fin ;"y") retourne ce nombre.
Le nombre de mois n’est guère plus difficile avec la fonction DATEDIF(date_début ; date_fin ;"ym") qui retourne le nombre de mois entiers s’ajoutant aux années entières écoulées (telles que calculées avec le paramètre "y").
Les difficultés commencent lorsqu’on veut calculer le nombre de jours complétant les nombres d’années et de mois.
Il existe bien une fonction DATEDIF(date_début ; date fin ;"md") censée retourner le nombre de jours à ajouter au nombre d’années et de mois (tels que calculés avec les paramètres "y" et "ym"). Mais, Microsoft recommande de ne pas l’utiliser car elle délivre un résultat faux !!! (ce que j’ai pu vérifier)
Toutefois Microsoft conscient de ses responsabilités nous livre la solution permettant de calculer le nombre de jours s’ajoutant aux nombres d’années et de mois. (https://support.microsoft.com/fr-fr/office/calculer-la-diff%C3%A9rence-entre-deux-dates-8235e7c9-b430-44ca-9425-46100a162f38)
NB : dans l’exemple donné dans le tuto (provenant de l’aide Excel américaine) les dates sont au format m/j/aaaa.
LA FORMULE MAGIQUE :
(dans la cellule D7 se trouve la date de début – 1er janvier 2014- et dans la cellule E7 se trouve la date de fin – 6 mai 2016-)
- Le calcul du nombre d’années : =DATEDIF(D17;E17;"y") résultat 2 années
- Le calcul du nombre de mois : =DATEDIF(D17;E17;"ym") résultat 4 mois
- Le calcul du nombre de jours : =E17-DATE(ANNEE(E17);MOIS(E17);1) résultat 5 jours. Cette formule fait l'objet d'explications détaillées, c'est le moins qu'on puisse faire.
La différence entre le 1er janvier 2014 et le 1er mai 2016 est bien de 2 ans et 4 mois comme retourné par les formules et le nombre de jours semble être correct puisque entre le 1er mai et le 6 mai il s’écoule bien 5 jours.
Mais les apparences sont trompeuses. Modifiez la date de début par exemple au lieu de 1er janvier, indiquez 2 janvier, ou 13 mars ou 25 octobre … ou n’importe quelle autre date le résultat du nombre de jours est toujours 5 jours.
Cela ne doit pas nous étonner : les deux premières formules prennent en compte la date de début et la date de fin (D17 et E17) tandis que celle du nombre de jours ne dépend que de la date de fin (E17).
En fait, la formule alambiquée du calcul du nombre de jours proposée par Microsoft peut se résumer à =JOUR(E17)-1
Le fait que le nombre de jours dans l’exemple donné par l’aide Excel soit juste n’est qu’une coïncidence (involontaire ?) car toute autre date de fin dont le jour est différent de 6 aurait donné un résultat faux, de même que toute autre date de début différente du 1er du mois.
Le problème demeure donc : comment déterminer l’écart entre 2 dates en années + mois + jours ?.
Je tourne en rond sur ce problème depuis 2 jours (bien calculés !) … et je n’arrive à rien. Et pourtant j’ai l’impression que ce n’est pas si compliqué.
Quelqu’un aura-t-il une idée pour résoudre le problème ?
Merci d’avance.
(mes excuses pour la longueur du post ... je n'ai pas pris le temps de faire plus court )
Beaucoup d’entre vous en voyant l’objet de mon post doivent penser que le sujet a déjà été traité et résolu sur le forum.
Pourtant, j’ai bien cherché sur le forum et sur le net et ma conclusion est qu’on ne sait pas calculer l’écart entre deux dates : « il s’est écoulé X années plus Y mois et Z jours »
Calculer le nombre d’années écoulées entre deux dates n’est finalement pas très compliqué : la fonction DATEDIF(date_début ; date_fin ;"y") retourne ce nombre.
Le nombre de mois n’est guère plus difficile avec la fonction DATEDIF(date_début ; date_fin ;"ym") qui retourne le nombre de mois entiers s’ajoutant aux années entières écoulées (telles que calculées avec le paramètre "y").
Les difficultés commencent lorsqu’on veut calculer le nombre de jours complétant les nombres d’années et de mois.
Il existe bien une fonction DATEDIF(date_début ; date fin ;"md") censée retourner le nombre de jours à ajouter au nombre d’années et de mois (tels que calculés avec les paramètres "y" et "ym"). Mais, Microsoft recommande de ne pas l’utiliser car elle délivre un résultat faux !!! (ce que j’ai pu vérifier)
Toutefois Microsoft conscient de ses responsabilités nous livre la solution permettant de calculer le nombre de jours s’ajoutant aux nombres d’années et de mois. (https://support.microsoft.com/fr-fr/office/calculer-la-diff%C3%A9rence-entre-deux-dates-8235e7c9-b430-44ca-9425-46100a162f38)
NB : dans l’exemple donné dans le tuto (provenant de l’aide Excel américaine) les dates sont au format m/j/aaaa.
LA FORMULE MAGIQUE :
(dans la cellule D7 se trouve la date de début – 1er janvier 2014- et dans la cellule E7 se trouve la date de fin – 6 mai 2016-)
- Le calcul du nombre d’années : =DATEDIF(D17;E17;"y") résultat 2 années
- Le calcul du nombre de mois : =DATEDIF(D17;E17;"ym") résultat 4 mois
- Le calcul du nombre de jours : =E17-DATE(ANNEE(E17);MOIS(E17);1) résultat 5 jours. Cette formule fait l'objet d'explications détaillées, c'est le moins qu'on puisse faire.
La différence entre le 1er janvier 2014 et le 1er mai 2016 est bien de 2 ans et 4 mois comme retourné par les formules et le nombre de jours semble être correct puisque entre le 1er mai et le 6 mai il s’écoule bien 5 jours.
Mais les apparences sont trompeuses. Modifiez la date de début par exemple au lieu de 1er janvier, indiquez 2 janvier, ou 13 mars ou 25 octobre … ou n’importe quelle autre date le résultat du nombre de jours est toujours 5 jours.
Cela ne doit pas nous étonner : les deux premières formules prennent en compte la date de début et la date de fin (D17 et E17) tandis que celle du nombre de jours ne dépend que de la date de fin (E17).
En fait, la formule alambiquée du calcul du nombre de jours proposée par Microsoft peut se résumer à =JOUR(E17)-1
Le fait que le nombre de jours dans l’exemple donné par l’aide Excel soit juste n’est qu’une coïncidence (involontaire ?) car toute autre date de fin dont le jour est différent de 6 aurait donné un résultat faux, de même que toute autre date de début différente du 1er du mois.
Le problème demeure donc : comment déterminer l’écart entre 2 dates en années + mois + jours ?.
Je tourne en rond sur ce problème depuis 2 jours (bien calculés !) … et je n’arrive à rien. Et pourtant j’ai l’impression que ce n’est pas si compliqué.
Quelqu’un aura-t-il une idée pour résoudre le problème ?
Merci d’avance.
(mes excuses pour la longueur du post ... je n'ai pas pris le temps de faire plus court )
A voir également:
- Excel : calcul de la différence entre 2 dates
- Liste déroulante excel - Guide
- Word et excel gratuit - Guide
- Déplacer colonne excel - Guide
- Si ou excel - Guide
- Excel trier par ordre croissant chiffre - Guide
7 réponses
Bonjour Patrick,
Pas de souci pour la longueur du post... tout est très clair dans vos explications.
Sujet très intéressant...
J’espère que quelqu’un nous ouvrira une piste.
Pas de souci pour la longueur du post... tout est très clair dans vos explications.
Sujet très intéressant...
J’espère que quelqu’un nous ouvrira une piste.
Salut à tous les deux
Pour obtenir le nombres de jours restant entre deux dates il faut utiliser la même formule que pour les années et les mois avec l'indice "md" =DATEDIF(D17;E17;"md") ce qui donne bien 5 jours pour les dates indiquées dans la question,
3 si on met la date de début au 3/1/2014
23 jours si la date de début est 13/3/2014
C'est une simple soustraction car les dates dans Excel sont des nombres ;
pour les mêmes dates 1/1/2014 et 6/5/2016 la formule =DATEDIF(D17;E17;"d") donne 856 jours ;
si on transforme les dates au format nombre sans les décimales ça donne 42496-41640 soit 856
Si on veux compter les deux extrémités il faut ajouter 1 jour ; par exemple la différence entre le 1/1/2021 et AUJOURDHUI() soit 24/1/2021 est de 23 jours donc +1 si on veut 24
Sinon une autre formule pour le nombre de jours
Cordialement
Pour obtenir le nombres de jours restant entre deux dates il faut utiliser la même formule que pour les années et les mois avec l'indice "md" =DATEDIF(D17;E17;"md") ce qui donne bien 5 jours pour les dates indiquées dans la question,
3 si on met la date de début au 3/1/2014
23 jours si la date de début est 13/3/2014
C'est une simple soustraction car les dates dans Excel sont des nombres ;
pour les mêmes dates 1/1/2014 et 6/5/2016 la formule =DATEDIF(D17;E17;"d") donne 856 jours ;
si on transforme les dates au format nombre sans les décimales ça donne 42496-41640 soit 856
Si on veux compter les deux extrémités il faut ajouter 1 jour ; par exemple la différence entre le 1/1/2021 et AUJOURDHUI() soit 24/1/2021 est de 23 jours donc +1 si on veut 24
Sinon une autre formule pour le nombre de jours
=E17-MOIS.DECALER(D17;DATEDIF(D17;E17;"m"))ce qui donne 5 et ce nombre change si la date de début est changée
Cordialement
Bonjour papyluc et merci pour ta réponse (je suis papy comme toi ;) )
Je n'ignorais pas l'option "md" de datediff ... je la mentionne dans dans mon post ...
mais Microsoft recommande de ne pas l'utiliser car elle retourne parfois des valeurs fausses
J'ai pu le vérifier mais je ne me rappelle pas dans quelles circonstances.
L'autre solution que tu proposes m'intéresse, même si je ne la comprends pas totalement pour le moment (je suis un débutant dans les formules Excel autres que les basiques et cette fonction MOIS.DECALER m'est totalement inconnue).
Encore merci pour ton aide.
Cdlt
Patrick
Je n'ignorais pas l'option "md" de datediff ... je la mentionne dans dans mon post ...
mais Microsoft recommande de ne pas l'utiliser car elle retourne parfois des valeurs fausses
J'ai pu le vérifier mais je ne me rappelle pas dans quelles circonstances.
L'autre solution que tu proposes m'intéresse, même si je ne la comprends pas totalement pour le moment (je suis un débutant dans les formules Excel autres que les basiques et cette fonction MOIS.DECALER m'est totalement inconnue).
Encore merci pour ton aide.
Cdlt
Patrick
On décale une date d'autant de mois de l'on veut (en + ou en -) pour déterminer une nouvelle date à soustraire de la date de fin ce qui donne le nombre de jours restant
Dans cette formule on détermine le nombre de mois avec DATEDIF pour avoir des mois entiers ce qui fait 28 (1/1/2014 + 28 mois donne 1/5/2016) que l'on soustrait à la date de fin 6/5/2016 =5 jours
Cordialement
Dans cette formule on détermine le nombre de mois avec DATEDIF pour avoir des mois entiers ce qui fait 28 (1/1/2014 + 28 mois donne 1/5/2016) que l'on soustrait à la date de fin 6/5/2016 =5 jours
Cordialement
Bonjour,
pour moi la question est triviale et n'a pas de réponse exacte.
Prenons un exemple simple :
du 28/02/2021 au 01/04/2021, tout le monde est d'accord, ça fait 1 mois et 2 jours
Prenons maintenant du 02/02/2021 au 28/04/2021
La seule réponse exacte est 1 mois et 55 jours, mais non satisfaisante.
On voit que ça fait 2 mois et quelques, et pour ce 'quelques' on ne peut que se contenter d'une approximation d'un mois à 30 jours (1 mois = 30.4375 en moyenne)
Ce qui donne 2 mois et 25 jours, sujet à caution.
eric
pour moi la question est triviale et n'a pas de réponse exacte.
Prenons un exemple simple :
du 28/02/2021 au 01/04/2021, tout le monde est d'accord, ça fait 1 mois et 2 jours
Prenons maintenant du 02/02/2021 au 28/04/2021
La seule réponse exacte est 1 mois et 55 jours, mais non satisfaisante.
On voit que ça fait 2 mois et quelques, et pour ce 'quelques' on ne peut que se contenter d'une approximation d'un mois à 30 jours (1 mois = 30.4375 en moyenne)
Ce qui donne 2 mois et 25 jours, sujet à caution.
eric
Si tu peux, mais c'est un peu lourd.
Il faut tenir compte des cas particuliers comme jour début = 1er jour du mois, jour de fin=dernier jour du mois, début et fin dans le même mois.
Pour simplifier la lecture j'ai laissé le travail découpé, à toi de voir si tu veux tout regrouper dans une formule unique.
Je te laisse valider la méthode sur plus de cas, pas impossible que des cas particuliers m'aient échappé.
https://www.cjoint.com/c/KAyrZQYnZtX
Sinon ça peut être une fonction personnalisée en vba.
eric
Il faut tenir compte des cas particuliers comme jour début = 1er jour du mois, jour de fin=dernier jour du mois, début et fin dans le même mois.
Pour simplifier la lecture j'ai laissé le travail découpé, à toi de voir si tu veux tout regrouper dans une formule unique.
Je te laisse valider la méthode sur plus de cas, pas impossible que des cas particuliers m'aient échappé.
https://www.cjoint.com/c/KAyrZQYnZtX
Sinon ça peut être une fonction personnalisée en vba.
eric
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
Bonsoir,
La différence entre le 1er janvier 2014 et le 1er mai 2016 est bien de 2 ans et 4 mois comme retourné par les formules et le nombre de jours semble être correct puisque entre le 1er mai et le 6 mai il s’écoule bien 5 jours.
Mais les apparences sont trompeuses. Modifiez la date de début par exemple au lieu de 1er janvier, indiquez 2 janvier, ou 13 mars ou 25 octobre … ou n’importe quelle autre date le résultat du nombre de jours est toujours 5 jours.
Voir, pj
https://www.cjoint.com/c/KAyrZ4E3iQj
Slts
La différence entre le 1er janvier 2014 et le 1er mai 2016 est bien de 2 ans et 4 mois comme retourné par les formules et le nombre de jours semble être correct puisque entre le 1er mai et le 6 mai il s’écoule bien 5 jours.
Mais les apparences sont trompeuses. Modifiez la date de début par exemple au lieu de 1er janvier, indiquez 2 janvier, ou 13 mars ou 25 octobre … ou n’importe quelle autre date le résultat du nombre de jours est toujours 5 jours.
Voir, pj
https://www.cjoint.com/c/KAyrZ4E3iQj
Slts
Bonjour eric et merci pour ta réponse
Toutefois, et tu sembles le confirmer, en calculant "à la main" sans l'aide d'Excel on peut déterminer le délai exact entre deux dates sous la forme années + mois + jours.
Pourquoi ne pourrait-on pas faire avec Excel ce que tout le monde peut faire avec un papier et un crayon ?
Encore merci pour ta contribution.
cdlt patrick
Toutefois, et tu sembles le confirmer, en calculant "à la main" sans l'aide d'Excel on peut déterminer le délai exact entre deux dates sous la forme années + mois + jours.
Pourquoi ne pourrait-on pas faire avec Excel ce que tout le monde peut faire avec un papier et un crayon ?
Encore merci pour ta contribution.
cdlt patrick
bonsoir
Je vous remercie tous de vous être intéressés à mon problème.
J'ai fait de très nombreuses tentatives, mais je n'ai pas réussi à reproduire l'erreur dans l'utilisation de la fonction DATEDIF(date_début;date_fin;"md").
Une telle erreur est pourtant signalée dans l'aide par Microsoft.
Je vais donc finalement adopter la solution proposée par papyluc utilisant cette fonction car elle est la plus facile à lire et à comprendre pour un novice comme moi. Je le remercie vivement non seulement pour m'avoir donné une solution toute faite mais aussi de m'avoir rendu plus savant par ses précisions sur MOIS.DECALER, j'aurai sûrement l'occasion de l'utiliser.
Microsoft ne sort pas grandi après toutes nos investigations : il recommande de ne pas utiliser la fonction DATEDIF avec le paramètre "md" alors que cette fonction semble fonctionner parfaitement, et nous invite à utiliser en lieu et place une formule qui ne fonctionne pas du tout !
Je remercie également eric et the boss 68 qui ont travaillé sur le problème et proposé deux solutions.
Pour info, passionné d'histoire et de généalogie, je me heurte fréquemment aux limites d'excel : non prise en compte des années antérieures au 1er janvier 1900, impossibilité d'utiliser les différentes fonctions de bases pour le traitement des dates avant 1900, considérer l'année 1900 comme une année bissextile, etc.
Aussi ai-je pour ambition (démesurée vu mes compétences actuelles en Excel) de développer un calendrier perpétuel, lié aux différents calendriers - grégorien, julien, républicain et peut-être d'autres si j'en ai l'usage - et, je l'espère, si je deviens un spécialiste de VBA, créer des fonctions de base relatives aux dates sans limites dans le passé ou le futur (actuellement j'utilise un subterfuge : j'ajoute 2000 ans aux dates antérieures au 1/1/1900 ).
Un travail gigantesque m'attend et je reviendrai peut être faire appel à la solidarité du forum.
Encore merci !
Je clôture le sujet
Patrick
Je vous remercie tous de vous être intéressés à mon problème.
J'ai fait de très nombreuses tentatives, mais je n'ai pas réussi à reproduire l'erreur dans l'utilisation de la fonction DATEDIF(date_début;date_fin;"md").
Une telle erreur est pourtant signalée dans l'aide par Microsoft.
Je vais donc finalement adopter la solution proposée par papyluc utilisant cette fonction car elle est la plus facile à lire et à comprendre pour un novice comme moi. Je le remercie vivement non seulement pour m'avoir donné une solution toute faite mais aussi de m'avoir rendu plus savant par ses précisions sur MOIS.DECALER, j'aurai sûrement l'occasion de l'utiliser.
Microsoft ne sort pas grandi après toutes nos investigations : il recommande de ne pas utiliser la fonction DATEDIF avec le paramètre "md" alors que cette fonction semble fonctionner parfaitement, et nous invite à utiliser en lieu et place une formule qui ne fonctionne pas du tout !
Je remercie également eric et the boss 68 qui ont travaillé sur le problème et proposé deux solutions.
Pour info, passionné d'histoire et de généalogie, je me heurte fréquemment aux limites d'excel : non prise en compte des années antérieures au 1er janvier 1900, impossibilité d'utiliser les différentes fonctions de bases pour le traitement des dates avant 1900, considérer l'année 1900 comme une année bissextile, etc.
Aussi ai-je pour ambition (démesurée vu mes compétences actuelles en Excel) de développer un calendrier perpétuel, lié aux différents calendriers - grégorien, julien, républicain et peut-être d'autres si j'en ai l'usage - et, je l'espère, si je deviens un spécialiste de VBA, créer des fonctions de base relatives aux dates sans limites dans le passé ou le futur (actuellement j'utilise un subterfuge : j'ajoute 2000 ans aux dates antérieures au 1/1/1900 ).
Un travail gigantesque m'attend et je reviendrai peut être faire appel à la solidarité du forum.
Encore merci !
Je clôture le sujet
Patrick