Formule excel échéance 55 jours fin de décade

Résolu
chti_ben -  
Bonjour,
J'essaie de trouver la formule dans Excel qui permet de calculer une date d'échéance correct pour une condition de 55j fin de décade, à savoir:
Si date de facture en cellule A6 = 5 jan 09
Alors date d'échéance = 10 mar 09
car 5 jan 09 + 55 = 1er mar 09, donc fin de décade 10 mar 09
Pour ce cas précis, la formule suivante est correcte:
=si(JOUR(A6+55)<=10,DATE(ANNEE(A6),MOIS(A6)+2,10),SI(JOUR(A6+55)<=20,DATE(ANNEE(A6),MOIS(A6)+2,20),SI(JOUR(A6+55)<=30,DATE(ANNEE(A6),MOIS(A6)+2,30),SI(JOUR(A6+55)=31,DATE(ANNEE(A6),MOIS(A6)+2,10)))))
(bonne digestion)
Mais pour le cas de figure suivant, le calcul est erronné:
Si date de facture en cellule A2 = 1 jan 09
<gras>Alors date d'échéance = 28 fev 09
car 1 jan 09 + 55 = 25 fev 09, donc fin de décade 28 fev 09.
Mais la meme formule suivante donne une échéance au 30 mars 09.
Quelqu'un peut m'aider?
Merci d'avance les amis.

Chti_Ben
Configuration: Windows XP
Internet Explorer 6.0

15 réponses

Résumé de la discussion

Le problème porte sur le calcul de la date d'échéance après 55 jours en fin de décade, lorsque la date de facture entraîne un arrondi vers février ou mars. La solution utilise DAY, MONTH et EOMONTH pour ajuster l'échéance et privilégier la fin du mois de février lorsque nécessaire, afin de respecter 55 jours et la fin de décade. Cette approche calcule l'échéance en fonction du jour obtenu après 55 jours et, si le mois est février, peut renvoyer la fin du mois via FIN.MOIS ou ajuster à la décade correspondante. D'autres propositions évoquent MOD et des variantes en anglais, mais l'approche principale reste l'utilisation de EOMONTH et d'une logique de fin de décade pour les cas complexes.

Bobot (l’IA à votre service)
  1. michel_m Contributeur
    Bonjour
    essaies (XL français...)
    =DATE(ANNEE(A1);MOIS(A1);(JOUR(A1)+55)+9-MOD(JOUR(A1)+55;10))
    1
    1. chti_ben
      Michel,
      Merci beaucoup.
      Je travaille sur un excel en anglais, quelle est la signification de MOD dans la formule (ou l'équivalent en anglais)?
      A bientot.
      Chti_ben
      0
      1. michel_m Contributeur
        en anglais MOD se dit... MOD
        calcule le reste de la division d'un nombre par un autre
        par exemple
        MOD(28;10) renvoie 8
        MOD(30;10 renvoie 0
        ect.
        et n'oublie pas qu'il faut remplacer les points-virgule par des virgules....
        0
        1. irishrom
          Bonsoir Michel,

          C'est une bien belle formule que voilà.
          Malheureusement, je crainds que ça coince à cause du 29 février.

          J'ai testé la version francaise:
          la formule fonctionne du 01 janvier au 28 fev sans problème.
          le 28.02.09 donne une échéance au 30.04.09

          Mais pour le 01.03.09 la formule renvoie l'échéance au 28.04.09 (??)

          Puis pour le 05.03.09 l'échéance devient 08.05.09 (étrange)...

          A moins que je me sois trompé, Chti_Ben, si tu peux confirmer de ton coté??

          Cdt,
          irishrom
          0
      2. michel_m Contributeur
        Bonjour,

        Effectivement!...
        Je regarde mais il est vrai que Février et 2012 mettent la pagaille: pour l'instant je pige pas...
        Irishrom, Merci de m'avoir signalé cette erreur
        0
        2. michel_m Contributeur
          Re,

          février et année bissectile= doliprane...

          à vérifier!!!
          =SI((JOUR(A1)+55>20)*(MOIS(DATE(ANNEE(A1);MOIS(A1);JOUR(A1)+55))=2);FIN.MOIS(A1+55;0);DATE(ANNEE(A1);MOIS(A1);JOUR(A1)+55+(10-MOD(JOUR(A1+55);10))))

          fin.mois ==> eomonth

          edit à 11:50

          formule modifiée!!...
          =SI((JOUR((DATE(ANNEE(A1);MOIS(A1);JOUR(A1)+55)))>=20)*(MOIS(A1+55)=2);FIN.MOIS(A1+55;0);DATE(ANNEE(A1);MOIS(A1);JOUR(A1)+55+(10-MOD(JOUR(A1+55);10))))

          explication: Si le jour "+55" tombe un 20 ou Plus et qu'il est en février, on expédie en fin de mois
          0
          1. chti_ben
            Michel,
            Ca a l'air bon sauf pour quelques cas de figures qui donnent une échéance au 9, à savoir:

            Date Fact Date facture + 55 Date échéance selon formule Date échéance réelle
            03/02/2009 30/03/2009 09/04/2009 10/04/2009
            04/02/2009 31/03/2009 09/04/2009 10/04/2009
            05/04/2009 30/05/2009 09/06/2009 10/06/2009
            06/04/2009 31/05/2009 09/06/2009 10/06/2009
            05/06/2009 30/07/2009 09/08/2009 10/08/2009
            06/06/2009 31/07/2009 09/08/2009 10/08/2009
            06/07/2009 30/08/2009 09/09/2009 10/09/2009
            07/07/2009 31/08/2009 09/09/2009 10/09/2009
            05/09/2009 30/10/2009 09/11/2009 10/11/2009
            06/09/2009 31/10/2009 09/11/2009 10/11/2009
            05/11/2009 30/12/2009 09/01/2010 10/01/2010
            06/11/2009 31/12/2009 09/01/2010 10/01/2010
            06/12/2009 30/01/2010 09/02/2010 10/02/2010
            07/12/2009 31/01/2010 09/02/2010 10/02/2010

            On y est presque car tous les autres jours de l'année sont corrects.

            Un grand merci
            0
            1. michel_m Contributeur
              je regarderai tout ca après la manif: ca me changera les idées!
              à tout à l'heure

              0
              1. michel_m Contributeur
                Re,

                voir cette pièce jointe
                https://www.cjoint.com/?dvsIKwwFPf

                Apparemment, c'est OK avec une colonne intermédiaire "date+55" , on pourrait s'en passer, mais ça compliquerait encore plus une formule qui l'est déjà!
                en fait, tout le bazar venait des mois de février(fin le 28 ou 29) et des mois à 31 jours

                Edit à 19 heures:
                ajouter ce qui est en gras dans la formule!
                =SI((JOUR((DATE(ANNEE(A2);MOIS(A2);JOUR(A2)+55)))>=20)*(MOIS(A2+55)=2);FIN.MOIS(A2+55;0);B2+(10-MOD(JOUR(B2);10))+SI(ET(JOUR(B2)>=30;JOUR(FIN.MOIS(B2;0))=31);1;0))
                en espèrant que cette fois -ci....
                https://www.cjoint.com/?dvtgRxhOmk
                0
                1. Benoît
                  Bonjour Michel,

                  Merci pour toute c'est bonne explication, je n'ai pas la possibilité de récupérer le tableau en pièce jointe car la session est expirée.
                  Serait-il possible de me faire par de la valeur de B2 car j'ai des difficultés à ressortir la bonne valeur à fin janvier.
                  D'avance merci.
                  Benoît
                  0
                2. michel_m Contributeur
                  Bonjour,

                  tu as l'explication de B2 dans le post au dessus, la date étant en A2
                  Apparemment, c'est OK avec une colonne intermédiaire "date+55"
                  donc A2+55

                  je n'ai plus la maquette (vieille de 4 ans...)
                  0
                3. Benoît
                  MERCI Michel pour ce retour
                  bon de nombreuses heures, je n'arrive tout de même pas...
                  Merci quand même...
                  bonne journée.
                  Benoît
                  0
              2. Mike-31
                Salut,

                Si je comprends bien on recherche une date d’échéance à 55 jours mais décalé au 10 du mois

                Si les dates commencent en A2 testes directement cette formule à mettre dans une cellule exemple C2 et incrémenter vers le bas

                =SI(JOUR(A2+55)>10;DATE(ANNEE(A2);MOIS(A2)+3;10);DATE(ANNEE(A2);MOIS(A2)+2;10))

                A+
                0
                1. michel_m Contributeur
                  Je viens d'essayer ta formule, elle est fausse dans la plupart des cas....
                  --
                  0
                  1. irishrom
                    Salut Mike,
                    il semble y qvoir une erreur dans ta 2ieme formule aussi. dsl :)

                    En revanche, félicitation à Michel !!!
                    la formule de 19h fait des miracles.
                    seul souci : le j+55 qui tombe le 10 renvoie au 20.
                    j'aurais aimé une échéance au 10 lui-même.
                    mais la formule est implacable: mathématiquement, le jour du 10 a deja commencé par conséquent la décade se finira le 20.

                    Je pense qu'il est dificile de faire mieux malgré la colonne intermédiaire.

                    Pour info, en glissement annuel, le DSO serait de 60,55j ce qui tombe sous la LME2009.

                    Encore bravo Michel... Et Merci!

                    Il ne reste plus qu'à trouver la facon d'appliquer cette formule Excel dans Oracle
                    (version E-Business)... s'il y a des amateurs :)
                    0
                  2. Mike-31
                    @irishromSalut,

                    Eh ben voilà le principal est que tu es une solution à ton problème

                    Bonne journée à tous
                    0
                  3. michel_m Contributeur
                    @irishromSalut Irishrom, Chti_ben

                    Merci Irish** et Content que ca marche enfin! Pour le 10, 20, finmois et 30 fin de décade je m'étais posé la question mais j'ai biaisé en utilisant le terme "fin de décade".
                    Par contre qu'est ce que c'est que le DSO et la LME2009?
                    Chti_ben, il faudrait marqué le post résolu si ca te convient

                    ** irish ? la St Patrick s'est t elle bien passée ?
                    0
                  5. irishrom
                    @michel_mBonjour Michel,

                    la derniere version de la formule, sans colonne intermédiaire, fonctionne a merveille.
                    Un grand merci pour nous avoir enlevé cette épine du pied :)

                    DSO = number of Days of Sales Outstanding
                    c'est un ratio utilisé en compta clt dans les grosses boites pour mesurer la performance de la collection.

                    la formule: DSO= (total du poste creance client) / (3 mois CA*91j)

                    Je trouve ce ratio tres theorique en lui meme. mais son évolution au file des mois est interessante a analyser.
                    En clair plus le DSO est proche du delai de paiement accordé dans le contrat mieux c'est, cela signifie que le client paie dans les temps.

                    La LME2009 est la nouvelle loi francaise qui réduit, entre autres, a 60j max les delais de paiement.

                    Cdt,
                    Irishrom.

                    PS: La St Patrick s'est apparemment bien passée. J'étais au TAF ce jour la...
                    Mais c'est la premiere année depuis longtemps ou il a fait "si beau" (d'habitude c'est vent, grele et 0 degré).
                    en revanche je ne conseil a personne de se faire arnaquer par les agences de voyage pour un billet d'avion vers Dublin pour assister a cette "fete de...la biere".
                    Ca n'a rien d'extraordinaire comme les media le laisse penser... Mieux vaut assister a la fete du citron a Menton :) au moins on est sur que le soleil soit de la partie.
                    0
                2. Mike-31
                  Re,

                  Effectivement, ma formule calculait une échéance 55 jours fin de mois décalé au 10

                  Pour ma part, j'ai testé cette formule sur une année, à tester de votre coté, à partir de la première ligne et incrémenter vers le bas !

                  =SI(ET(JOUR(A1+55)>=11;JOUR(A1+55)<=24);DATE(ANNEE(A1);MOIS(A1)+3;10);DATE(ANNEE(A1);MOIS(A1)+2;10))

                  Bonne nuit
                  0
                  1. OLOMIDE
                    merci j'essaie
                    0
                  2. OLOMIDE
                    peut etre que j'ai mal disposé les info dans ma colonne par exemple en A1 vous avez mis koi? ainsi de suite
                    ( juste une exemple de la premiere ligne en tableau ( données)

                    merci encore
                    0
                  3. Mike-31
                    @OLOMIDESalut Olomide,

                    Tu aurais du ouvrir ta propre discussion, celle ci est en statut résolu.
                    Cette discussion portait sur le calcul d’échéance 55 jours fin de mois, si la date d’échéance tombait avant le 10 elle était décalée au 10 du mois et si elle tombait après le 10 elle était décalée au 10 du mois suivant.
                    La date de départ dans cet exemple est en A1 la formule dans une cellule vide première ligne et incrémenter vers le bas si nécessaire

                    =SI(ET(JOUR(A1+55)>=11;JOUR(A1+55)<=24);DATE(ANNEE(A1);MOIS(A1)+3;10);DATE(ANNEE(A1);MOIS(A1)+2;10))

                    A+
                    Mike-31

                    Un problème sans solution est un problème mal posé  (Einstein)
                    0
                3. chti_ben
                  Gentlemen,
                  Cela marche avec la formule suivante:
                  =IF((DAY(A2+55)>=20)*(MONTH(A2+55)=2),EOMONTH(A2+55,0),(A2+55)+(10-MOD(DAY(A2+55),10))+IF(AND(DAY(A2+55)>=30,DAY(EOMONTH(A2+55,0))=31),1,0))
                  Un grand merci pour votre support.
                  Chti_ben
                  0
                  1. OLOMIDE
                    Bonjour, Michel

                    je suis interessé par la formule pour les jours decales , mais je ne maitrise pas votre formule, est ce que vous pouvez mieux l'expliquer, car je l'essaye ç ane marche pas.
                    0
                    1. philtax
                      bonjour,

                      pendant que vous parlez d'excel

                      j' ai une question très bête

                      à quoi ça sert exactly

                      merci
                      -1
                      1. philtax
                        décidément j'ai pas de bol aujourd'hui

                        bonne soirée à tous
                        -1

                        Discussions similaires

                        Word : effacer les marques de ¶ ¶ ¶ ¶ ¶ ¶ ¶ ¶ ¶ ¶
                        B@ccm

                        3 réponses
                        Dossier .zip : Fin de l'archive incorrecte
                        mentonman

                        4 réponses
                        word attend la fin d'une action OLE
                        Paris163

                        4 réponses
                        Impossible de désinstaller quoique ce soit
                        Båldrum

                        8 réponses
                        Tampons d'encre en fin de vie
                        koubilai

                        24 réponses