Formule Excel pour un projet

**Supremereboot** · 28/12/2010, 12h09

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

**Philippe Tulliez** · 28/12/2010, 12h24

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** · 28/12/2010, 12h53

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=(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** · 28/12/2010, 14h10

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** · 28/12/2010, 14h38

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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** · 28/12/2010, 14h42

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** · 28/12/2010, 16h03

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

=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** · 28/12/2010, 16h16

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

**wilfried_42** · 28/12/2010, 17h19

re:

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

**wilfried_42** · 29/12/2010, 08h00

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** · 29/12/2010, 09h09

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** · 29/12/2010, 10h57

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** · 29/12/2010, 12h26

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** · 29/12/2010, 13h15

Re:

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

=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** · 29/12/2010, 13h35

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** · 30/12/2010, 16h51

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

**wilfried_42** · 31/12/2010, 10h43

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** · 03/01/2011, 09h18

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** · 03/01/2011, 11h09

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** · 03/01/2011, 11h50

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.

Formule Excel pour un projet

Excel

Discussions similaires

Partager

Partager