Formule pour facturation quadrimestrielle

Question

Tout d'abord, bonne année 2016 à tous ceux qui liront ce message. Je précise que suis nouveau sur le forum mais je connais depuis longtemps ce site et je profite régulièrement des réponses apportées aux différents problèmes exposés. Aujourd'hui, c'est à mon tour d'en poser un qui me résiste fortement. Voici le contexte : Dans le cadre de la réalisation d'un projet (disons la construction d'un équipement technique), le contrat prévoie une facturation possible en fonction des lots techniques franchis. Le contrat a été signé le 02/07/2015 et c'est important car tout va s'articuler ensuite autour de cette date (T0) Ces lots techniques ont une date de réalisation théorique (en nombre de mois après la date de signature du contrat). Le client a jusqu'à 45 jours pour les approuver. Jusque là, pas de problème avec Excel. Ensuite, le client accepte que les lots approuvés soient facturés une fois tous les 4 mois. Le contrat ayant été signé le 02/07/2015, seul les lots approuvés le 02/11/2015 pourront être facturés. Les dates de facturation suivantes sont le 02/03/2016, puis le 02/07/2016, le 02/11/2016, etc. Le contrat cours jusqu'en 2020. Je cherche donc à calculer automatiquement la date potentielle de facturation de chaque lot en fonction de la date de validation que le chef de projet me donnera (soit sous forme de date, soit en nombre de mois depuis la date de signature du contrat ... mais je n'arrive pas à automatiser cette tâche. Je pense que la clé réside dans l'utilisation de la formule MOIS.DECALER, mais je ne trouve pas l'algorithme entre la date du jalon validé et la date "anniversaire" de facturation potentielle. Je joins à ce message un fichier d'exemple, dont je détaille le contenu ci-après : Colonne : A : ID du jalon B : Nom de l'équipement concerné C : Descriptif du jalon D : Durée (en nombre de mois depuis la signature du contrat) pour franchir ce jalon E à G : Données financières H : vide I : Traduction de la colonne D sous forme de date (cette cellule est calculée, mais est parfois supplantée pour simuler certaines échéances connues. Il faut rester sur la logique du calcul pour les dates futures J : I + 45 jours (délai d'approbation du client) K : prochaine date de facturation quadrimestrielle du jalon concerné C'EST SUR CETTE FORMULE QUE JE BLOQUE. Dans le fichier d'exemple, les valeurs sont saisies à la main pour pouvoir aller jusqu'au bout de la démonstration L : K + 45 jours (délai de paiement au mandataire) M : L + 10 jours (délai de rétrocession du mandataire vers les autres sociétés) N'hésitez pas à commenter si tout ceci n'est pas clair. Merci par avance pour votre participation, je suis sûr que ce cas existe par ailleurs et que sa résolution en aidera un grand nombre ... moi le premier Bonne journée à tous...

CHRIS1945 · Accepted Answer

J'espère avoir bien saisi ta demande à savoir : Calculer le quadrimestre de facturation immédiatement supérieure à la date de validation en colonne J + 4 mois en sachant que ces quadrimestres se calculent à partir de la date de commande en D1 (que j'ai nommé par facilité D_DEPART) augmenté de chaque fois 4 mois. Pour ce faire, j'ai un peu remanié tes formules en : colonnes I : calcul de la date de franchissement qui me semble mieux adapté (je n'ai pas rempli les lignes 4 à 9 qui semblent répondre à une logique différente que je ne comprend pas (mais que je regrette sur le plan informatique : jamais mélanger le manuel et les formules dans une colonne d'un tableau traitant des mêmes données - nous verrons plus loin comment résoudre ce genre de cas) colonne J : simplement ajouter le nombre de jours à la colonne précédente colonne K : comme il semble que vous deviez parfois ne pas respecter la règle générale en imposant une date de facturation quadrimestre, j'ai intercalé une colonne qui est devenu la K où il est possible d'imposer une date manuellement : c'est le cas pour les lignes de 4 à 9 et également pour les lignes 29 ; 35 et 38. colonne L (ancienne colonne K) où, si la colonne K contient une date, on impose cette date sinon on calcule le quadrimestre immédiatement supérieure à la date de la colonne J + 4 mois par la formule : MOIS.DECALER(D_DEPART;(ARRONDI.SUP((J31-D_DEPART)/121.7;0))*4) Explications : on décale à partir de la D_DEPART (cellule D1) du nombre de mois nécessaires pour atteindre le bon quadrimestre en divisant la différence de date de la colonne J avec la D_DEPART par 121.07 (moyenne de jours pour 4 mois) et dont on prend l'arrondi supérieur. Le chiffre obtenu représente le nombre de périodes de 4 mois pour atteindre le bon quadrimestre. Il suffit donc de le multiplier par 4 pour obtenir le nombre de mois à décaler. J'ai laissé en regard des lignes 29 ; 35 et 38 les dates manuelles qui ne correspondaient pas au calcul et je les ai interprétées comme étant les exceptions dont tu parles dans ton exposé de la, la création de la colonne supplémentaire pour imposer une date ne correspondant pas au calcul. J'espère que j'ai bien fait. Comme tu l'as remarqué, la D_DEPART est figée et utilisée dans toutes les formules : il est donc, à mon avis, inutile de la répéter au dessus des colonnes I ; J ; L ; M N Merci de confirmer si cela répond à tes attentes Je te souhaite une bonne année 2016 et ce pour tous les quadrimestres... Chris...