Microsoft 365 Lisser budget sur le nombre de mois en fonction des dates début et fin

[aba2s]

Bonjour la communauté,

J'ai depuis 3 jours des difficultés à résoudre ce problème. J'ai un tableau excel contenant plusieurs colonnes mais ici seules 3 nous intéresse :

- Date de début (Start Date) // - Date de fin (End Date) // - et Total Budget.
Je souhaite si entre (la différence) Start Date et Date il y a n mois, lisser le budget sur ces n mois (de la colonne AC à la colonne AN).

Exemple : Start Date = 24/04/2020 // End Date = 31/10/2020 // Total Budget = 245.00 €

ici on devrait avoir : 245.00 €/7 sur toutes les cases : de April à october et sur les autres 0 ou rien.

J'ai crée une fonction macro custom mais le résultat n'est pas satisfaisant. Pourriez vous svp m'aider avec cette fonction?

Je vous remercie d'avance.

Public Function MonthlyBudgetForecast(Cellule As Range) As Double


    Dim i As Byte
    Dim FeDataPipe As Worksheet: Set eDataPipe = ThisWorkbook.Worksheets("Data - Pipe")
    Dim departure As Range
    Dim departure_index As Integer
    Dim compteur As Integer
    

    departure = FeDataPipe.Cells.Find(what:="January")
    departure_index = departure.Column

    nb_month = Month(Cellule.Offset(, 2)) - Month(Cellule.Offset(, 1)) + 1
    compteur = 0
    
    For i = 1 To nb_month
        FeDataPipe.Cells(Cellule.Row, departure_index + compteur) = Cellule / 10
        MonthlyBudgetForecast = FeDataPipe.Cells(Cellule.Row, departure_index + compteur)
        compteur = compteur + 1
        MsgBox i
    Next
    
    MonthlyBudgetForecast = compteur
    Set FeDataPipe = Nothing
    
End Function

 

[sylvanu]

Bonjour Aba2s,
Un essai en PJ avec cette macro :

Function EssaiLissage(L As Integer, C As Integer)
' Syntax : =EssaiLissage(LIGNE();COLONNE())
Dim Month1 As Integer, Month2 As Integer
Application.Volatile
Month1 = Month(Cells(L, 23))                                        ' Start month extract
Month2 = Month(Cells(L, 24))                                        ' End month extract
If (C - 28) >= Month1 And (C - 28) <= Month2 Then                   ' Month segment definition
    EssaiLissage = Round(Cells(L, 22) / (Month2 - Month1 + 1), 2)   ' Average value distribution
Else
    EssaiLissage = ""
End If
If EssaiLissage = 0 Then EssaiLissage = ""
End Function

 

[aba2s]

Bonjour Sylvanu,

Merci beaucoup pour ton aide très précieuse. ça répond exactement à mon besoin.
Encore une fois merci d'avoir pris ton temps à m'aider.

Sur "Excel download" il n'y a que des passionnés.

 

[aba2s]

Bonjour @sylvanu ,

J'ai voulu adapté ta macro afin de lisser le budget sur le nombre de jours du mois sur le nombre total de jours. Mais je n'ai pas réussi.

Exemple : Start Date = 24/04/2020
End Date = 31/10/2020
Total Budget = 245.00 €

On devrait avoir sur :

- avril : 245 * 7/ (End Date - Start Date +1)
- mai : 245*31/ (End Date - Start Date +1)
- Juin : 245*30/(End Date - Start Date +1)
- Juillet : 245*31/(End Date - Start Date +1)
- Août : 245*31/(End Date - Start Date +1)
- Sept : 245*30/(End Date - Start Date +1)
- Oct : 245*31/(End Date - Start Date +1)

Merci beaucoup pour ton aide

 

[sylvanu]

Bonjour Aba2s,
Au post #1 vous avez dit :

Exemple : Start Date = 24/04/2020 // End Date = 31/10/2020 // Total Budget = 245.00 €
ici on devrait avoir : 245.00 €/7 sur toutes les cases : de April à october et sur les autres 0 ou rien.

Donc je lisse par rapport au nombre de mois ( soit 245/7=35) comme demandé, mais pas au prorata des jours pour le premier et le dernier mois.

Ce serait d'ailleurs beaucoup plus complexe avec les 30 et 31 jours, plus tenir compte de l'année bissextile pour les 28 ou 29 février.

 

[aba2s]

En effet, il fallait lisser par nombre de mois initialement.
Il y a eu un changement et c'est plus complexe.
Merci quand même pour aide.
Bonne journée