Calculer le nombre d’heures ouvrées entre deux dates

Y compris Power BI, Power Query et toute autre question en lien avec Excel
a
acropoter
Jeune membre
Jeune membre
Messages : 25
Inscrit le : 31 juillet 2010
Version d'Excel : 2007 EN

Message par acropoter » 3 février 2018, 17:49

Bonjour,
Après plusieurs essais je n’arrive pas à écrire la formule qui me permettra de calculer le nombre d’heures travaillées entre 2 dates au format jour/mois heure/min, en tenant des heures d’ouverture d’un atelier.

Ci joint un fichier de suivi atelier pour une ligne de production donnée.
Le premier onglet n’est que la matrice pour impression papier du doc suivi d’atelier
Le second onglet est le fichier de reporting Excel, que le chef d’équipe de nuit complète à partir des docs papier de suivi.
Le troisième onglet me sert à stocker les listes diverses, notamment celles des jours fériés, heure ouverture etc …

Sur le second onglet fichier de reporting j’ai commencé des calculs de durée d’occupation/phases de la colonne AX à BF, qui fonctionnent tant que l’atelier est en 3*8, avec la fonction NETWORKDAYS.INTL (en disant que le weekend = 17 (samedi) pour prendre en compte les phases qui débutent le dimanche en 21h00 et 24H00) + Partie entière.
Mon souci c’est que l’atelier va passer en 3*8 + week-end de 8 :25 à 21 :00, et là je pioche un peu …
Je pensais ajouter une colonne dans le fichier pour mentionner le type d’horaire appliqué à l’atelier, puis avec un vlookup allé chercher les horaires d’ouverture, mais je me perd ...
Quelqu’un pourrait il m.aider svp ?
Merci
Modifié en dernier par acropoter le 3 février 2018, 23:50, modifié 1 fois.
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'171
Appréciations reçues : 446
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 3 février 2018, 22:16

Bonsoir,

J'avais trouvé un peu hasardeuse ta formule qui ne pouvait fonctionner que du fait de la conjonction fin vendredi début dimanche sur la même heure... :)
Mais ta question porte porte sur le calcul de durée dans le régime 3*8+WE...
La formulation classique peut (à mon goût) demeurer réalisable de façon raisonnable lorsque la durée ouvrables des jours ouvrés est égale, mais avec tes variations, je n'essaie même pas et te propose d'emblée une fonction personnalisée.
Une fonction personnalisée est une procédure VBA, de type Function, que l'on place dans un Module Standard, et que l'on utilise dans les mêmes conditions que les autres fonctions d'Excel.
Function D3_8WE(dhd As Date, dhf As Date)
    Dim Tdh(), h!, i%, j%
    Application.Volatile False
    ReDim Tdh(Int(dhf) - Int(dhd), 1): j = UBound(Tdh, 1)
    Tdh(0, 0) = (Int(dhd) + 1) Mod 7
    For i = 1 To j
        Tdh(i, 0) = (Tdh(i - 1, 0) + 1) Mod 7
    Next i
    h = dhd - Int(dhd)
    Select Case Tdh(0, 0)
        Case 3 To 6: Tdh(0, 1) = 1 - h
        Case 1, 2
            If h < 515 / 1440 Or h >= 21 / 24 Then
                D3_8WE = CVErr(xlErrNA): Exit Function
            End If
            Tdh(0, 1) = 21 / 24 - h
        Case 0
            If h >= 21 / 24 Then
                D3_8WE = CVErr(xlErrNA): Exit Function
            End If
            Tdh(0, 1) = 21 / 24 - h
    End Select
    h = dhf - Int(dhf)
    Select Case Tdh(j, 0)
        Case 3 To 6: Tdh(j, 1) = h
        Case 1, 2
            If h <= 515 / 1440 Or h > 21 / 24 Then
                D3_8WE = CVErr(xlErrNA): Exit Function
            End If
            Tdh(j, 1) = h - 515 / 1440
        Case 0
            If h > 21 / 24 Then
                D3_8WE = CVErr(xlErrNA): Exit Function
            End If
            Tdh(j, 1) = h
    End Select
    For i = 1 To j - 1
        Select Case Tdh(i, 0)
            Case 3 To 6: Tdh(i, 1) = 1
            Case 1, 2: Tdh(i, 1) = 745 / 1440
            Case 0: Tdh(i, 1) = 21 / 24
        End Select
    Next i
    h = 0
    For i = 0 To j
        h = h + Tdh(i, 1)
    Next i
    D3_8WE = h
End Function
L'usage d'une fonction perso permet en outre une vérification de la validité des heures saisies de début et fin selon la date.
Ainsi une heure de début >= 21h00 pour une date de vendredi à dimanche ou < 08h35 pour une date de samedi ou dimanche, sera reconnue erronée et entraînera l'affichage de la valeur d'erreur #N/A.
De même pour une heure de fin > 21h00 (vendredi à dimanche) ou <= 08h35 (samedi ou dimanche).
Sinon le calcul est tout à fait basique : on monte un petit tableau allant du jour de début au jour de fin en codant le jour de la semaine dont il s'agit, et en regard on établit la durée à comptabiliser pour chacun des jour, selon le jour, et de façon différenciée selon qu'il s'agit du premier, du dernier, ou d'un autre intermédiaire. Et on fait bêtement l'addition... :wink:

J'ai rendu la fonction non recalculable lors des recalculs automatiques avec la commande Application.Volatile False, estimant que ton utilisation ne nécessitait pas qu'elle soit recalculée en permanence, et qu'il n'est pas utile de multiplier les recalculs lorsque ce n'est pas nécessaire. Si toutefois tu jugeais préférable qu'elle soit recalculée, il te suffit simplement de supprimer le False de cette ligne.

Le code de la fonction se trouve dans Module1.
Je n'ai pris le temps que d'un test sommaire en Sheet1... Tu me diras si tu constates des anomalies dans les résultats renvoyés...

Cordialement.
acropoter__feuille suivi et compil.xlsm
(87 Kio) Téléchargé 80 fois
Modifié en dernier par MFerrand le 6 février 2018, 19:51, modifié 1 fois.
a
acropoter
Jeune membre
Jeune membre
Messages : 25
Inscrit le : 31 juillet 2010
Version d'Excel : 2007 EN

Message par acropoter » 6 février 2018, 06:04

Bonjour,
Et merci pour votre réponse.
Je ne suis pas familier de VBA et de l’usage de fonction personnalisée mais je vais essayer.
Pouvez vous svp m’orienter vers un post qui explique comment on place ce code dans un module ?

Par ailleurs, je crois comprendre que le code fonctionnera si les horaires d’ouverture d’atelier sont en 3*8+weekend.
Serait ce possible de le compléter pour qu’il fonctionne en rotation journée, 2*8 et 3*8 sans week-end (je pourrai préciser le type de rotation dans un calendrier ou dans une colonne supplémentaire du tableau de compil.

Merci encore
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'171
Appréciations reçues : 446
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 6 février 2018, 13:57

C'est possible, on ajoute un argument pour la rotation, et on aura une fonction pour l'ensemble. :wink: Faut regarder de plus près les cas pour garder les calculs communs lorsqu'ils le sont et ne différencier que quand ils diffèrent...

Je te mets en réserve ! :lole: Je sors d'une fibroscopie et je pense qu'il vaut mieux attendre un peu pour des calculs réclamant de l'attention, j'ai atteint le délai où je suis autorisé à manger et boire, et les effets désagréables résiduels s'estompent un peu... A plus tard !
a
acropoter
Jeune membre
Jeune membre
Messages : 25
Inscrit le : 31 juillet 2010
Version d'Excel : 2007 EN

Message par acropoter » 6 février 2018, 19:25

Oh pétard, oui bien sûr ! La santé d.abord 😉.
N.hesitez pas à me contacter par mp lorsque vous irez mieux, et je tacherai d.apporter les précisions nécessaires.
Bon rétablissement
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'171
Appréciations reçues : 446
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 7 février 2018, 15:27

Bonjour,

Voilà la version générale, toutes rotations :
'rotations : "2_8", "3_8", "3_8we"
Function DTTROT(dhd As Date, dhf As Date, rot As String)
    Dim Tdh(), h!, i%, j%, r%
    Application.Volatile False
    For r = 1 To 3
        If Array("", "2_8", "3_8", "3_8we")(r) = LCase(rot) Then Exit For
    Next r
    If r > 3 Then DTTROT = CVErr(xlErrRef): Exit Function
    ReDim Tdh(Int(dhf) - Int(dhd), 1): j = UBound(Tdh, 1)
    Tdh(0, 0) = (Int(dhd) + 1) Mod 7
    For i = 1 To j
        Tdh(i, 0) = (Tdh(i - 1, 0) + 1) Mod 7
    Next i
    h = dhd - Int(dhd)
    Select Case Tdh(0, 0)
        Case 3 To 6
            If r = 1 Then
                If h < 6 / 24 Or h >= 20.5 / 24 Then
                    DTTROT = CVErr(xlErrNA): Exit Function
                End If
                Tdh(0, 1) = 20.5 / 24 - h
            Else
                Tdh(0, 1) = 1 - h
            End If
        Case 1, 2
            If r = 1 Then
                DTTROT = CVErr(xlErrNA): Exit Function
            ElseIf r = 2 Then
                If Tdh(0, 0) = 1 Then
                    DTTROT = CVErr(xlErrNA): Exit Function
                Else
                    If h < 21 / 24 Then
                        DTTROT = CVErr(xlErrNA): Exit Function
                    End If
                    Tdh(0, 1) = 1 - h
                End If
            Else
                If h < 515 / 1440 Or h >= 21 / 24 Then
                    DTTROT = CVErr(xlErrNA): Exit Function
                End If
                Tdh(0, 1) = 21 / 24 - h
            End If
        Case 0
            If r = 1 Then
                If h < 6 / 24 Or h >= 20.5 / 24 Then
                    DTTROT = CVErr(xlErrNA): Exit Function
                End If
                Tdh(0, 1) = 20.5 / 24 - h
            Else
                If h >= 21 / 24 Then
                    DTTROT = CVErr(xlErrNA): Exit Function
                End If
                Tdh(0, 1) = 21 / 24 - h
            End If
    End Select
    h = dhf - Int(dhf)
    Select Case Tdh(j, 0)
        Case 3 To 6
            If r = 1 Then
                If h <= 6 / 24 Or h > 20.5 / 24 Then
                    DTTROT = CVErr(xlErrNA): Exit Function
                End If
                Tdh(j, 1) = h - 6 / 24
            Else
                Tdh(j, 1) = h
            End If
        Case 1, 2
            If r = 1 Then
                DTTROT = CVErr(xlErrNA): Exit Function
            ElseIf r = 2 Then
                If Tdh(j, 0) = 1 Then
                    DTTROT = CVErr(xlErrNA): Exit Function
                Else
                    If h <= 21 / 24 Then
                        DTTROT = CVErr(xlErrNA): Exit Function
                    End If
                    Tdh(j, 1) = h - 21 / 24
                End If
            Else
                If h <= 515 / 1440 Or h > 21 / 24 Then
                    DTTROT = CVErr(xlErrNA): Exit Function
                End If
                Tdh(j, 1) = h - 515 / 1440
            End If
        Case 0
            If r = 1 Then
                If h <= 6 / 24 Or h > 20.5 / 24 Then
                    DTTROT = CVErr(xlErrNA): Exit Function
                End If
                Tdh(j, 1) = h - 6 / 24
            Else
                If h > 21 / 24 Then
                    DTTROT = CVErr(xlErrNA): Exit Function
                End If
                Tdh(j, 1) = h
            End If
    End Select
    For i = 1 To j - 1
        Select Case Tdh(i, 0)
            Case 3 To 6
                If r = 1 Then
                    Tdh(i, 1) = 14.5 / 24
                Else
                    Tdh(i, 1) = 1
                End If
            Case 1, 2
                If r = 1 Then
                    Tdh(i, 1) = 0
                ElseIf r = 2 Then
                    If Tdh(i, 0) = 1 Then
                        Tdh(i, 1) = 0
                    Else
                        Tdh(i, 1) = 3 / 24
                    End If
                Else
                    Tdh(i, 1) = 745 / 1440
                End If
            Case 0
                If r = 1 Then
                    Tdh(i, 1) = 14.5 / 24
                Else
                    Tdh(i, 1) = 21 / 24
                End If
        End Select
    Next i
    h = 0
    For i = 0 To j
        h = h + Tdh(i, 1)
    Next i
    DTTROT = h
End Function
Elle s'allonge pas mal évidemment vu la multiplication des cas, et j'espère bien sûr avoir évité des bévues d'inattention... :wink:
J'ai conservé une structure qui me paraissait ne pas trop allonger tout en gardant une rationalité relativement aisée d'appréhension des cas : on traite (comme auparavant) le jour de début, le jour de fin, les jours intermédiaires ; dans chacune de ces catégories on distingue les jours, du lundi au jeudi, les samedi et dimanche, le vendredi [le code jour utilisé est 0 pour vendredi, 1 et 2 pour samedi-dimanche, 3 à 6 pour lundi à jeudi, on l'obtient avec l'opérateur Mod qui permet de se passer d'utiliser Weekday (équivalent de JOURSEM) pour renvoyer directement une valeur dans une série 0-6 centrant le 0 sur le vendredi] ; dans chaque catégorie de jours l'évaluation sera dépendante de la rotation, les rotations "3_8" et "3_8we" pouvant avoir des résultats communs dans plusieurs cas, mais pour "3_8" on est amené à distinguer samedi et dimanche qui n'y ont pas le même traitement (mais on le fait dans la catég. Samedi-Dimanche pour conserver l'homogénéité du découpage).

Le commentaire qui précède la fonction indique comment doit être indiquée la rotation... J'ai gardé un argument sous forme texte, mais il est converti en 1, 2 ou 3 dans la fonction, aussi si l'on souhaite utiliser 1, 2 ou 3 pour passer les rotations, on peut le faire en :
remplaçant rot As String par r As Integer dans la déclaration,
supprimer r% dans les déclarations de variables (première ligne de code suivant la déclaration de proc.),
éliminer ce fragment de code :
    For r = 1 To 3
        If Array("", "2_8", "3_8", "3_8we")(r) = LCase(rot) Then Exit For
    Next r
    If r > 3 Then DTTROT = CVErr(xlErrRef): Exit Function
qui opérait la conversion des rotations-texte en valeurs numériques, et testait qu'il n'y avait pas d'erreur dans l'argument (en cas d'erreur la fonction renvoie l'erreur #REF! pour spécifier qu'elle a décelé une erreur dans l'indication de la rotation).
Et le remplacer par :
    If r < 1 Or r > 3 Then DTTROT = CVErr(xlErrRef): Exit Function
a
acropoter
Jeune membre
Jeune membre
Messages : 25
Inscrit le : 31 juillet 2010
Version d'Excel : 2007 EN

Message par acropoter » 7 février 2018, 19:38

Bonsoir,
ébé ! Je suppose qu’il me faudrait beaucoup d’enseignement et de pratique pour être capable d’ecrire un tel code un jour. J’espere que vous allez mieux.
D’abord je vais commencer par un gros MERCI pour avoir consacré du temps à la résolution de mon problème.
Ensuite je vais d’abord m’attacher à essayer de comprendre comment configurer ce code dans mon fichier moi qui n’utilise aujourd’hui que des fonctions calculs dans les cellules ou le résultat doit s’afficher 😌.
Alors pardonnez d’avance les questions stupides ci dessous :
- chaque ligne du tableau correspondant à une « charge » à laquelle nous appliquons differentes phases de travail dans l’ordre chronologique des colonnes de gauche à droite, comment dois je procéder pour faire apparaître dans les colonnes AX à BF, le nombre d’heures et minutes de travail qui se sont écoulées entre le début et à la fin de ces phases en tenant compte des rotations (horaires ouvertures d’ateliers), et des jours fériés ?
- même question lorsque je souhaite calculer le temps de travail écoulé entre le début d’une phase de la charge 1, et le début de la même phase mais de la charge suivante (lignes différentes)
Les intentions de calcul sont dans le fichier source téléchargé.

Enfin, dans un premier temps, dois je ajouter une colonne dans le tableau pour préciser ligne à ligne le type de rotation de l’atelier sur la période incluse entre le début de la première phase et la fin de la dernière,
Ou dois je renseigner un calendrier quelque part ou je renseignerais le type de rotation pour chaque jour de l’annee, et les jours fériés ??

Merci
a
acropoter
Jeune membre
Jeune membre
Messages : 25
Inscrit le : 31 juillet 2010
Version d'Excel : 2007 EN

Message par acropoter » 7 février 2018, 20:35

Ré-bonsoir,
Après plusieurs lectures je pense avoir compris 😉
Si je veux calculer le temps de travail écoulé entre deux cellules A1 et A2 au format date et heure, il me suffit d’entrer la fonction suivante dans la cellule ou je veux que le résultat s’affiche : = "2_8"DTTROT(A1;A2)
dans la mesure où le type de rotation est 2*8 entre ces deux dates.
Est ce celà ?
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'171
Appréciations reçues : 446
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 7 février 2018, 20:47

Une fonction personnalisée présente cet avantage que son code étant placé dans un module standard de ton classeur, comme toute autre macro ordinaire, il n'y a rien à configurer, rien à lancer, on l'utilise à l'instar de toute autre fonction d'Excel en formulant, comme si elle avait toujours existée... :wink:
Un second avantage est que conçue pour opérer un calcul que l'on ne pouvait au moyen des fonctions habituelles, ou de façon compliquée avec une ou plusieurs formules longues, son usage sera généralement plus simple et l'on utilisera le plus souvent la fonction toute seule dans une formule simple. :wink:

La fonction DTTROT [pour Durée toutes rotations ! :lole: ] nécessite qu'on lui fournisse 3 arguments pour lui permettre de faire son calcul. On les peut les qualifier ainsi : DTTROT(date-heureDébut;date-heureFin;Rotation)
Les deux premiers, on aurait à les produire pour n'importe quelle formulation : calculer la durée écoulée entre deux moments nécessite toujours que l'on fournissse le moment du départ et celui de l'arrivée, naturellement sous forme de valeurs dates-heures d'Excel (nombres dont la partie entière représente la date et la partie décimale l'heure, et qui s'affichent sous forme littérale selon le format de cellule choisi).
Le calcul de durée doit se faire selon le caractère ouvré ou non des heures incluses dans la période entre le moment de début et le moment de fin indiqués. C'est en cela que réside la personnalisation de la fonction car on n'a pas à lui indiquer de détails concernant les horaires ouvrés en prendre en compte, on les lui a incorporés. Et comme l'on a inclus 3 régimes distincts de rotations horaires pour qu'elle puisse répondre en fonction du régime de rotation utilisé, on doit lui indiquer en troisième argument quelle rotation est à prendre en considération pour réaliser le calcul, selon une notation codée : "2_8", "3_8", "3_8we",qui soit sera indiquée sous cette forme directe dans la formule, soit pourra être renvoyée par une cellule contenant cette valeur...

Dernier point qui serait d'ailleurs commun avec une quelconque formulation classique : les cellules dans lesquelles on formule en utilisant cette fonction devront être mise au format : [h]:mm de façon à afficher la durée calculée en heures:minutes avec une valeur heures pouvant dépasser 24h.
M
MFerrand
Fanatique d'Excel
Fanatique d'Excel
Messages : 17'171
Appréciations reçues : 446
Inscrit le : 20 juillet 2015
Version d'Excel : 2010 FR

Message par MFerrand » 7 février 2018, 20:48

Sur ton exemple, c'est :
=DTTROT(A1;A2;"2_8")
edit: comme je l'avais indiqué auparavant, si tu souhaites remplacer l'indication de la rotation sous forme littérale par les nombres 1, 2 ou 3, l'adaptation est assez facile (dans la mesure où l'on convertit dans la fonction l'argument dans ces valeurs numériques...
Maintenant, l'indication littérale n'a pas à être décodée, tu n'auras pas de doute sur la rotation demandée, avec 1, 2, 3 il faut garder en mémoire à quoi correspond chacun...
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message