Calcul du temps travaillé en Minute excluant week end et jou

Bonjour,

Je suis nouveau sur ce foru. J'ai un cas tres complexe et j'ai besoin de votre expertise en excel pour m'aider à automatiser le temps travaillé en minute du departement TRANSIT

Heures d'affaires

lundi au Mercredi: 8AM - 6PM

Jeudi- Vendredi : 8AM - 8PM

Mon objectif est de calaculer les heurs travaillées.

lundi au Mercredi: 8AM - 6PM et non avant ou apres cette plage horaire

Jeudi- Vendredi : 8AM - 8PM et non avant ou apres cette plage horaire

Aussi ne pas tenir comptes des week end et des jours ferie.

Ci-joint un tableau indiquant les resultats qu'on devrait obtenir lorsqu'on calcul manuellement.

Merci pour votre aide

Bonjour,

Un 1er essai :

=NB.JOURS.OUVRES.INTL(C4;D4;"0001111")*600+NB.JOURS.OUVRES.INTL(C4;D4;"1110011")*720-(MAX(MIN(C4-ENT(C4);SI(MOD(JOURSEM(C4);7)>1;SI(MOD(JOURSEM(C4);7)<5;18/24;20/24);0));8/24)-8/24)*1440-(SI(MOD(JOURSEM(D4);7)>1;SI(MOD(JOURSEM(D4);7)<5;18/24;20/24);0)-MIN(MAX(D4-ENT(D4);8/24);SI(MOD(JOURSEM(D4);7)>1;SI(MOD(JOURSEM(D4);7)<5;18/24;20/24);0)))*1440

Je regarderai plus tard si on peut simplifier ça...

Je n'ai pas intégré les jours fériés, mais ils sont facilement intégrables dans la fonction NB.JOURS.OUVRES.INTL qui admet en dernier argument optionnel une plage dans laquelle sont consignés les jours fériés à exclure.

Par contre, si l'un des jours bornes est férié, l'inclusion d'un test dans les expressions apportant les correctifs horaires sur les jours bornes allongera encore sensiblement la formule...

Cordialement.

Ferrand

Bonjour Ferrand,

Je voudrais tout abord te remercier pour la prise en charge de ma demande. Aussi merci pour ton temps et cet impressionnant travail.

Je n'ai pas pu tester la formule avec differents. Quand je modifie une date j'ai l'erreur #NAME? et quand je remets la date qui y etait j'ai toujours #NAME?

J'ai Excel 2007.

https://www.cjoint.com/c/FAootX0y0JG

S'il te plait pourrais-tu intégrer les jours fériés dans le calcul. Ci-dessous le Calendrier jour férié 2016

vendredi 1er janvier (Jour de l'An)

vendredi 25 mars (Vendredi saint) ou lundi 28 mars (lundi de Pâques), au choix de l'employeur

lundi 23 mai (Journée nationale des patriotes)

vendredi 24 juin (Fête nationale du Québec)

vendredi 1er juillet (Fête du Canada)

lundi 5 septembre (Fête du travail)

lundi 10 octobre (Action de grâce)

dimanche 25 décembre (Noël)

26 Décembre (Lendemain de Noël)

Effectivement un des jours bornes peut être férié. Ce cas est rare mais ca pourrait arriver. Est ce possible d'en tenir compte dans le calcul STP?

Cordialement.

Merci infiniment pour ton temps et ton aide.

C'était journée dentiste... désolé ! Voilà avec intégration fériés (à tester).

=NB.JOURS.OUVRES.INTL(C4;D4;"0001111";Fériés)*600+NB.JOURS.OUVRES.INTL(C4;D4;"1110011";Fériés)*720-(SI(NB.SI(Fériés;ENT(C4))>0;MAX(MIN(C4-ENT(C4);SI(MOD(JOURSEM(C4);7)>1;SI(MOD(JOURSEM(C4);7)<5;18/24;20/24);0));8/24)-8/24;0))*1440-(SI(NB.SI(Fériés;ENT(D4))>0;SI(MOD(JOURSEM(D4);7)>1;SI(MOD(JOURSEM(D4);7)<5;18/24;20/24);0)-MIN(MAX(D4-ENT(D4);8/24);SI(MOD(JOURSEM(D4);7)>1;SI(MOD(JOURSEM(D4);7)<5;18/24;20/24);0));0))*1440

Comme je l'ai dit, il serait bon de trouver un moyen de simplifier cette formule qui commence à être un peu longue à mon goût...

Cordialement.

Bonjour Mferrand,

J'espère tout s'est bien passé chez le dentiste!!!!

Merci beaucoup pour ton aide!!

Pour le dernier fichier je crois que l'ajout des jours fériés à quelques peu modifier les resultats.

Normalement le Calcul automatisé et le Calcul manuel devraient avoir les mêmes valeurs.

A titre d'exemple les dates juil. 3, 2015 5:08 AM à juil. 3, 2015 7:48 AM on a 720 pour Calcul automatisé et 0 pour Calcul manuel. Normalement on devrait avoir 0. Vu qu'on a aucun jour ferié.

Le premier fichier est bon car le Calcul automatisé et le Calcul manuel donnent les mêmes resultats.

seulement quand je veux apporter des modifications dans les dates pour tester avec differents cas de figure j'ai l'erreur #NAME?.

Par exemple au lieu de juil. 3, 2015 5:08 AM je mets juil. 2, 2015 5:08 AM j'ai l'erreur #NAME? dans Calcul automatisé.

ci-dessous le premier fichier avec l'erreur #NAME?

https://www.cjoint.com/c/FAootX0y0JG

Merci beaucoup pour ton temps.

Excellent week end!!

Tu as dû te tromper de fichier. C'est le premier et sans erreur.

J'ai par contre des erreurs de résultats (puisqu'il n'y a pas de jours fériés dans les dates) dans celui que j'ai ici.

J'ai dû me tromper quelque part en modifiant. Je vais regarder (un peu plus tard) et je reviens.

Je suis confus ! J'avais simplement inversé les termes du test. Il suffit donc de remplacer >0 par =0 dans les tests (fériés) sur les correctifs. Là cela annulait tous les correctifs, les résultats ne restaient donc bons que si les correctifs étaient nuls.

Il vaut mieux que je ne poursuive pas trop ce soir...

Bonjour MFerrand,

Après investigation, je remarque que cette fonction Networkdays.Intl est nouvelle dans Excel 2010 et donc n'est pas disponibles dans les versions antérieures d'Excel. Cependant, il est similaire à la fonction Networkdays, qui est disponible dans les versions antérieures d'Excel.

Vu que j'ai Excel 2007, ce qui explique que j'ai l'erreur #NAME?.

Quand j'ouvre ton fichier tout est bien avec les resultats mais il suffit de modifier une date pour tester et j'ai l'erreur #NAME?

J'ai essayé de remplacer la formule Networkdays.Intl par Networkdays mais je n'ai pas les même resultats.

Ceci m'empeche de tester avec differents cas de figures la formule.

J'avoue que c'est très impressionnant ce travail que tu as effectué et je t'en remercie.

C'est vrai tu en a fais beaucoup aujourd'hui et j'en suis reconnaissant.

Bon repos et excellent week end!

Merci beaucoup!!

Oui, la fonction antérieure n'offrait pas la possibilité d'utilisation pour sérier les configurations de jours que tu utilises.

Je regarderai si on peut trouver une solution pas trop compliquée.

A+

Merci beaucoup!! j'attendrai ton retour pour mieux tester la formule. Merci !!

A+

Bonsoir,

La solution la plus économique me paraît être une fonction personnalisée...

Je n'étais pas bien réveillé en l'écrivant et j'ai eu quelques doutes sur le chemin pris par mon raisonnement, mais il semble qu'elle donne bien le résultat attendu. A toi de poursuivre la vérification.

Function TTM(dhd, dhf, fe As Range)
    'constantes heures de début de travail minimum (lu à ve)
    'et de fin de travail maximum (1-lu à me, 2-je et ve)
    Const hmin = 8 / 24, hmax1 = 18 / 24, hmax2 = 20 / 24
    Dim d, dd, df, hd, hf, tdd%, tdf%, tdi%, c As Range, nf As Boolean
    Application.Volatile
    If dhf < dhd Then
        TTM = CVErr(xlErrValue)
        Exit Function
    End If
    'calculs préliminaires
    dd = Int(dhd): df = Int(dhf)
    hd = dhd - dd: hf = dhf - df
    For Each c In fe
        If dd = c.Value Then hd = hmax2
        If df = c.Value Then hf = hmin
    Next c
    Select Case Weekday(dd)
        Case 2 To 4
            If hd > hmax1 Then hd = hmax1
        Case 5, 6
            If hd > hmax2 Then hd = hmax2
        Case Else
            hd = hmax2
    End Select
    If hd < hmin Then hd = hmin
    Select Case Weekday(df)
        Case 2 To 4
            If hf > hmax1 Then hf = hmax1
        Case 5, 6
            If hf > hmax2 Then hf = hmax2
        Case Else
            hf = hmin
    End Select
    If hf < hmin Then hf = hmin
    'calculs temps jours bornes
    If df = dd Then
        tdd = (hf - hd) * 1440
    Else
        Select Case Weekday(dd)
            Case 2 To 4
                tdd = (hmax1 - hd) * 1440
            Case Else
                tdd = (hmax2 - hd) * 1440
        End Select
        tdf = (hf - hmin) * 1440
    End If
    'calculs temps jours intermédiaires (s'il y a lieu)
    If df - dd > 1 Then
        d = dd + 1
        Do
            nf = True
            For Each c In fe
                If d = c.Value Then
                    nf = False: Exit For
                End If
            Next c
            If nf Then
                Select Case Weekday(d)
                    Case 2 To 4
                        tdi = tdi + (hmax1 - hmin) * 1440
                    Case 5, 6
                        tdi = tdi + (hmax2 - hmin) * 1440
                End Select
            End If
            d = d + 1
        Loop While d < df
    End If
    TTM = tdd + tdf + tdi
End Function

Cordialement.

Ferrand

Bonjour Ferrand,

Merci beaucoup pour cette solution economique!!

Je vais poursuivre les vérifications puis te revenir pour confirmation.

Merci beaucoup

Très bonne fin de semaine!!

Bonjour Ferrand,

Tu as fais un travail extraordinaire!!

Effectivement ca semble bien fonctionner! je vais poursuiivre mes test en semaine avec d'autres cas et te revenir.

Pour les jours fériés j'ai essayé de créer une feuille nommée Fériés mais ca ne fonctionne pas. Est ce possible de faire ca: Mettre les jours fériés dans la feuille nommée Fériés pour pas l'avoir dans la feuille calcul STP.

Merci beaucoup pour cette solution economique!!

Merci beaucoup

J'espère que tu as passé un excellent week end!!

Bonne semaine!!

Oui bien sûr. Tu mets la liste des fériés où tu veux : en nommant la plage, tu n'as que le nom à utiliser dans les formules, Excel la trouvera...

Bonne semaine.

Cordialement.

Ferrand

Bonjour Ferrand,

Merci pour ta disponibilité et ton aide!!

Je remarque que quand les cellules Date Début et Date Fin sont vide, J'ai la valeur -720 au lien de 0 ou à defaut une case vide.

J'ai suivi tes indications pour les jours fériés et ca fonctionne. Merci Beaucoup!

Bonne semaine! et Merci pour ton aide!!

Bonjour,

Excellent travail de MFerrand Je me la garde au chaud cette fonction perso

Juste en passant pour éviter l'affichage -720 tu peux ajouter le test dans le code

avant la ligne 'calculs préliminaires

    If dhf = "" And dhd = "" Then
        TTM = 0
        Exit Function
    End If

Leakim

Bonjour,

Il me semblait bien que mon attention n'était pas au niveau requis... ! Et pas testé ce cas, même par inadvertance.

Le problème survient parce que 0 est une date (samedi 30 décembre 1899 pour VBA, [Excel l'afficherait 0 janvier 1900]).

La solution de Leakim ( ) règlerait le problème lorsque les cellules sont vides ou à 0. Mais il se produira également si le même jour de début et de fin tombe un samedi, un dimanche ou un férié.

L'"astuce" consistant à aligner l'heure de début sur l'heure max et l'heure de fin sur l'heure min (qui réduit le nombre de cas à distinguer par la suite) est contradictoire avec le fait de distinguer le cas où début et fin sont le même jour pour ne calculer que sur le jour de début : l'annulation qui devrait résulter de l'"astuce" précédente ne se produit alors pas et le résultat est systématiquement -720 erroné. Il convient donc d'éliminer ce cas pour que l'annulation intervienne.

'calculs temps jours bornes
    Select Case Weekday(dd)
        Case 2 To 4
            tdd = (hmax1 - hd) * 1440
        Case Else
            tdd = (hmax2 - hd) * 1440
    End Select
    tdf = (hf - hmin) * 1440

Réduire le code du calcul pour les jours bornes à ce qui figure ci-dessus.

Avec mes excuses

A+

Bonjour Ferrand,

Je voudrais une fois encore te remercier et aussi dire merci a Leakim pour sa contribution.

Je vais tester et te revenir. Normalement d'ici la fin de la semaine je devrais mettre à résolu

Je continu mes tests entre temps.

Merci beaucoup pour tes efforts.

Tres bonne semaine!

Bonjour Ferrand,

J'ai une question STP!

Est ce qu'il est possible de protéger la plage E4:E11 du resultat du Calcul automatisé. Que se soit en lecture seul pour éviter que quelqu'un efface la formule ou le resultat par erreur

Merci beaucoup!

Pas de problème pour protéger la feuille de la façon habituelle...

A+