XL 2016 jours fériés/dimanche/heures de nuit

[baptistou]

Bonjour à tous et merci pour ce forum riche en données et exemples.
J'ai récupéré d'un collègue un fichier Excel pour établir un planning en calculant les coûts etc... merci à lui.
Je l'ai modifié pour essayer de l'adapter au mieux à mes besoins, mais je sèche sur des formules pour calculer heures et coûts quand un jour férié tombe aussi un dimanche et pour éviter que ne soit compté le dimanche mais aussi le jour férié....idem pour les nuits. je pense que la formule est avec ET mais je bute....
un exemple étant plus parlant, je vous joins le fichier.
Merci pour votre aide,
B.

 

[Bernard_XLD]

Bonjour baptistou, le forum

Qu'appelez vous heures de nuit, sont elles fixes ou variables, créneau de quelle heure à quelle heure ?
Vos nuits sont rattachées au jour précédent ou rattachées au jour suivant comme un super matinal ?
Solution sans vba ou vba possible ? (pour une formule personnalisée pour la détermination des jours fériés)

Bien cordialement, @+

 

[baptistou]

Bonjour Bernard, Merci pour ces demandes de précisions :
- les heures de nuits sont fixes de 21h à 6h. Pour leurs rattachements, c'est au jour donné, par exemple, si dimanche 1/05, les heures de 00h à 6h sont des dimanches féries ainsi que celles de 21h à 00h. Celles du 00h à 6h du 2/05 sont normales.
Pour le VBA, je n'ai pas de préférences, pour moi le plus simple reste le mieux.
Encore merci

 

[Bernard_XLD]

Re,

Voila un premier jet !
J'ai corrigé et revu les formules, les MFC, intégré les jours fériés automatiques et supprimé le tableau correspondant à tenir à jour, dissocié les dimanches et fériés, géré les mois en automatique.
Les dimanches sont en typo rouge, les fériés sont en fond jaune.
Merci de me confirmer que c'est ok pour cette première phase.
Je vais regarder pour le traitement des nuits, à cheval sur deux jours, pour les primes de nuit, de dimanche et jour férié en ou hors nuit, cela va être complexe, un dimanche et un jour férié pouvant se suivre avec une nuit à cheval, il faut gérer tous les cas de figure possibles, pas le temps de faire ce matin, j'essaierai de boucler cet après midi ou ce soir. Je pense que je vais coder une fonction personnalisée.

Cordialement, @+

 

[baptistou]

Bonjour Bernard, merci infiniment pour ce travail et le temps passé pour me solutionner le tableur.
C'est vraiment super et déjà pour moi une grande avancée !!!!
Merci pour le partage des connaissances, je regarde cela de plus près pour comprendre les formules.

 

[Bernard_XLD]

Re,

Voila le fichier, j'ai programmé une fonction personnalisée qui renvoie le nombre d'heures pour les huit possibilités avec la gestion des nuits à cheval sur deux jours.
Jour standard, nuit sur jour standard, dimanche férié, nuit sur dimanche férié, dimanche, nuit sur dimanche, jour férié, nuit sur jour férié, idem sur le jour suivant.
J'ai utilisé ma fonction TJF pour les déterminations de jours fériés.

Bons tests !

Bien cordialement, @+

Function Determine_Heures(ByVal Jour_Ref, ByVal Heure_Deb, ByVal Heure_Fin, ByVal Heure_Deb_Nuit_Ref#, ByVal Heure_Fin_Nuit_Ref#, ByVal Heure_Deb_Nuit_Dim_Ref#, ByVal Heure_Fin_Nuit_Dim_Ref#, ByVal Type_Retour%)
If Heure_Deb = "" Or Heure_Fin = "" Then Determine_Heures = "": Exit Function
Dim Heure_DebJ1#, Heure_FinJ1#, Heure_FinJ2#
Dim Heures_Jour_Std1#, Heures_Jour_Std2#, Heures_Nuit_Std1#, Heures_Nuit_Std2#
Dim Heures_Jour_Dim1#, Heures_Jour_Dim2#, Heures_Nuit_Dim1#, Heures_Nuit_Dim2#
Dim Heures_Jour_JF1#, Heures_Jour_JF2#, Heures_Nuit_JF1#, Heures_Nuit_JF2#
Dim Heures_Jour_DF1#, Heures_Jour_DF2#, Heures_Nuit_DF1#, Heures_Nuit_DF2#
Heure_DebJ1 = Heure_Deb
Heure_FinJ1 = Heure_Fin
If Heure_Fin < Heure_Deb Then
    Heure_FinJ1 = 1
    Heure_FinJ2 = Heure_Fin
End If
If Weekday(Jour_Ref, 2) = 7 Or TJF(Jour_Ref) Then
    If Weekday(Jour_Ref, 2) = 7 And TJF(Jour_Ref) Then
        If Heure_DebJ1 < Heure_Fin_Nuit_Dim_Ref Then
            Heures_Nuit_DF1 = Heure_Fin_Nuit_Dim_Ref - Heure_DebJ1
            Heure_DebJ1 = Heure_Fin_Nuit_Dim_Ref
        End If
        If Heure_FinJ1 > Heure_Deb_Nuit_Dim_Ref Then
            Heures_Nuit_DF1 = IIf(Heure_Deb_Nuit_Dim_Ref > Heure_DebJ1, Heures_Nuit_DF1 + (Heure_FinJ1 - Heure_Deb_Nuit_Dim_Ref), Heures_Nuit_DF1 + (Heure_FinJ1 - Heure_DebJ1))
            Heure_FinJ1 = IIf(Heure_Deb_Nuit_Dim_Ref > Heure_DebJ1, Heure_Deb_Nuit_Dim_Ref, Heure_DebJ1)
        End If
        Heures_Jour_DF1 = Heure_FinJ1 - Heure_DebJ1
    Else
        If Weekday(Jour_Ref, 2) = 7 Then
            If Heure_DebJ1 < Heure_Fin_Nuit_Dim_Ref Then
                Heures_Nuit_Dim1 = Heure_Fin_Nuit_Dim_Ref - Heure_DebJ1
                Heure_DebJ1 = Heure_Fin_Nuit_Dim_Ref
            End If
            If Heure_FinJ1 > Heure_Deb_Nuit_Dim_Ref Then
                Heures_Nuit_Dim1 = IIf(Heure_Deb_Nuit_Dim_Ref > Heure_DebJ1, Heures_Nuit_Dim1 + (Heure_FinJ1 - Heure_Deb_Nuit_Dim_Ref), Heures_Nuit_Dim1 + (Heure_FinJ1 - Heure_DebJ1))
                Heure_FinJ1 = IIf(Heure_Deb_Nuit_Dim_Ref > Heure_DebJ1, Heure_Deb_Nuit_Dim_Ref, Heure_DebJ1)
            End If
            Heures_Jour_Dim1 = Heure_FinJ1 - Heure_DebJ1
        Else
            If Heure_DebJ1 < Heure_Fin_Nuit_Dim_Ref Then
                Heures_Nuit_JF1 = Heure_Fin_Nuit_Dim_Ref - Heure_DebJ1
                Heure_DebJ1 = Heure_Fin_Nuit_Dim_Ref
            End If
            If Heure_FinJ1 > Heure_Deb_Nuit_Dim_Ref Then
                Heures_Nuit_JF1 = IIf(Heure_Deb_Nuit_Dim_Ref > Heure_DebJ1, Heures_Nuit_JF1 + (Heure_FinJ1 - Heure_Deb_Nuit_Dim_Ref), Heures_Nuit_JF1 + (Heure_FinJ1 - Heure_DebJ1))
                Heure_FinJ1 = IIf(Heure_Deb_Nuit_Dim_Ref > Heure_DebJ1, Heure_Deb_Nuit_Dim_Ref, Heure_DebJ1)
            End If
            Heures_Jour_JF1 = Heure_FinJ1 - Heure_DebJ1
        End If
    End If
Else
    If Heure_DebJ1 < Heure_Fin_Nuit_Ref Then
        Heures_Nuit_Std1 = Heure_Fin_Nuit_Ref - Heure_DebJ1
        Heure_DebJ1 = Heure_Fin_Nuit_Ref
    End If
    If Heure_FinJ1 > Heure_Deb_Nuit_Ref Then
        Heures_Nuit_Std1 = IIf(Heure_Deb_Nuit_Ref > Heure_DebJ1, Heures_Nuit_Std1 + (Heure_FinJ1 - Heure_Deb_Nuit_Ref), Heures_Nuit_Std1 + (Heure_FinJ1 - Heure_DebJ1))
        Heure_FinJ1 = IIf(Heure_Deb_Nuit_Ref > Heure_DebJ1, Heure_Deb_Nuit_Ref, Heure_DebJ1)
    End If
    Heures_Jour_Std1 = Heure_FinJ1 - Heure_DebJ1
End If
If Heure_FinJ2 > 0 Then
    If Weekday(Jour_Ref + 1, 2) = 7 Or TJF(Jour_Ref + 1) Then
        If Weekday(Jour_Ref + 1, 2) = 7 And TJF(Jour_Ref + 1) Then
            If Heure_FinJ2 < Heure_Fin_Nuit_Dim_Ref Then
                Heures_Nuit_DF2 = Heure_FinJ2
            Else
                Heures_Nuit_DF2 = Heure_Fin_Nuit_Dim_Ref
                Heures_Jour_DF2 = Heure_FinJ2 - Heure_Fin_Nuit_Dim_Ref
            End If
        Else
            If Weekday(Jour_Ref + 1, 2) = 7 Then
                If Heure_FinJ2 < Heure_Fin_Nuit_Dim_Ref Then
                    Heures_Nuit_Dim2 = Heure_FinJ2
                Else
                    Heures_Nuit_Dim2 = Heure_Fin_Nuit_Dim_Ref
                    Heures_Jour_Dim2 = Heure_FinJ2 - Heure_Fin_Nuit_Dim_Ref
                End If
            Else
                If Heure_FinJ2 < Heure_Fin_Nuit_Dim_Ref Then
                    Heures_Nuit_JF2 = Heure_FinJ2
                Else
                    Heures_Nuit_JF2 = Heure_Fin_Nuit_Dim_Ref
                    Heures_Jour_JF2 = Heure_FinJ2 - Heure_Fin_Nuit_Dim_Ref
                End If
            End If
        End If
    Else
        If Heure_FinJ2 < Heure_Fin_Nuit_Ref Then
            Heures_Nuit_Std2 = Heure_FinJ2
        Else
            Heures_Nuit_Std2 = Heure_Fin_Nuit_Ref
            Heures_Jour_Std2 = Heure_FinJ2 - Heure_Fin_Nuit_Ref
        End If
    End If
End If
Select Case Type_Retour
    Case 1
        Determine_Heures = IIf(Heures_Jour_Std1 + Heures_Jour_Std2 = 0, "", Heures_Jour_Std1 + Heures_Jour_Std2)
    Case 2
        Determine_Heures = IIf(Heures_Nuit_Std1 + Heures_Nuit_Std2 = 0, "", Heures_Nuit_Std1 + Heures_Nuit_Std2)
    Case 3
        Determine_Heures = IIf(Heures_Jour_Dim1 + Heures_Jour_Dim2 = 0, "", Heures_Jour_Dim1 + Heures_Jour_Dim2)
    Case 4
        Determine_Heures = IIf(Heures_Nuit_Dim1 + Heures_Nuit_Dim2 = 0, "", Heures_Nuit_Dim1 + Heures_Nuit_Dim2)
    Case 5
        Determine_Heures = IIf(Heures_Jour_JF1 + Heures_Jour_JF2 = 0, "", Heures_Jour_JF1 + Heures_Jour_JF2)
    Case 6
        Determine_Heures = IIf(Heures_Nuit_JF1 + Heures_Nuit_JF2 = 0, "", Heures_Nuit_JF1 + Heures_Nuit_JF2)
    Case 7
        Determine_Heures = IIf(Heures_Jour_DF1 + Heures_Jour_DF2 = 0, "", Heures_Jour_DF1 + Heures_Jour_DF2)
    Case 8
        Determine_Heures = IIf(Heures_Nuit_DF1 + Heures_Nuit_DF2 = 0, "", Heures_Nuit_DF1 + Heures_Nuit_DF2)
End Select
End Function

 

[baptistou]

Bonsoir Bernard,
Un grand merci, impressionnant la maitrise du logiciel !!!! Et merci de partager sur le forum.
Je m'empresse de regarder mais c'est super.
Bonne soirée

 

[baptistou]

Bonjour @Bernard_XLD, encore merci pour ton aide, je me sers toujours du fichier pour planifier et calculer le coût de mon service incendie. C'est top. Petit Hic, nous venons de migrer vers Excel 365 et là le code ne fonctionne plus. Ne connaissant pas grand chose en VBA et comme tes 2 fonctions sont justes parfaites, y a t'il une modification à effectuer ? En te remerciant grandement pour ton aide.

 

[Bernard_XLD]

nous venons de migrer vers Excel 365 et là le code ne fonctionne plus.

Bonjour

très bizarre, cela a été développé sur 365, je viens de le retester, ça fonctionne toujours bien.
par contre le fichier du post 6 avait un problème, je l'ai remis à jour. Si vous aviez repris le fichier du post 6, ça peut expliquer.

refaites un essai et tenez moi au courant

Cordialement,
Bernard_XLD

 

[baptistou]

Bonjour @Bernard_XLD, merci , le problème venait effectivement du fichier du post 6 Encore merci

 

[baptistou]

Bonjour @Bernard_XLD, j'ai à nouveau un souci avec le tableur. Je ne sais pas si cela est lié à une mise à jour, mais le résultat me sort #NOM? ou #VALEUR! sans avoir rien modifié. J'ai repris le tableur du post 6 et j'ai ces erreurs. As-tu une idée de pourquoi ces erreurs ? En te remerciant de prendre ce temps pour des novices. Merci

 

[Bernard_XLD]

Bonjour

#NOM sans rien modifier veut dire que la fonction n'est pas trouvée, très certainement parce que les macros sont désactivées, il est impératif de les activer pour qu'une fonction personnalisée soit disponible (c'est du VBA)

Cordialement, @+

 

[baptistou]

merci @Bernard_XLD, d'aider les petits novices. Je vais vérifier cela.
Je viens de vérifier, les macros sont autorisées ( le VBA aussi), le fichier se trouve dans un emplacement approuvé. Pour autant pas de calculs, cela me retourne maintenant ; FAUX quand je rentre une plage horaire et #valeur! dans certaines cellules sans raison.... je ne comprends pas pourquoi, n'ayant rien touché au fichier de départ.

 

[baptistou]

... recherche défaut suite... en reprenant directement le fichier sur mon onedrive sans passer par les propositions d'excel à l'ouverture du logiciel cela fonctionne. Par contre dès que je fais un copier/coller, excel s'arrête. et me met aussi comme message que les calculs sont incomplets et me demande si je veux continuer. Je précise que ces 2 erreurs ne sont pas nouvelles, mais de plus en plus récurrentes. Rencontrez vous aussi ce type de soucis ?

 

[baptistou]

bonsoir @Bernard_XLD le souci ou bug avec la disparition des macros est à nouveau présent et sans rien changé depuis ces derniers jours. Vraiment bizarre, samedi j'ai retravaillé dessus, les calculs se faisaient et ce matin à nouveau plus rien.