Microsoft 365 Calcule entre plusieurs périodes

[Lemtosh]

Bonjour à tous,

Dans le cadre de mon travail, les équipes opérationnels sont amenés à calculer des périodes (d'assurances). Nous échantillonnons sur les 36 derniers mois d'assurances pour estimer le "risque" d'un client.

Par exemple:
Je vais avoir le début d'une période "A" en A1 et la fin de cette période en B1 (ex: du 01/12/2022 au 01/05/2024 = 17 mois pleins)
Puis une autre période "B" début en C1 et fin en D1 (ex: du 01/02/2024 au 01/06/2025 = 16 mois pleins)
Et enfin une période "C" début en E1 et fin en F1 (ex: du 01/07/2025 au 15/08/2025 = 1 mois pleins)
L'addition des trois périodes donnerait 34 mois pleins.

Toutefois la réalité est que la période du 01/02/2024 au 01/05/2024 concerne une période qui se chevauche et qui ne peut pas compter double.

J'aimerais donc pouvoir avoir une formule qui "comprendrait" qu'il y a chevauchement et qu'il faut donc calculer uniquement de A1 à D1 et rajouter quand même la période C.

Précisions:
- Idéalement si la formule n'est pas "embêter" par l'ordre dans lequel les périodes sont saisies c'est mieux (pas de restriction de les saisir dans un ordre chronologique par exemple)
- il faudrait que le nombre de période (A, B, C, D, etc) puisse entre étendre autant que nécessaire, que la formule puisse s'adapter à plusieurs périodes (7/8 par exemple)
- Si il y a des périodes qui ne se chevauchent pas, elles doivent quand même être calculés comme des périodes.

Merci milles fois pour votre aide!

*quand je dis mois pleins, je sous-entends que les jours/semaines ne compte pas

 

[sylvanu]

Re,
ChatGPT m'a donné la réponse : La formule et la formule en VBA ne sont pas les mêmes. 😡😡😡
Ala question :

Sous Excel, J'ai un souci avec la fonction Datediff. Datediff utilisé en formule, et Datediff utilisé en VBA ne retourne pas la même valeur. Y a t-il une explication ?

La réponse est :

Oui 👍 tout à fait — c’est un point très important à comprendre :
la fonction DATEDIF (en formule Excel) et la fonction DateDiff (en VBA) ne sont pas les mêmes fonctions et n’ont pas les mêmes règles internes de calcul.

---

🔹 1. En formule Excel : =DATEDIF(date_début; date_fin; unité)​

• C’est une fonction héritée de Lotus 1-2-3, cachée dans Excel (non listée dans l’aide).
• Syntaxe :
  

  =DATEDIF(A1; B1; "d")  → nombre de jours entiers
  =DATEDIF(A1; B1; "m")  → nombre de mois entiers complets
  =DATEDIF(A1; B1; "y")  → nombre d’années entières complètes

• Le calcul ignore la partie fractionnaire (il compte uniquement les unités entières entre deux dates).
• Exemple :
  

  A1 = 01/01/2024
  B1 = 31/12/2024

  =DATEDIF(A1;B1;"y") → 0
  =DATEDIF(A1;B1;"m") → 11
  =DATEDIF(A1;B1;"d") → 365

---

🔹 2. En VBA : DateDiff(interval, date1, date2)​

• Fonction du langage VBA, pas d’Excel.
• Syntaxe :
  

  DateDiff("d", #01/01/2024#, #31/12/2024#)  '→ 365
  DateDiff("m", #01/01/2024#, #31/12/2024#)  '→ 11
  DateDiff("yyyy", #01/01/2024#, #31/12/2024#)  '→ 0

• Mais différence clé :
  DateDiff compte le nombre de changements de frontière entre les unités demandées.
  → Il n’arrondit pas et ne vérifie pas la complétude de l’unité comme DATEDIF.
  Par exemple :
  

  DateDiff("m", #31/01/2024#, #01/02/2024#) → 1

  (alors que =DATEDIF(31/01/2024;01/02/2024;"m") → 0)
  ➜ Donc une journée de différence peut faire “1 mois” pour DateDiff, si on passe le mois de janvier à février.

---

🔹 3. Résumé des différences principales​

Aspect	DATEDIF (formule Excel)	DateDiff (VBA)
Origine	Ancienne fonction Excel/Lotus	Fonction VBA native
Disponibilité	Formule Excel seulement	Code VBA seulement
Type de calcul	Nombre complet d’unités	Nombre de frontières franchies
Exemple (31 janv → 1 fév) en “mois”	0	1
Exemple (année bissextile)	Plus précis pour intervalles entiers	Peut décaler selon le mode de calcul
Gestion des fractions	Ignore	Compte les transitions

---

🔹 4. En résumé pratique​

Si tu veux obtenir les mêmes résultats dans VBA que dans Excel :
👉 il faut reproduire le comportement de DATEDIF manuellement, par exemple :

Function DATEDIF_Excel(StartDate As Date, EndDate As Date, Interval As String) As Long
    Select Case LCase(Interval)
        Case "d"
            DATEDIF_Excel = Int(EndDate - StartDate)
        Case "m"
            DATEDIF_Excel = DateDiff("m", StartDate, EndDate)
            If Day(EndDate) < Day(StartDate) Then DATEDIF_Excel = DATEDIF_Excel - 1
        Case "y"
            DATEDIF_Excel = DateDiff("yyyy", StartDate, EndDate)
            If Month(EndDate) < Month(StartDate) Or _
               (Month(EndDate) = Month(StartDate) And Day(EndDate) < Day(StartDate)) Then
                DATEDIF_Excel = DATEDIF_Excel - 1
            End If
    End Select
End Function

Donc je vais regardé ça de plus près et rectifié quand j'aurais trouvé la solution.

 

[sylvanu]

Re,
En PJ une V4.
ChatGPT m'a donné une fonction VBA qui reproduit le comportement d'un Datediff Excel.