[XL-2003] Formule Excel pour un projet

[Supremereboot]

Bonjour

Je viens vers vous pour vous demandez de l'aide.
Voici mon probleme :
Je suis dans une hotline est nous recevons tous les jours des tickets d'incident entre 9h et 18h.
Je souhaiterai faire une formule qui me calcule un temp de resolution entre ces plages horaires suivant une variable.
J'ai deja reussi a faire la formule de la variable =J4+TEMPS(RECHERCHEV(C4;'Info resol'!B:C;2;FAUX);0;0) mais je n'arrive pas a integrer la plage horaire.

Exemple 1 :
Je recois un incident le 01/02/2010 à 9h. Mon delais de resolution est de 4h donc la fin de l'intervention est le 01/02/2010 à 13h ( cela j'arrive à le faire avec ma formule).

Exemple 2 ( je n'arrive pas a faire ) :
Je recois un incident le 01/02/2010 à 17h. Mon delais de résolution est de 4h donc la fin de l'intervention est le 02/02/2010 à 12h.
Il faut aussi que je tienne compte du samedi et dimanche ( non travaillé ), mais aussi des jours ferié.

Cordialement

[corona]

Bonjour,
Qu'est-ce qu'il y a en J4, C4 et le tableau de la feuille InfoResol que contient-il ?
Nom des champs et données ?

[wilfried_42]

Bonjour

Voici un debut de formule : En A1 j'ai la date : En B1 : l'heure de depart : en C1 le résultat de la recherche qui doit donner le temps d'intervention

En d1 je mets :

Code :

=(B1+C1)+("15:00" * ((B1+C1)>"18:00" *1))

le résultat en décimal me donne 1,5 soit 1 jour et demi si je l'additionne avec A1, j'aurai en format date et Heure : le lendemain à 12:00

il reste à traiter les jours feries, les samedis et dimanches, ça complique la formule (j'en ai une chez moi mais pas ou je suis actuellement), il te faut néanmoins une base de données avec les jours feriés ou une fonction personalisée que j'ai donnée : Ici

[Supremereboot]

Voila comment ce presente ma feuille :

Dans J4 j'ai la colonne avec la date et l'heure : 08/12/2010 12:10:00
je fais un recherche v qui additionne a l'heure le delais d'intervention.
en suite il me faut l'intervale de temps de 09h à 18h. Tout en sachant que tout cela doit ce calculer automatiquement. Les seul valeurs que je dois rentrée manuellement, c'est la date et l'heure lorsque je recois l'incident.

Quelqu'un a une idée pour continuer ma formule =J4+TEMPS(RECHERCHEV(C4;'Info resol'!B:C;2;FAUX);0;0)

J4 = 01/01/2010 17:00
info resol = c le delais d'intervention ( 4h ) que j'additionne à ma date et heure de depart en J4
K4 = resultat de la formule avec les plages horaire de 9h à 18h qui m'indiquera la date et l'heure precisie de la fin de l'intervention ( 02/01/2010 12:00 )

[wilfried_42]

re:

je t'avais détaillé simplement pour expliquer le principe de la formule

voici une série de fonction qui te faciliteront la vie : il faus les placer dans un module (VBA)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
Function estferie(ByVal cellule As Variant) As Boolean
    'Vrai si la valeur courante correspond a un jour férié
    Application.Volatile
    Dim Y As Long, i As Long, SylvesterDay As Date, SpecD, b As Long
    Y = Year(cellule)
    b = Abs((Y Mod 4 = 0 And Y Mod 100 <> 0) Or (Y Mod 400 = 0))
    SylvesterDay = DateSerial(Y, 1, 1) - 1
    i = EASTER(Y) - SylvesterDay - b
    'Pour la France (1 Mai = 121em jour d'une année NON-bissextile)
    SpecD = Array(1 - b, 121, 128, 195, 227, 305, 315, 359, i + 1, i + 39, i + 50)
    Y = Int(cellule) - SylvesterDay - b
    For i = 0 To UBound(SpecD)
        estferie = Y = SpecD(i)
        If estferie Then Exit For
    Next
End Function
Function NonOuvrable(ByVal cellule As Variant) As Boolean
    'Vrai si la valeur courante correspond a un jour férié
    Application.Volatile
    Dim Y As Long, i As Long, SylvesterDay As Date, SpecD, b As Long
    NonOuvrable = False
    If Weekday(cellule, vbMonday) > 5 Then ' Regarde si j'ai un samedi ou un dimanche
        NonOuvrable = True
        Exit Function
    End If
    Y = Year(cellule)
    b = Abs((Y Mod 4 = 0 And Y Mod 100 <> 0) Or (Y Mod 400 = 0))
    SylvesterDay = DateSerial(Y, 1, 1) - 1
    i = EASTER(Y) - SylvesterDay - b
    'Pour la France (1 Mai = 121em jour d'une année NON-bissextile)
    SpecD = Array(1 - b, 121, 128, 195, 227, 305, 315, 359, i + 1, i + 39, i + 50)
    Y = Int(cellule) - SylvesterDay - b
    For i = 0 To UBound(SpecD)
        NonOuvrable = Y = SpecD(i)
        If NonOuvrable Then Exit For
    Next
End Function
Function ProchainOuvrable(cellule As Variant) As Double
    Dim d As Double
    d = cellule
    Application.Volatile
    While NonOuvrable(d) = True ' Tant que la date est un jour non ouvrable,
        d = d + 1 ' j'ajoute 1 jour
    Wend
    ProchainOuvrable = d ' je transmets la date trouvée
End Function
 
Function EASTER(Yr As Long) As Long
    '*Dans la fonction originale, les données étaient de type Integer*
    Dim Century As Long, Sunday As Long, Epact As Long, N As Long
    Dim Golden As Long, LeapDayCorrection As Long, SynchWithMoon As Long
    Golden = (Yr Mod 19) + 1
    Century = Yr \ 100 + 1
    LeapDayCorrection = 3 * Century \ 4 - 12
    SynchWithMoon = (8 * Century + 5) \ 25 - 5
    Sunday = 5 * Yr \ 4 - LeapDayCorrection - 10
    Epact = (11 * Golden + 20 + SynchWithMoon - LeapDayCorrection) Mod 30
    If Epact < 0 Then Epact = Epact + 30
    If (Epact = 25 And Golden > 11) Or Epact = 24 Then Epact = Epact + 1
    N = 44 - Epact
    If N < 21 Then N = N + 30
    N = N + 7 - ((Sunday + N) Mod 7)
    EASTER = DateSerial(Yr, 3, N)
End Function

ensuite la formule en A2 : la date, En b2 : l'heure

Code :

=ProchainOuvrable(A2+(B2+(TEMPS(RECHERCHEV(C4;'Info resol'!B:C;2;FAUX);0;0)))+("15:00" * ((B2+(TEMPS(RECHERCHEV(C4;'Info resol'!B:C;2;FAUX);0;0)))>"18:00" *1)))

j'ajoute 15:00 (laps de temps compris entre 18:00 et 9:00) si la somme de l'heure saisie et du temps d'intervention dépasse 18:00

[Supremereboot]

et on ne peux pas faire la meme formule avec la date et l'heure dans la meme cellule ?

Je voulais aussi preciser que j'ai 2 temps de resolution 8h et 16h dc avec ta forumle ca marche correctement pour 8h mais pas pour 16h

[wilfried_42]

re:

ca serait bien que toutes les données soient fournies et un fichier de test n'aurait pas été de trop non plus
pour que ce soit plus simple pour moi, j'ai converti les horraires en decimal d'où les multiplications par 24 et la division finale par 24 pour remmetre en format horraire

Code :

=ProchainOuvrable(A2+ENT(((TEMPS(RECHERCHEV(C2;'Info resol'!B:C;2;FAUX);0;0)))*24)/9)+(((B2*24)+MOD((TEMPS(RECHERCHEV(C2;'Info resol'!B:C;2;FAUX);0;0)))*24;9))/24)+(((15*((B2*24)+MOD((TEMPS(RECHERCHEV(C2;'Info resol'!B:C;2;FAUX);0;0)))*24;9)>18)))/24))

[Supremereboot]

Bisare je n'arrive pas a faire fonctionner ta formule
Oui tu as raison ^^ Voila le fichier : cela sera plus claire

[wilfried_42]

re:

Je n'ai pas la place disponible pour te renvoyer ton fichier. je vois ca demain

[wilfried_42]

re: c'est demain

Bon la formule est devenue complexe pour 2 raisons :
Date et Heure dans la même cellule
RECHERCHEV intégré à la formule
dans le classeur, je te détaille succintement les différentes phases de calcul
Incident%20test1.zip

[Supremereboot]

pffffffou oui la forumle ce complique ^^. j'ai bien compri les differentes partie.
Lorsque je telecharge le ficher a la place de la formule j'ai "#NOM?". Dois je faire quelque chose en plus pour que cela fonctionne ?

[wilfried_42]

re:

personnellement je n'ai aucun problème à l'ouverture du fichier, la seule chose que je puisse mettre un doute c'est sur le degré de sécurité concernant les Macro. Ce qui bloquerait le fonction personalisée et qui affiche #nom

[Supremereboot]

ouai merci cela marche du tonner a par une petite chose.
J'ai noter en bleu les delais qui me pose probleme car la formule me met des heures avant 9h alors que normalement les horaires doivent etre de 9h a 18h pas plus ni moin ^^.
Pourrais tu, encore, faire quelque chose Wilfried ?
Pourrais tu aussi voir pour la colonne "Difference entre heure de transfere
et heure de cloture reel", j'aimerai avoir si la formule est bonne ?
Je n'ai pas besoin en faite de la colonne delais car celle-ci c'est exactement la meme que la colonne "Difference entre heure de transfere
et heure de cloture reel"

Par contre je viens de me rendre compte que les delai sont de 16h au lieu de soit 16h ou 8h par rapport a la rechercheV pour tous les incidents ?
Je viens aussi de me rendre compte que il integre mal le samedi et dimanche j'ai des horraires de resolution le dimanche et samedi

Merci

[wilfried_42]

Re:

Enfin, c'est lourd à gerer les heures avec poses medianes.
la formule :

Code :

=ProchainOuvrable((((C2-ENT(C2))+(MOD((RECHERCHEV(B2;'Info resol'!B:C;2;FAUX)/24);9/24)))+((15/24)*(((C2-ENT(C2))+(MOD((RECHERCHEV(B2;'Info resol'!B:C;2;FAUX)/24);9/24)))>(18/24)))+(ENT(((RECHERCHEV(B2;'Info resol'!B:C;2;FAUX))/24)/(9/24))))+ENT(C2))

[Supremereboot]

Pfffffouuuua tu es trop fort merci cela marche correctement pour tout les cas.
Juste une derniere chose avant de te laisser.

Je viens de resoudre le probleme de nombre de jours.

Dernier fignolage : je souhaiterai que lorsque je depasse 8h ou 16h en fonction de l'intervention que la formule =JOUR(M10) & " Jour(s) " & HEURE(M10) & " Heure(s) " & MINUTE(M10) & " Minute(s)" ce colorie en rouge lorsque je suis hors horaire et lorsque je suis dans les horaires de resolution elle ce colore en bleu. Comment aussi integrer la notion de weekend et jour ferié dans cette formule ?

encore merci

[Supremereboot]

Merci encore wilfried, mais pourrais tu encore m'aider car je n'arrive pas a faire la formule du message du dessus

[wilfried_42]

re:

désolé, j'étais ailleur, tu pourrais préciser, je n'ai pas tout compris, envoi un exemple, avec les couleurs désirées.

[Supremereboot]

Deja bonne année a toi wilfried ^^.

Alors voila le fichier test.
Ce que j'aimerai : que la colonne N "delais de resolution" tienne compte des weekend et des periodes 9h-18h. Et que lorsque le delais de 8h ou 16h ( cela depend des recherches V ) est depassé il colore la case en rouge alors que si le delais est inferieur a 8h ou 16h ( cela depend des recherches V ) cela depend, il la colorie en bleu.

Merci

[wilfried_42]

re: merci tous mes voeux à toi et aux tiens

le plus simple est de tester l'heure prevue de fin d'intervention et l'heure réelle. l'heure prevue tenant compte des jours ouvrables ainsi que de la pause entre 18:00 et 9:00 il est plus facile de tester les colonnes L et K

Mfc :
Formule 1 : =L2>K2 couleur Rouge
Formule 2 : =K2>L2 Couleur Bleue

[Supremereboot]

lol c vrai chui trop "con". marche nikel
par contre tu ma dit que la colonne N "delais de resolution" tiens compte des plage horaire 9h18h, je ne pense par car si tu regarde bien il calcule le temps total "Date + Heure de cloture reel" - "Date + Heure de transfere" sans tenir compte des plages horaires et des weekend.