Repérer dépassements d'amplitude horaire (13h)

Bonjour à tous !

Dans le fichier joint, je cherche à pouvoir mettre en évidence (surbrillance orange) les N° de plannings dont l'amplitude horaire est supérieur à 13h

Quelques précisions sur ce fichier :

Ce fichier comporte un "échantillon".

Cet échantillon comporte, sur chacune de ses lignes, des numéros de course, qui ont toutes une heure de départ et une heure d'arrivée.

Sur chacune de ces courses, les "N°" de plannings présents sur cette course sont définis (colonnes K à BH).

Le fichier est peut-être plus explicite...

Merci par avance à tous ceux qui se pencherons sur ce problème...

P.S.: Cette demande a été également postée sur d'autres forums, sans succès pour l'instant

Salut,

Je ne comprends pas très bien sur quelles bases tu sais que pour la cellule P10 il faut prendre la cellule H10 comme point de départ et la cellule J12 comme point d’arrivée. Dans aucune des colonnes A à G ou I, je ne vois d’inscription permettant logiquement de décider de choisir ces points de départ et d'arrivée.

Idem pour la cellule Z10 : comment sais-tu qu’il faut prendre H10 et J13 comme points de départ et d’arrivée ?

De plus, ces inscriptions dans les colonnes K à BH, tu les fais manuellement et c’est à ce moment-là que tu voudrais que la couleur passe à l’orange si la durée est de plus de 13 heures ??

A te relire.

Bonjour Yvouille, et merci de te pencher sur mon problème.

En fait, les "points de départs" et les "points d'arrivées", sont simplement, à une date donnée et pour un numéro donné, la 1ère heure de départ où il apparait et la dernière heure d'arrivée où il apparait...

Prenons le N°9 :

Considérons toutes les lignes où il apparait à une date donnée (ici le 5 octobre)

Nous recherchons "l'heure de départ" la moins élevée (le plus tôt donc) dans laquelle il apparait dans cette journée. Ici 7h15 pour ce N° 9

Nous recherchons également l'heure d'arrivée la plus élevée (le plus tard donc) dans laquelle il apparait dans cette même journée. Ici 20h20 pour ce N°9

La différence entre ces deux heures est appelée "l'amplitude" (horaire). Ici, 20h20 moins 7h15 = 13h05 d'amplitude, ce qui est donc supérieur à 13h, et qui doit donc être "identifié" en tant que dépassement en surbrillance.

Effectivement, pour le N°8 dans mon exemple, il peut y avoir une ambiguïté : Son "heure de départ" la moins élevée le 5 octobre est également 7h15, son heure d'arrivée la plus tardive est 23h49, donc, "dépassement" des 13h, mais il est vrai qu'il est déjà "en dépassement" dès la ligne précédente où il apparait (20h20, comme le N°9).

Dans le cadre de mes besoins, peu importe finalement si "l'heure de fin" prise en compte est la dernière où un N° apparait, ou la 1ère ou le dépassement des 13 heures est constatée, selon ce qui est plus facile à coder en VBA... Par contre, l'heure de début doit vraiment toujours être la 1ère heure de départ dans laquelle un N° apparait dans une journée...

Ce que le fichier exemple ne montre pas (je l'ai trop "épuré"), c'est qu'il peut y avoir beaucoup de date différentes, ces recherches doivent bien se faire date par date..

Sinon, pour la deuxième question, peu importe si la surbrillance apparait au moment de la saisie ou bien après l'exécution d'une macro, ma préférence allant vers la méthode qui dure le moins de temps, sachant qu'il peut y avoir jusqu'à 2500 lignes (quelquefois plus, mais rarement), et surtout potentiellement beaucoup de dates différentes...

Voilà, j'espère avoir été plus clair,

Merci encore de l'intérêt porté !

Re-bonjour,

Kiriko a écrit :

Voilà, j'espère avoir été plus clair

Non, pas trop, je dirais même que tu as apporté de nouveaux brouillards

Tu dis que tu peux avoir beaucoup de dates différentes. Sont-elles toujours groupées par jour et dans l’ordre chronologique ou peuvent-elles être réparties n’importe comment, donc toutes mélangées ?

J’ai compris qu’il fallait prendre l’heure de départ de 07:15 car c’est la première heure de départ possible pour la date du 5 octobre, donc la première heure de départ des lignes 2 à 13. Merci de me confirmer que j'ai bien compris cela.

Mais pourquoi je ne prends pas tout de suite l’heure d’arrivée de 23:49 puisque c’est la plus grande des heures d’arrivée de ces mêmes lignes 2 à 13 concernant le 5 octobre ????

Kiriko a écrit :

... et pour un numéro donné ....

A quoi correspondent ces numéros ?

Cordialement.

Re bonjour Yvouille !

  • Les dates peuvent être toutes mélangées, et même les horaires sur une même date (d'une ligne sur l'autre) ne sont pas forcément croissants (mais bien sûr, sur une même ligne, l'heure d'arrivée se situe toujours après l'heure de départ)
  • A la lumière de tes questions, je comprend que l'omission d'un détail (que je pensais anodin) a beaucoup jouer sur l'incompréhension de mon message : Les N° donnés sont des N° de Plannings, donc des "identifiants" de personnes.
  • Donc, le 7h15 par exemple ne doit pas être considéré comme " la première heure de départ des lignes 2 à 13" (ici, du 5 octobre), mais uniquement la première heure de départ des plannings N° 9, 8 et 7 (le 5 octobre).

Autre exemple, pour être plus clair : Le 5 octobre, la première heure de départ du Planning N° 3 par exemple est ici 10h (ainsi que le N° 2 et le N°1)...

Mes excuses pour la faible qualité de mes explications, tu me diras si j'arrive enfin à être un peu plus clair....

Merci !

Re,

Donc si j'ai bien compris, dans le fichier ci-joint pour l'identifiant 3 qui apparait dans les lignes 2 à 9, on prendrait l'heure de départ 10:00 et l'heure d'arrivée 20:08 et pour l'identifiant 7, l'heure de départ 07:15 et l'heure d'arrivée 18:15 ?

Si ceci est bien correct, alors encore deux questions avant de me lancer dans la création d'un code : Est-il possible que la date de départ soit un jour et la date d'arrivée le lendemain ou encore plus tard ? Et est-il possible qu'un identifiant fasse un voyage une première fois (du 5 au 6 octobre ou uniquement le 5 octobre) et un second voyage une deuxième fois (par exemple le 25 et 26 octobre) ?

Si tu penses que c'est nécessaire, fournis-moi un fichier avec des exemples incluant les demandes ci-dessus.

Cordialement.

Bonsoir Yvouille, et merci encore de te casser la tête à essayer de comprendre mes explications...

Mais oui, c'est exactement cela, tu as très bien compris...

La précision est que toute ces lignes peuvent être dans n'importe quel ordre, et qu'il y a plusieurs dates différentes...

Pour répondre à tes demandes de précisions, je dirais que chaque "course" (ligne) est attribuée à une date donnée... Cependant, il peut arriver qu'une course dépasse les minuit, mais dans ce cas, j'utilise [hh]:[mm], et je note 24:00 pour minuit, 25:00 pour 1h,....

D'une manière générale, une "journée" de travail débute à 4h du matin, et se termine à 3h le lendemain (27:00).

J'ai rajouté quelques lignes dans l'exemple, avec un horaire terminant après minuit (tapé 24:16 mais apparaissant 00:16), et en "mélangeant" les lignes...

Merci encore à toi !!!

Comme je ne vois encore pas trop par où commencer, encore des questions

As-tu un tableau de 2500 lignes à traiter et tu voudrais qu'en une seule fois tes cellules des colonnes K à BH se mettent en surbrillance selon tes désirs ou as-tu un tableau où toutes les cellules sont déjà en surbrillance d'une manière correcte et que tu voudrais que ces marquages se modifient au fur et à mesure de tes modifications ???

Dans les deux cas, ne serait-il pas possible de trier au préalable (éventuellement par macro) le tableau par date (colonne B) ? Ceci simplifierait grandement les choses.

Sinon, à propos de tes heures après minuit, utilise donc le format d'heure [hh]:mm comme sur l'image ci-dessous.

capture 01 10

Cordialement.

Rebonsoir Yvouille !

J'avais plutôt dans l'idée d'une macro qui s’exécuterait "à la demande" en une seule fois, mais l'autre solution (au fur et à mesure) me conviendrait également, à voir laquelle des deux est le plus facile à coder, voir laquelle alourdie le moins le fichier...

Quand au tri, il vaut mieux ne pas partir dessus, elle sont classées dans un ordre qui n'a pas vraiment de logique mais qui doit rester le même... A moins de le trier pour exécuter la macro et de le remettre en place ensuite, mais je pense que ce serait assez lourd, non ?

Pour info, je dispose maintenant d'un autre code qui s'adapte à ce fichier, qui lui, vérifie si un identifiant apparait sur 2 lignes dans une même plage horaire (si il y a "chevauchement")... Je ne te cache pas que je ne suis pour rien dans ce code, un forumeur (merci mapomme) me l'a fourni... Peut être que certaines variables peuvent être réutilisées ?? :

Sub VerifChevauchements()
Range("K1:BH1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
    Range("K2").Select

   PresenceANO = False

Dim DerLigne As Long, Plage As Range, C As Range, C2 As Range
    DerLigne = Cells(Rows.Count, 1).End(xlUp).Row

Dim FF As Worksheet, i, j, k, m, n
Dim MaxLig, X, Distinctes As Boolean
Dim Cond_Jours As Boolean, Cond_DebutDansPlage As Boolean
Dim Cond_FinDansPlage As Boolean, Cond_LigneDiff As Boolean
Dim Cond_Chevauche As Boolean

Set FF = Sheets("échantillon daté")
MaxLig = FF.Range("B" & Rows.Count).End(xlUp).Row
ReDim t(1 To MaxLig, 1 To 60) '30
t = FF.Range("A1:BH" & MaxLig).Value

'ReDim Preserve T(1 To MaxLig, 1 To 32)
'ReDim Result(1 To MaxLig, 1 To 20)
ReDim Preserve t(1 To MaxLig, 1 To 62)
ReDim Result(1 To MaxLig, 1 To 50)

' Ajout des corrections Jour+heures
' si heure est inf à 3h00, on change de jour
For i = 2 To MaxLig
   t(i, 61) = t(i, 2) + t(i, 8)
   If t(i, 8) < TimeSerial(3, 0, 0) Then t(i, 61) = 1 + t(i, 61)
   t(i, 62) = t(i, 2) + t(i, 10)
   If t(i, 10) < TimeSerial(3, 0, 0) Then t(i, 62) = 1 + t(i, 62)
Next i

For i = 2 To MaxLig
For j = 11 To 60
   X = t(i, j)
   If X <> "" Then
      For k = i + 1 To MaxLig
      For m = 11 To 60
         If t(k, m) = X Then
            'les plages se chevauchent elles ?
            ' = non (plages distinctes)
            ' les plages doivent être attribuées aux mêmes jours (colonne B)
            Cond_Jours = (t(k, 2) = t(i, 2))
            Cond_DebutDansPlage = (t(i, 61) <= t(k, 61)) And (t(k, 61) <= t(i, 62))
            Cond_FinDansPlage = (t(i, 61) <= t(k, 62)) And (t(k, 62) <= t(i, 62))
            Cond_Chevauche = Cond_Jours And (Cond_DebutDansPlage Or Cond_FinDansPlage)
            If Cond_Chevauche Then
               Result(i, j - 10) = 1
               Result(k, m - 10) = 1
               PresenceANO = True
            End If
         End If
      Next m
      Next k
   End If
Next j
Next i

Application.ScreenUpdating = False
FF.Range("A2:BH" & MaxLig).Interior.Pattern = xlNone '''''''''''''''''''
For i = 2 To MaxLig
   For j = 1 To 50
      If Result(i, j) = 1 Then FF.Cells(i, 10 + j).Interior.Color = RGB(255, 93, 93)
         Next j
Next i
For i = 2 To MaxLig
For j = 1 To 50
      If Result(i, j) = 1 Then FF.Cells(1, 10 + j).Interior.Color = RGB(255, 93, 93)
   Next j
Next i

        Application.ScreenUpdating = True

Application.ScreenUpdating = False

If PresenceANO = True Then MsgBox "Des chevauchements d'horaires sont présents ! Ils ont été mis en surbrillance"

Application.ScreenUpdating = True    
    Set Plage = Range("K2:BH" & DerLigne)
    For Each C In Plage
        If C.Value <> "" Then
            Set C2 = Range(Cells(C.Row + 1, "K"), Cells(DerLigne, "BH")).Find(C.Value, , , xlWhole, xlByRows, xlPrevious)
            If Not C2 Is Nothing Then
                If C.Address = C2.Address Then Exit Sub
                If CDate(Cells(C2.Row, "J").Value - Cells(C.Row, "H").Value) > CDate("13:00") Then
                    C.Interior.ColorIndex = 46
                    C2.Interior.ColorIndex = 46
                End If
            End If
        End If
    Next C

End Sub

Merci encore !!!

Salut,

Je pense que ça ne sert à rien qu'on soit deux à travailler en parallèle ; l'un des deux travaille dans le vide. Je te laisse continuer sur ExcelDownloads. Tu avais dit ci-dessus que tu n’avais pas reçu de réponse sur les autres forums visités, raison pour laquelle je m’étais lancé à t’aider gracieusement.

Cordialement.

Bonjour Yvouille !

Si d'aventure mon attitude t'a froissée, toutes mes excuses en ce cas.

Tu es bien le 1er à t'être penché sur mon soucis, mais effectivement, après toi, un autre forumeur m'a proposé une MFC avec formule matricielle, ce qui fonctionne, mais une formule matricielle sur un fichier déjà lourd qui comporte 2500 lignes, je pense que ça rame pas mal...

En tous cas, tu as toute ma reconnaissance pour t'être penché sur mon soucis, je m'excuse encore si mon comportement a pu te faire croire à un quelconque manque de respect, ce qui, je peux te l'assurer, n'est vraiment pas le cas, du moins dans l'intention...

Cordialement,

Ne te fais pas trop de souci, je ne l'ai pas si mal pris que ça

Mais effectivement qu'il est inutile qu'on travaille à deux en parallèle sur le même fichier.

J'ai personnellement demandé de l'aide sur différents forums, mais j'ai toujours indiqué sur le premier que ma demande était close avant d'ouvrir une discussion sur un deuxième, histoire de ne pas faire bosser quelqu'un dans le vide.

Cordialement.

Bonjour

J'avais étudié un peu la question et je livre le résultat des mes essais (si cela peut aider)

Risque d'être long si beaucoup de lignes

Bonjour Banzai64, et merci de ta participation, plus que productive !!

Ton code marche parfaitement!

Reste à moi de le tester avec un fichier contenant beaucoup de lignes !!!

Peut-être devrais-je créer un autre sujet, mais je cherche également à mettre en surbrillance (d'une autre couleur) les temps inférieurs à 11h, entre, pour un N° identifiant donné (N° de planning), entre sa dernière heure d'arrivée d'un jour J, et sa première heure de départ du jour J plus 1 (si J plus 1 il y a). Est-ce plus compliqué ?

En tous les cas, merci beaucoup déjà pour le travail fourni, à toi Banzai64, ainsi qu'à toi Yvouille !!!

Bonne journée à tous !!

Rechercher des sujets similaires à "reperer depassements amplitude horaire 13h"