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

 

[sousou]

bonjour
regarde cette fonction
=ftest(celluel de départ; cellule defin)

Function ftest(deb, fin)
ftest = DateDiff("m", deb, fin)
End Function

 

[sylvanu]

Bonjour Lemtosh, et bienvenu sur XLD,
Essayez avec :

=ARRONDI(SI(MIN(C1:D1)<MAX(A1:B1);(MAX(C1:D1)-MIN(A1:B1))/31;(MAX(A1:B1)-MIN(A1:B1)+MAX(C1:D1)-MIN(C1:D1))/31);0)

Les Min Max permettent l'inversion des dates.
Cependant il ne peut y avoir qu'inversion d'une période et non des deux.
( par ex Date2 Date1 et/ou Date 4 Date3, mais pas Date4 Date1 Date3 Date2)

AddOn : Bonjour Sousou,
Si les deux périodes sont disjointes alors votre macro ne marche pas car elle tiendra aussi compte de l'intervalle entre les périodes.

 

[Lemtosh]

@sylvanu, vous êtes incroyable toutefois, j'ai rajouter une information après avoir posté la demande (et pas des moindre désolé) que s'il y a des périodes qui ne se chevauchent pas, elles doivent quand même être calculés comme des périodes.

Est-ce possible d'étendre la formule à 7 ou 8 périodes?

 

[Hecatonchire]

Bonjour à toutes et tous,

Il faudrait déjà savoir ce qu'est un mois pour toi : 30, 30,5, 31 jours ou lié à la durée réelle donc en février 28/29 jours.

Tu écrits " (ex: du 01/12/2022 au 01/05/2024 = 17 mois pleins", OK mais pour moi du 01/12/2022 au 30/04/2024 c'est aussi 17 mois (du 1/12/22 ou 31/12/22 c'est un mois et au 1/1/23 c'est 1 mois et 1 jour).

Je te propose

=LET(d_1;A1;f_1;B1;d_2;C1;f_2;D1;
SI(f_1>=d_2;DATEDIF(d_1;f_2+1;"m");DATEDIF(d_1;f_1+1;"m")+DATEDIF(d_2;f_2+1;"m")))

 

[sylvanu]

que s'il y a des périodes qui ne se chevauchent pas, elles doivent quand même être calculés comme des périodes.

Qu'entendez vous par là ?
Qu'il faut calculer les deux périodes séparément et les additionner ?
Dans ce cas ma formule marche :

Est-ce possible d'étendre la formule à 7 ou 8 périodes?

Là, par formules ça devient une usine à gaz. Acceptez vous le VBA ?

 

[Lemtosh]

@Hecatonchire Merci ! Je vais étudier une solution à la fois, milles merci !

@sylvanu, mon tableau se constitue comme ça à ce jour (ce qui n'est pas la bonne solution c'est une certitude) cf fichier joint

Idéalement il faudrait que la formule comprenne que la période "A" chevauche à la période "B" par exemple mais que si la période "C" il y a du 01/07/2025 au 15/08/2025 c'est un mois supplémentaire à rajouter.

Oui pour la question du VBA

 

[sylvanu]

Re,
Est ce que dans votre fichier les dates seront croissantes ?
Par ex Date C pourrait chevaucher Date A ?
Ou est ce que le chevauchement ne peut être que sur deux plages consécutives.

 

[Lemtosh]

Oui, elle pourrait l'être. C'est un fichier qui sera à destination des équipes pour calculer facilement les antécédents (d'assurances). Si ils n'ont pas à réfléchir si une date est au-dessus/en dessous d'une autre et saisir librement ça serait l'idéal effectivement

 

[sylvanu]

Re,
Un essai en VBA. Il suffit de changer une date en C6:N6 pour que le recalcul se fasse automatiquement.
A tester.
NB:
Pris en compte :
-Position Début Fin inversé
-Plage consécutives ou avec recouvrement
-Position des plages dans le temps aléatoires
N'est pas pris en compte :
-Le cas d'une plage de date qui serait totalement à l'intérieur d'une autre plage.
( par ex Plage1 1/1/2025 au 31/12/2025 et Plage2 : 1/5/2025 à 1/8/2025 )

 

[sylvanu]

Re,
En PJ "Le cas d'une plage de date qui serait totalement à l'intérieur d'une autre plage." est résolu.
Il suffit qu'après classement chronologique on regard si Début2>Début1 ET Fin2<Fin1.

 

[Lemtosh]

Re,
Un essai en VBA. Il suffit de changer une date en C6:N6 pour que le recalcul se fasse automatiquement.
A tester.
NB:
Pris en compte :
-Position Début Fin inversé
-Plage consécutives ou avec recouvrement
-Position des plages dans le temps aléatoires
N'est pas pris en compte :
-Le cas d'une plage de date qui serait totalement à l'intérieur d'une autre plage.
( par ex Plage1 1/1/2025 au 31/12/2025 et Plage2 : 1/5/2025 à 1/8/2025 )

Ce que tu as fais est incroyable! Je t'offrirais un café volontiers 😂 J'ai regardé le code toutefois sans surprise c'est bien au delà de mes compétences...

Je rencontre toutefois deux problèmes :
- si aucune date n'est saisie, l'idéale serait d'indiquer 0 mois

si je regarde la période d'assurance d'un client (par exemple du 18/05/2022 au 18/05/2025) et qu'un client se présente avec 3 périodes :
- 15/12/2022 au 22/05/2023
- 01/05/2023 au 28/08/2023
- 04/08/2023 au 01/01/2024

Ces périodes sont au sein de la période que je regarde ok, mais finalement ces périodes "s'imbriquent" les unes dans les autres pour au final donner 8 mois (du 15/12/2022 au 01/01/2024 sans interruption), pourtant le tableau me trouve 13 mois.

Aurais-tu une solution?

 

[sylvanu]

Re,
Avec ces trois plages avec recouvrement, on obtient une seule plage continue, du 15/12/2022 au 01/01/2024.
Ce qui ne fait pas 8 mois mais 13 mois.
( Décembre 2022 + 12 mois de 2023 )
Est ce exact ou me trompais je ?

 

[Lemtosh]

oups pardon, ça fait 12* mois pleins

 

[sylvanu]

Re,
J'ai un gros souci.... ou plutôt vous. 🙂
Je prends 2 dates 15/12/2022 et 01/01/2024.
Par formule =DATEDIF(C21;C22;"m") je trouve 12;
Par VBA je trouve 13 avec cette fonction :

Function DatediffVBA(Début, Fin)
    DatediffVBA = DateDiff("m", Début, Fin)
End Function

( voir PJ pour le test. )

En d'autres termes Datediff en formule et Datediff en VBA ne donne pas la même valeur.

Pour l'instant je n'ai aucune explication. A suivre ....