Microsoft 365 répartir montant entre 2 dates

Sigma13

XLDnaute Nouveau
Bonsoir à tous!

J'aurais besoin de vos lumières!

Je dois répartir un montant en fonction d'une date de départ et de fin (répartition d'un revenu mensuellement).
J'ai trouvé un fichier qui correspondait à peu près à mon besoin mais le calcul du montant était fait par jour et donc était différent chaque mois. J'ai donc modifié les formules et j'ai réussi à avoir des montants constants par mois.:)
Par contre, cela se corse si les date de début et de fin ne correspondent pas à un mois entier. J'ai tenté de modifier les formules mais cela ne fonctionne pas, le total des mois ne correspond pas à la somme total du contrat.
Est-ce que quelqu'un pourrait me dire ce que je fais de faux? cela fait 3 nuits que je parcours les forums et je perd espoir :-(


Merci d'avance pour votre aide!

(Dans le fichier joint, le fichier de base se trouve dans l'onglet "base" et ma version se trouve dans "ADJUST")
 

Pièces jointes

  • Book3.xlsx
    127 KB · Affichages: 37

David Aubert

XLDnaute Barbatruc
Administrateur
Modérateur
Bonjour,
Une tentative rapide (pas tant que ça en fait ;) ) en partant du postulat que les contrats sont toujours sur 12 mois et que si le contrat ne démarre pas le 1er du mois on répartit le mois de début et mois de fin au prorata du nb de jours ouvrés.
Et ajout des colonnes I, J & K pour faire des calculs intermédiaires pour simplifier les formules.
Dis mois si ça te va.
Bonne journée
David
 

Pièces jointes

  • Book3test_DA.xlsx
    186.4 KB · Affichages: 21
Dernière édition:

David Aubert

XLDnaute Barbatruc
Administrateur
Modérateur
Après relecture et petit déjeuner ;-)...quelques questions :
Il faut tout de même que tu précises tes attentes sur le 1er mois et le dernier lorsque le contrat ne démarre pas le 1er du mois.
Est ce que dans ce cas tu souhaites un montant égal pour les 13 mois?
Ou bien un montant égal sur les 11 mois avec ajustement sur le 1er et le 13ème?
A te lire
Bonne journée
 

Amilo

XLDnaute Accro
Bonjour Sigma13, David Aubert, le forum,

@Sigma13, votre fichier semble ne pas correspondre à votre remarque ci-dessous
J'ai trouvé un fichier qui correspondait à peu près à mon besoin mais le calcul du montant était fait par jour et donc était différent chaque mois. J'ai donc modifié les formules et j'ai réussi à avoir des montants constants par mois

Je ne vois pas de montants constants tous les mois dans votre fichier mais au contraire justement des montants variables en fonction du nombre de jours d'un mois à l'autre !!!

Si vous avez réussi comme évoqué, à avoir des montants constants, il faudrait alors mettre cet exemple et indiquer clairement la ou les valeurs qui posent problème.

Cordialement
 
Dernière édition:

Sigma13

XLDnaute Nouveau
merci infiniment pour le fichier! Heureusement que vous êtes là! c'est top.
Par rapport aux questions soulevées:
- les contrats n'ont pas de durée fixe, cela peut aller d'un mois à 3 ans.
- si un contrat commence en cours de mois on répartit sur 13 mois (11 mois plein et le 1er et 13e au prorata)
- un dernier point: si la facture date d'après le début du contrat, il faut que le rattrapage des mois se fasse sur le 1er mois facturé. Ex: facture datée du 01.04 mais contrat commence le 01.02: on doit avoir un total de 3 mois sur avril
Merci encore pour votre aide!
Silvia
 

Sigma13

XLDnaute Nouveau
Bonjour Sigma13, David Aubert, le forum,

@Sigma13, votre fichier semble ne pas correspondre à votre remarque ci-dessous


Je ne vois pas de montants constants tous les mois dans votre fichier mais au contraire justement des montants variables en fonction du nombre de jours d'un mois à l'autre !!!

Si vous avez réussi comme évoqué, à avoir des montants constants, il faudrait alors mettre cet exemple et indiquer clairement la ou les valeurs qui posent problème.

Cordialement
Bonjour,
alors dans l'onglet de base, les montants sont en fonction des jours. J'ai donc essayé d'ajuster dans l'onglet "adjust", cela fonctionne pour la première ligne mais pas pour les autres :rolleyes:

Merci pour votre aide!
Silvia
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour à tous :),

Un méthode par une fonction personnalisée en VBA.

La fonction s'écrit :
VB:
= RmbtMensuel(ThisMonth ; SubStartDate ; SubEndDate ; TotalSub )
avec :
  • ThisMonth est la date du mois de la mensualité
  • SubStartDate est la date de début du contrat
  • SubEndDate est la date de fin du contrat
  • TotalSub est le montant global du contrat
Voir le fichier joint. La formule de la cellule J17 est à recopier vers le bas et vers la droite.


nota : le dernier point du message #5 a été ignoré puisque pas mentionné dans la question initiale.


Le code de la fonction est dans module1:
VB:
Function RmbtMensuel(ThisMonth As Date, SubStartDate As Date, SubEndDate As Date, TotalSub As Currency) As Currency
Dim deb, nbrMoisComplet&, i, nbrJour1&, nbrJour2&, parJour@, parMois@, Avant@, apres@

   deb = DateSerial(Year(SubStartDate), Month(SubStartDate), 1)
   nbrMoisComplet = 1
   For i = 1 To 9999
      deb = DateSerial(Year(deb), Month(deb) + 1, 1)
      If Format(deb, "yymm") > Format(SubEndDate, "yymm") Then Exit For
      nbrMoisComplet = nbrMoisComplet + 1
   Next i

   If Day(SubStartDate) <> 1 Then
      nbrJour1 = Application.WorksheetFunction.EoMonth(SubStartDate, 0) - SubStartDate + 1
      nbrMoisComplet = nbrMoisComplet - 1
   End If

   If Day(SubEndDate) <> Day(Application.WorksheetFunction.EoMonth(SubEndDate, 0)) Then
      nbrJour2 = Day(SubEndDate)
      nbrMoisComplet = nbrMoisComplet - 1
   End If

   parJour = TotalSub / (nbrJour1 + nbrJour2 + 30 * nbrMoisComplet)
   parMois = Round(30 * parJour, 2)
   Avant = Round(nbrJour1 * parJour, 2)
   apres = TotalSub - nbrMoisComplet * parMois - Avant

   If Format(ThisMonth, "yymm") = Format(SubStartDate, "yymm") Then
      RmbtMensuel = IIf(nbrJour1 = 0, parMois, Avant)
   ElseIf Format(ThisMonth, "yymm") = Format(SubEndDate, "yymm") Then
      RmbtMensuel = IIf(nbrJour2 = 0, parMois, apres)
   Else
      If (Format(ThisMonth, "yymm") >= Format(SubStartDate, "yymm")) And (Format(ThisMonth, "yymm") <= Format(SubEndDate, "yymm")) Then RmbtMensuel = parMois
   End If
End Function
 

Pièces jointes

  • Sigma13- Rmbt mensuel- v1.xlsm
    148.1 KB · Affichages: 15
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
Re,

Une version v2 avec la condition supplémentaire :
si la facture date d'après le début du contrat, il faut que le rattrapage des mois se fasse sur le 1er mois facturé. Ex: facture datée du 01.04 mais contrat commence le 01.02: on doit avoir un total de 3 mois sur avril

On a ajouté un cinquième paramètre à la fonction qui est la date de la première facture.
 

Pièces jointes

  • Sigma13- Rmbt mensuel- v2.xlsm
    151.2 KB · Affichages: 7

mapomme

XLDnaute Barbatruc
Supporter XLD
Re,

La v2 modifiée pour que la somme des mensualités soit exactement le montant total du contrat.
Pour cela, on a modifié la formule Excel en :
de
VB:
=RmbtMensuel(J$16;$G17;$H17;$D17;$F17)
en
VB:
=SI(FIN.MOIS(J$16;-1)+1 =FIN.MOIS($F17;-1)+1;$D17-SOMME(K17:$EK17);   RmbtMensuel(J$16;$G17;$H17;$D17;$F17))
 

Pièces jointes

  • Sigma13- Rmbt mensuel- v2a.xlsm
    66.7 KB · Affichages: 12

mapomme

XLDnaute Barbatruc
Supporter XLD
@Sigma13 ;), @David Aubert ;), @Amilo ;)

La nuit portant conseil, une pensée m'a traversé l'esprit et voilà au petit matin une toute petite (dernière?) modification de la v2a.

La v2a modifiait le premier remboursement pour que la somme totale des remboursements soient égale, à la virgule prêt, au montant du contrat. Mais, j'avais oublié un petit point :( .

En VBA, je travaille en type "currency". Les valeurs ont donc 4 chiffres après la virgule. Ces valeurs sont retranscrites sur Excel avec aussi 4 décimales. Il serait donc mieux que les valeurs du tableau Excel soient aussi de vraies valeurs à deux chiffres après la virgule.

Pour cela, on modifie la formule en introduisant un arrondi au résultat de la précédente formule :
Code:
=ARRONDI(SI(FIN.MOIS(J$16;-1)+1=FIN.MOIS($F17;-1)+1;$D17-SOMME(K17:$EK17);RmbtMensuel(J$16;$G17;$H17;$D17;$F17));2)

Ça ne change pas grand chose au résultat mais pour l'esprit, du moins le mien, c'est beaucoup plus satisfaisant (toujours un peu laborieux, ces histoires de chiffres après la virgule lors d'opération mathématiques).

Voir la v2b
 

Pièces jointes

  • Sigma13- Rmbt mensuel- v2b.xlsm
    66.5 KB · Affichages: 9
Dernière édition:

Amilo

XLDnaute Accro
Bonjour @Sigma13, @David Aubert, @mapomme, le forum,

alors dans l'onglet de base, les montants sont en fonction des jours. J'ai donc essayé d'ajuster dans l'onglet "adjust", cela fonctionne pour la première ligne mais pas pour les autres

@Sigma13, merci pour votre retour, désolé pour mon précédent message, il y avait effectivement 2 onglets dans votre fichier dont celui "Adjust" correspondant à votre problématique.

Sinon, pour ma part, je n'ai pas réussi à trouver une solution par formule :confused: et pas encore essayé avec Power Query mais je pense que cela ne vaudra pas l'excellente solution à mapomme ;)

Comme je m'étais déjà penché hier, je poste malgré tout, ma version avec formule (voir onglet "Base").
La formule ne répond pas à la demande car il s'agit d'une répartition par jours mais peut-être que cela inspirera certains ou servira pour d'autres cas.

VB:
=$D17/($H17-$G17+1)*((MAX(J$16-$G17;0)-MAX(FIN.MOIS(J$16;-1)-$G17;0))-(MAX(J$16-$H17;0)-MAX(FIN.MOIS(J$16;-1)-$H17;0))+(FIN.MOIS(J$16;0)=FIN.MOIS($G17;0)))

Cordialement
 

Pièces jointes

  • Book3.xlsx
    123.3 KB · Affichages: 8
Dernière édition:

mapomme

XLDnaute Barbatruc
Supporter XLD
Re @Sigma13 , @David Aubert , @Amilo

Me reste plus qu'à essayer de comprendre les codes, histoire de m'améliorer un peu!

Je vous ai fait un fichier avec le code commenté.
Je me suis plus attaché à décrire le "comment" que d'expliquer à fond chaque instruction.

nota: excusez-moi pour les fautes d'orthographe et de grammaire, je saturais à la fin... Il doit en rester quelques unes ! o_O
 

Pièces jointes

  • Sigma13- Commentaires- Rmbt mensuel- v2a.xlsm
    71.6 KB · Affichages: 23

Sigma13

XLDnaute Nouveau
Encore merci à tous pour ce boulot monstreux!
Par contre j'ai intégré mes données et j'ai un soucis avec la réconciliation.
En octobre 2015 et novembre 2018, il y a une différence mais je ne vois pas d'où ça vient.
En ocobre 2015, il y a un écart d'environ 250 si je totalise les montants (y12) puis en novembre 2018 (bj12), l'écart diminue à 54.

Est-ce que vous arrivez à voir ce qui ne joue pas?

Merci encore!
Le fichier étant trop volumineux voici le lien:

Lien supprimé
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonjour @Sigma13 :),

Par contre j'ai intégré mes données et j'ai un soucis avec la réconciliation.
En octobre 2015 et novembre 2018, il y a une différence mais je ne vois pas d'où ça vient.
En ocobre 2015, il y a un écart d'environ 250 si je totalise les montants (y12) puis en novembre 2018 (bj12), l'écart diminue à 54.

Je vois plusieurs points qui peuvent pécher :

Premier point :
Pour coder ma fonction, j'ai fait certaines hypothèses pour les dates "Invoice date", "Sub Start Date", "Sub End Date", à savoir :
  • Invoice date doit être supérieure ou égale à la date du début du contrat Sub Start Date
  • Invoice date doit être inférieure ou égale à la date de fin de fin du contrat Sub End Date
  • Invoice date doit être supérieure ou égale à la première date du tableau (2015)
  • Invoice date doit être inférieure ou égale à la dernière date du tableau (2025)
  • Le début du contrat appartient au 21e siècle
  • La fin du contrat appartient au 21e siècle
  • L'année de début d'un contrat est supérieure ou égale à 2020
  • L'année de fin d'un contrat est inférieure ou égale à 2025
  • le début du contrat est inférieur à la fin de contrat
Les colonnes O à W comportent un "X" quand une de ces conditions n'est pas vérifiées.

Si une ou plusieurs de ces conditions ne sont pas respectées, je ne réponds pas de ce que la fonction peut renvoyer.


Second point :

Dans vos formules en lignes 12 à 14, vous utilisez des expressions du type :
SOMME.SI($L$17:$L$1272;AB$16;$F$17:$F$1272)

Cette somme va renvoyer dans 99,99% des cas la valeurs 0 et dans 100% des cas une valeur inexacte.

Vous comparez la colonne L ( Invoice date ) à la valeur de la ligne 16, or :
  • Les valeurs de la colonne sont des dates quelconques avec des jours variant de 1 à 31
  • Les valeurs de la ligne 16 sont les dates des premiers du mois (01/01/20015, 01/02/2015, etc)

La comparaison retournera FAUX dans la majorité des cas sauf si la date Invoice date est le premier jour du mois. C'est comme si cous compariez "des choux et des carottes"

Il faux donc convertir les dates de Invoice date en leur premier du mois avant d'utiliser le Somme.si. Pour faire cela, utilisez la colonne X au lieu de la colonne L.


Lien supprimé
 

Discussions similaires

Réponses
11
Affichages
473

Statistiques des forums

Discussions
311 725
Messages
2 081 941
Membres
101 847
dernier inscrit
Djigbenou