Calcul engagements de delais/heures&jours ouvres

mariet95

XLDnaute Junior
Bonjour à tous,
J'ai un problème complexe que je n'arrive pas à résoudre même en l'ayant soumis à quelques collègues relativement experts en Excel.
Je souhaiterai pouvoir calculer en automatique de délais d'engagement contractuelle avec date et heure, en tenant compte des paramètres suivants:
- Criticité : mineur ou grave
-Si Mineur = intervention à réaliser sous 16h ouvrées calculé à partir d'une date et heure de référence
-Si Grave = intervention à réaliser sous 8h ouvrées calculé à partir d'une date et heure de référence
-Heures ouvrées = entre 8:00 et 18:00
-Jours ouvrés = du Lundi au vendredi, hors jours fériés,

J'ai joint un fichier avec sur un onglet pas mal d'exemples où je donne le détail du calcul et le résultat à trouver pour chaque. J'ai aussi inséré dans un onglet un calendrier perpétuel avec les jours fériés.
Si l'un ou l'une d'entre vous peut m'aider un grand merci par avance.
Cordialement,
Mariet95
 

Pièces jointes

  • ESSAI CALCUL SLA_V1.zip
    23.9 KB · Affichages: 91
Dernière édition:

ROGER2327

XLDnaute Barbatruc
Re : Calcul engagements de delais/heures&jours ouvres

Bonjour mariet95
En supposant que vos deux derniers exemples sont faux ou que votre tableau de jours fériés est incomplet, je propose
Code:
[COLOR="DarkSlateGray"][B]=SERIE.JOUR.OUVRE(C2;D2+(1+(E2="Mineur"))/3+((D2+(E2="Mineur")/3>5/12)+((D2+(E2="Mineur")/3+(D2+(E2="Mineur")/3>5/12)*7/12)>17/12))*7/12;Calendrier!$Y$4:$Y$29)[/B][/COLOR]
pour la date en F2.

Code:
[B][COLOR="DarkSlateGray"]=MOD(D2+(1+(E2="Mineur"))/3+(D2+(E2="Mineur")/3>5/12)*7/12+((D2+(E2="Mineur")/3+(D2+(E2="Mineur")/3>5/12)*7/12)>17/12)*7/12;1)[/COLOR][/B]
pour l'heure en G2.

S'il n'y a ni erreur dans les exemples, ni erreur dans le tableau de jours fériés, je n'ai pas d'idée.​
ROGER2327
#2385
 

mariet95

XLDnaute Junior
Re : Calcul engagements de delais/heures&jours ouvres

Bonjour,
Désolée, je viens de m'apercevoir effectivement que les derniers exemples sont faux.
J'aurai du mettre 31 Décembre. Je voulais avoir comme pour Noël, le jour férié + le WE qui sautaient dans le calcul et le passage à une autre année.
Je vais l'insérer dans mes fichiers. Vous serait-il possible de m'expliquer les formules. Je souhaiterai dans d'autres contextes pouvoir l'adpater à d'autres critères.
Exemple: plus de critères de criticité; délais sur 4H ouvrés, autres plages horaires comme des astreintes.
Un grand merci pour tout. Bien cordialement.
 

ROGER2327

XLDnaute Barbatruc
Re : Calcul engagements de delais/heures&jours ouvres

Re...
Les formules ont été optimisées pour répondre précisément au problème que vous posiez plus haut. (J'ignorais qu'en fait c'était un autre problème qui vous préoccupait.) La formule pour trouver l'heure est une simplification de la formule suivante :
Code:
[COLOR="DarkSlateGray"][B]=MOD(D2+(8*(E2="grave")+16*(E2="Mineur"))/24+(D2+(8*(E2="grave")+16*(E2="Mineur"))/24>18/24)*14/24+((D2+(8*(E2="grave")+16*(E2="Mineur"))/24+(D2+(8*(E2="grave")+16*(E2="Mineur"))/24>18/24)*14/24)>42/24)*14/24;1)[/B][/COLOR]
En la décortiquant un peu, elle n'est pas vraiment difficile :
Code:
[FONT="Courier New"][COLOR="Navy"][B][U]a[/U]	=MOD(
[U]c[/U]		D2+(8*(E2="grave")+8*(E2="Mineur"))/24
[U]f[/U]		+(
[U]g[/U]			D2+(8*(E2="grave")+8*(E2="Mineur"))/24
[U]j[/U]			>18/24
[U]k[/U]		)*14/24+(
[U]l[/U]			(
[U]m[/U]				D2+(8*(E2="grave")+8*(E2="Mineur"))/24
[U]p[/U]				+(
[U]q[/U]					D2+(8*(E2="grave")+8*(E2="Mineur"))/24
[U]t[/U]					>18/24
[U]u[/U]				)*14/24
[U]v[/U]			)
[U]w[/U]			>42/24
[U]x[/U]		)*14/24
[U]y[/U]	;1)[/B][/COLOR][/FONT]
Les lettres servent à indexer les lignes et ne font pas partie de la formule.

On voit que tout tourne autour de :

Code:
[SIZE="3"][U]c[/U]		D2+(8*(E2="grave")+8*(E2="Mineur"))/24[/SIZE]

répété en g, m, q.

Cette section donne l'heure de début augmentée de 8h si (E2="grave") est vrai, ou l'heure de début augmentée de 16h si (E2="Mineur") est vrai.
C'est l'heure limite de fin d'intervention si elle est inférieure ou égale à 18h de la date C2.
Sinon, il faut reporter l'heure limite au lendemain : c'est ce qu'on fait aux lignes fgjk en ajoutant 14h à cette heure limite. (14h = durée entre 18h du jour C2 et 8h du lendemain.)
Mais il se peut que cette nouvelle heure limite tombe après le lendemain à 18h : il faut donc ajouter à nouveau 14h pour trouver l'heure limite le surlendemain du jour C2. C'est ce que font les lignes lmnpqtuvwx.
On voit qu'à ce moment le temps calculé peut dépasser deux jours. D'où les lignes a et y : en prenant le résultat modulo 1, on élimine les jours pour ne garder que l'heure.
L'autre formule en découle : la partie entière de cfgjklmnpqtuvwx.

Sur ces bases, il doit être possible de compliquer un peu.
ROGER2327
#2396
 

Discussions similaires

Haut Bas