Microsoft 365 Calcule entre plusieurs périodes

Lemtosh · Mardi à 14:52

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 · Hier à 11:39

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 :

Code:

=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 :

Code:

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

Code:

=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 :

Code:

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 :
Code:
```
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 :

Code:

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 · Hier à 12:55

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

Lemtosh · Hier à 13:53

Je suis impressionné 😶

Après une solution, intervient un nouveau "soucis". Je vais prendre l'exemple suivant, un client donne ces trois périodes :
- du 01/12/2022 au 15/05/2023
- du 01/05/2023 au 18/08/2023 (donc s'imbrique dans la période précédente, parfait !)
- du 15/11/2022 au 01/04/2023

J'obtiens un total de 8 mois, toutefois la dernière période "étend" la première période pour donner normalement un total 9 mois pleins (du 15/11/2022 au 18/08/2023) 😵‍💫

sylvanu · Hier à 15:25

Re,
En regardant, je me suis aperçu qu'il y a tellement de combinaisons possibles que je trouve aucune solution globale.
Entre les plages distinctes, se chevauchant, incluses et le tout dans le désordre à chaque fois un nouveau problème survient.
Désolé mais j'arrive à mes limites. 😭

Lemtosh · Hier à 15:40

Milles merci en tout cas pour ton temps et ton investissement ! Je vais continuer à fouiller les possibilités. Chat GPT suggère le fait de faire une frise chronologique directement dans excel via des cellules et compter les mois pleins... à voir si je tiens qqchose

sylvanu · Hier à 17:03

Re,
Après réflexion, je pense que l'erreur est de vouloir coller des durées pour chaque couple de dates.
Il vaudrait mieux construire les périodes continues, puis calculer les mois sur ces périodes.
Je vais regarder comment faire.

sylvanu · Aujourd'hui à 09:58

Re,
Une V7 en PJ.
En ligne 6 on met les dates.
En ligne 10 sont calculées les différentes périodes continues. Ce qui semble plus logique.
En espérant que ça résiste aux différents tests.

Lemtosh · Aujourd'hui à 12:27

Pour les quelques premiers tests j'ai l'impression que ça fonctionne parfaitement. C'est incroyable 🤯

Toutefois dans mes tests j'ai ultime requête si tu es toujours intéressé pour le solutionner, serait-il possible que deux périodes qui se collent soit considéré comme la même période? Je m'explique :
une période allant du 01/05/2023 au 24/08/2023
une période allant du 25/08/2023 au 15/12/2023

En réalité du 24/08/2023 au 25/08/2023 on ne considère pas ça comme une interruption mais comme une continuité.

J'ai regardé le code toujours pas curiosité mais en dehors de mes compétences pour l'instant... J'imagine que la solution doit se trouver dans cette portion de code :

' Calcul des périodes consécuties ( mis dans Tout )
Tout(1, 1) = T(1, 1): Tout(1, 2) = T(1, 2) ' initialisation 1ere ligne
j = 2
For i = 2 To UBound(T)
' Cas de période séparée
If T(i, 1) > T(i - 1, 2) Then
Tout(j, 1) = T(i, 1): Tout(j, 2) = T(i, 2): j = j + 1
End If

sylvanu · Aujourd'hui à 15:19

Re,
Voir V8.
A noter qu'avec Datediff j'ai vu que la durée entre le 01/03/2025 et le 31/03/2025 donne 0, car il considère le mois comme non complet.
La date de fin étant exclue. Mais ça c'est le fonctionnement de Datediff.
Comment compter vous un mois complet ?
Du 01/03/2025 eu 31/03/2025 ou du 01/03/2025 au 01/04/2025 ?
Si c'est la seconde proposition alors Datedif ne marche pas, et je ne sais pas le résoudre.

Lemtosh · Aujourd'hui à 17:31

C'est parfait ! C'est exactement ça ! D'après la calculatrice Windows (qui est notre référence) du 01/03/2025 au 31/03/2025 = 4 semaines et 2 jours mais pas un mois. Donc ce que tu as fais est parfait 👌
Je ne te remercierais jamais assez 😅

Merci à votre belle communauté 😍

Microsoft 365 Calcule entre plusieurs périodes

Lemtosh

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

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

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

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

🔹 4. En résumé pratique

sylvanu

XLDnaute Barbatruc

Pièces jointes

Lemtosh

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Lemtosh

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

sylvanu

XLDnaute Barbatruc

Pièces jointes

Lemtosh

XLDnaute Nouveau

sylvanu

XLDnaute Barbatruc

Pièces jointes

Lemtosh

XLDnaute Nouveau

Discussions similaires

Microsoft 365 Calcule entre plusieurs périodes

XLDnaute Nouveau

XLDnaute Barbatruc

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

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

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

🔹 4. En résumé pratique​

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Nouveau

Discussions similaires

Privacy & Transparency

Privacy & Transparency

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

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

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

🔹 4. En résumé pratique