Fonction pour compter heures jour/nuit semaine, férié et dimanche

C

Christian0258

XLDnaute Accro
Bonjour à tout le forum,

Je souhaiterais votre aide afin d'écrire les fonctions pour un planning..
voir fichier joint, c'est plus simple car explications compliquées...

Je vous remercie pour le temps que vous voudrez bien m'accorder.

Bien à vous,
Christian.
 

Pièces jointes

  • PlanningPlusieursHorairesUneColonne.zip
    13.7 KB · Affichages: 109
Trier par date Trier par votes
job75

job75

XLDnaute Barbatruc
Re : Fonction pour compter heures jour/nuit semaine, férié et dimanche

Bonsoir Christian,

Voyez le fichier joint avec les 6 fonctions.

Il y a peu de différences entre elles, une fois construites les 2 premières les 4 autres coulent de source.

Bien noter que pour une date donnée les plages horaires sont toutes ventilées sur la même ligne, même s'il s'agit du lendemain.

A+
 

Pièces jointes

  • PlanningPlusieursHorairesUneColonne(1).xls
    85 KB · Affichages: 109
Dernière édition:
Vote positif 0 Vote négatif
C

Christian0258

XLDnaute Accro
Re : Fonction pour compter heures jour/nuit semaine, férié et dimanche

Bonjour à tout le forum, Job75,

Merci, Job75, pour ce beau boulot...comme d'hab.

Comme tu as pu le voir, je me suis inspiré de ton intervention sur des formules...il y a peu de temps sur ce forum pour construire ce planning.
J'ai bien noté que pour une date donnée les plages horaires sont toutes ventilées sur la même ligne, même s'il s'agit du lendemain et ce que je souhaitais.

Encore un grand merci à toi.

Bien à vous,
Christian
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Fonction pour compter heures jour/nuit semaine, férié et dimanche

Bonjour Christian, le forum,

On peut alléger le code avec cette macro paramétrée utilisée par les 6 fonctions :

Code: 
Sub Calcul(JN$, test1 As Boolean, test2 As Boolean, horaire, temp#)
Dim s, i%, s1, x#, h1#, h2#, deb#, fin#
horaire = Replace(horaire, "24H00", "00H00")
s = Split(horaire)
For i = 0 To UBound(s)
  s1 = Split(s(i), "-")
  If UBound(s1) = 1 Then
    x = CDate(Replace(s1(0), "H", ":"))
    If x < h2 Then x = x + 1
    h1 = x
    h2 = CDate(Replace(s1(1), "H", ":"))
    If h2 < h1 Then h2 = h2 + 1
    deb = [DN]
    fin = [FN] + 1
    If JN = "Jour" Then
      If test1 Then temp = temp + IIf(h2 > deb, deb, h2) - IIf(h1 > deb, deb, h1)
      If test2 Then temp = temp + IIf(h2 > fin, h2, fin) - IIf(h1 > fin, h1, fin)
    Else 'Nuit
      If test1 And h1 < 1 And h2 > deb Then temp = temp + IIf(h2 > 1, 1, h2) - IIf(h1 > deb, h1, deb)
      If test2 And h1 < fin And h2 > 1 Then temp = temp + IIf(h2 > fin, fin, h2) - IIf(h1 > 1, h1, 1)
    End If
  End If
Next
End Sub
Fichier (2).

J'ai testé : les durées d'exécution des fichiers (1) et (2) sont exactement les mêmes.

Donc autant utiliser cette version.

A+
 

Pièces jointes

  • PlanningPlusieursHorairesUneColonne(2).xls
    78.5 KB · Affichages: 81
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Fonction pour compter heures jour/nuit semaine, férié et dimanche

Re,

Ceci est mieux :

Code: 
Sub Calcul(JN$, test1 As Boolean, test2 As Boolean, horaire$, temp#)
If test1 + test2 = 0 Then Exit Sub
Dim s, i%, s1, h1#, h2#, deb#, fin#
horaire = Replace(Replace(horaire, "H", ":"), "24:", "0:")
s = Split(horaire)
For i = 0 To UBound(s)
  s1 = Split(s(i), "-")
  If UBound(s1) = 1 Then
    h1 = CDate(s1(0))
    If h1 < h2 Then h1 = h1 + 1
    h2 = CDate(s1(1))
    If h2 < h1 Then h2 = h2 + 1
    deb = [DN]
    fin = [FN] + 1
    If JN = "Jour" Then
      If test1 Then temp = temp + IIf(h2 > deb, deb, h2) - IIf(h1 > deb, deb, h1)
      If test2 Then temp = temp + IIf(h2 > fin, h2, fin) - IIf(h1 > fin, h1, fin)
    Else 'Nuit
      If test1 And h1 < 1 And h2 > deb Then temp = temp + IIf(h2 > 1, 1, h2) - IIf(h1 > deb, h1, deb)
      If test2 And h1 < fin And h2 > 1 Then temp = temp + IIf(h2 > fin, fin, h2) - IIf(h1 > 1, h1, 1)
    End If
  End If
Next
End Sub
Avec le test If test1 + test2 = 0 on évite le calcul des cellules "vides" (résultat nul).

L'exécution est nettement plus rapide.

Utilises donc cette version (3).

A+
 

Pièces jointes

  • PlanningPlusieursHorairesUneColonne(3).xls
    78 KB · Affichages: 93
Vote positif 0 Vote négatif
C

CHALET53

XLDnaute Barbatruc
Re : Fonction pour compter heures jour/nuit semaine, férié et dimanche

Bonjour,

J'interviens uniquement pour saluer le travail remarquable de JOB75
Si je peux me permettre une remarque : il y a un cas de figure qui ne fonctionne pas, c'est le 31/12 si l'activité se prolonge sur le 01/01 de l'année suivante (jour férié)

Il suffit de gérer les jours fériés sur 2 exercices (et modifier la plage fériés pour intégrer A+1) : je l'ai testé

a+
 
Vote positif 0 Vote négatif
C

CHALET53

XLDnaute Barbatruc
Re : Fonction pour compter heures jour/nuit semaine, férié et dimanche

Re,

Il me semble qu'il y ait une autre anomalie si l'heure de départ est avant 06H00
05H00-10H00 (un jour normal) donne 5 heures de jour alors qu'il y a une heure de nuit pour une tranche nuit : 21 H - 6 H

En espérant que Job75 repasse

a+
 
Vote positif 0 Vote négatif
C

Christian0258

XLDnaute Accro
Re : Fonction pour compter heures jour/nuit semaine, férié et dimanche

Re, le forum CHALET53, Job75,

Merci CHALET53 pour le contrôle...

J'ai élargi ma plage "férié" au 1er janvier année suivante et je suis comme toi, j'attends que le maître repasse...

Bien à vous.
Christian
 
Vote positif 0 Vote négatif
job75

job75

XLDnaute Barbatruc
Re : Fonction pour compter heures jour/nuit semaine, férié et dimanche

Bonsoir Christian, CHALET53,

Bien rentré à Paris.

Voici la macro corrigée pour tenir compte de la 1ère plage de nuit (00:00-06:00) :

Code: 
Sub Calcul(JN$, test1 As Boolean, test2 As Boolean, horaire$, temp#)
If test1 + test2 = 0 Then Exit Sub
Dim deb#, fin1#, fin#, s, i%, s1, h1#, h2#
deb = [DN]: fin1 = [FN]: fin = fin1 + 1
horaire = Replace(Replace(horaire, "H", ":"), "24:", "0:")
s = Split(horaire)
For i = 0 To UBound(s)
  s1 = Split(s(i), "-")
  If UBound(s1) = 1 Then
    h1 = CDate(s1(0))
    If h1 < h2 Then h1 = h1 + 1
    h2 = CDate(s1(1))
    If h2 < h1 Then h2 = h2 + 1
    If JN = "Jour" Then
      If test1 Then
        temp = temp - IIf(h2 > fin1, fin1, h2) + IIf(h1 > fin1, fin1, h1)
        temp = temp + IIf(h2 > deb, deb, h2) - IIf(h1 > deb, deb, h1)
      End If
      If test2 Then temp = temp + IIf(h2 > fin, h2, fin) - IIf(h1 > fin, h1, fin)
    Else 'Nuit
      If test1 Then
        temp = temp + IIf(h2 > fin1, fin1, h2) - IIf(h1 > fin1, fin1, h1)
        If h1 < 1 And h2 > deb Then temp = temp + IIf(h2 > 1, 1, h2) - IIf(h1 > deb, h1, deb)
      End If
      If test2 And h1 < fin And h2 > 1 Then temp = temp + IIf(h2 > fin, fin, h2) - IIf(h1 > 1, h1, 1)
    End If
  End If
Next
End Sub
Fichier (4), voyez les horaires en B11 et B12.

Edit : j'ai mis deb = [DN]: fin1 = [FN]: fin = fin1 + 1 tout au début...

A+
 

Pièces jointes

  • PlanningPlusieursHorairesUneColonne(4).xls
    81 KB · Affichages: 106
Dernière édition:
Vote positif 0 Vote négatif
L

lucmohamed

XLDnaute Nouveau
Re : Fonction pour compter heures jour/nuit semaine, férié et dimanche

Bonjour Chalet et job75,

permettez moi d'intervenir. J'avais le meme besoin de christian et votre fichier correspond à mes attentes.

Juste il y avait un point que j'en ai discuté avec CHALET qui correspondait aux heures travaillés de 06H00-06H00 cad 24h complète.

Chalet m'a proposé une solution qui est : If h2 <= h1 Then h2 = h2 + 1. Ca marché. Y a t-il autre chose à modifier M. le docteur job75??

il y avait un autre point que je voudrais savoir. Dans le code VBA, le weekday=1 correspond à dimanche. Comment faire pour que le premier jour de la semaine soit un samedi????

Merci. Bonne soirée.
 
Vote positif 0 Vote négatif
C

CHALET53

XLDnaute Barbatruc
Re : Fonction pour compter heures jour/nuit semaine, férié et dimanche

Re,

Codification des jours :
1 =Dimanche
2 =Lundi
3= Mardi
4= Mercredi
5= Jeudi
6 =Vendredi
7= Samedi

le tableau décompose les horaires entre Jours ouvrables, Jours fériés ou Dimanche. Donc, si j'ai bien compris :

pour un jour férié (Function Tot3 et Tot4 : contrôle à partir de la table en feuille Config (le jour est présent test=vrai)
Pour un dimanche (Function Tot5 et tot6 : Contrôle que la date n'est pas un jour férié (résultat test=Faux) et que le jour = 1
Pour un jour ouvrable (function Tot1 et Tot2 : Contrôle que la date n'est pas un jour férié (résultat test=faux) et que le jour > 1

Ce n'est donc pas lié au fait que la semaine commence un Samedi ou un Dimanche...ou un Lundi
Par contre si le samedi est géré comme un Dimanche pour le décompte des heures, il faut en tenir compte dans les tests liés aux différentes fonctions (Tot1 à tot6)
Je laisse Job75 répondre sur ce point

A+
 
Vote positif 0 Vote négatif

Discussions similaires

S
  • Question
Microsoft 365 Compteur heures travaillées jour et nuit
Réponses
2
Affichages
252
Sam7
S
G
  • Question
Microsoft 365 Calcul des heures normales et des heures supplémentaires, les heures de nuit étant modifiées en fonction du début de la journée de travail.
2 3 4
Réponses
57
Affichages
3 K
CISCO
CISCO
M
  • Résolu(e)
XL 2013 Calcul des heures travaillées de jour, de nuit et plus de 24h consécutives
2 3
Réponses
41
Affichages
1 K
Manutela
M
B
  • Question
XL 2016 Formule avec plusieurs conditions pour planning heures annuelles
Réponses
5
Affichages
393
fanfan38
fanfan38
B
  • Résolu(e)
XL 2016 jours fériés/dimanche/heures de nuit
2
Réponses
16
Affichages
1 K
baptistou
B
P
  • Résolu(e)
XL 2016 Planning...Total heures semaine
Réponses
10
Affichages
559
Hubert1959
H
N
  • Question
XL 2016 Calcul Heures
Réponses
6
Affichages
799
TooFatBoy
TooFatBoy
LB04
  • Question
XL 2019 compter les jour et demi-journee
Réponses
1
Affichages
679
fanch55
fanch55
L
  • Question
XL 2019 Comptabiliser les heures dimanches et jours fériés sans doublons
Réponses
2
Affichages
531
leo13
L
3
  • Question
XL 2013 condition en fonction d'un jour spécifique (dimanche et jour férié)
Réponses
6
Affichages
756
GALOUGALOU
GALOUGALOU

Membres actuellement en ligne

4 membres et 35 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 177
Messages
2 085 972
Membres
103 073
dernier inscrit
MSCHOE16