Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Excel Discussion :

Semaine d'astreinte + horaires de nuit


Sujet :

Excel

  1. #1
    Nouveau Candidat au Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    novembre 2019
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux

    Informations forums :
    Inscription : novembre 2019
    Messages : 5
    Points : 1
    Points
    1
    Par défaut Semaine d'astreinte + horaires de nuit
    Bonjour à tous,

    Je souhaiterais arriver à faire un fichier Excel qui me permettrait de calculer mes primes d'astreinte et mes horaires supplémentaires.

    1) Dans un 1er temps, arriver à calculer ma prime d'astreinte.
    En entrée j'ai 2 dates au format jj/mm/aaaa correspondant au début et à la fin de ma période d'astreinte.
    Un lundi/mardi/mercredi/jeudi/vendredi est rémunéré 45€, un samedi 48€, un dimanche 91€, mais un weekend complet (samedi + dimanche) 120€.
    Mais si pendant ma période d'astreinte, j'ai un jour férié, la prime est de 91€ quel que soit le jour de la semaine.

    Pourriez vous à m'aider à calculer ma prise d'astreinte ?

    2) Dans un second temps, je souhaiterais calculer mon temps d'intervention global et mon temps d'intervention de nuit.
    En entrée, j'ai 2 dates et horaires au format jj/mm/aaa hh:mm correspondant au début et à la fin de mon intervention.
    Dans le fichier joint, j'arrive bien à calculer le temps global de mon intervention (colonne H) et à la transformer en décimale (Colonne I).
    Mon 1er problème est le suivant. Je n'arrive pas systématiquement à arrondir ma colonne I à la demie heure supérieure dans la colonne J (2,75 en I -> 3 en colonne J; 8,37 -> 8,50; 1,3 -> 1,50).

    Ensuite, je souhaiterais calculer mes horaires de nuit qui sont compris entre 22h et 6h en K, puis en décimale en L et pareil, arrondir à la demie heure supérieure en M.

    Vous le verrez, j'arrive pas trop mal à gérer ma 1ère intervention en ligne 4, mais pas sur les lignes suivantes.

    Merci d'avance pour votre aide !!!

    Cordialement,
    Korbandia
    Fichiers attachés Fichiers attachés

  2. #2
    Membre expérimenté
    Homme Profil pro
    retraité
    Inscrit en
    juin 2012
    Messages
    1 026
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : retraité
    Secteur : Associations - ONG

    Informations forums :
    Inscription : juin 2012
    Messages : 1 026
    Points : 1 670
    Points
    1 670
    Par défaut
    Bonjour,

    Personnellement dans des cas comme le vôtre où il y a une série de conditions à vérifier, je préfère utiliser des fonctions personnalisées qui finalement m'apparaissent plus claires que des suites de formules. Mais bon chacun ses préférences. Une façon de faire avec des fonctions personnalisées:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    71
    72
    73
    74
    75
    76
    77
    78
    79
    80
    81
    82
    83
    84
    85
    86
    87
    88
    89
    90
    91
    92
    93
    94
    95
    96
    97
    98
    99
    100
    101
    102
    103
    104
    105
    106
    107
    108
    109
    110
    111
    112
    Option Explicit
    
    Public Function Astr(d1 As Date, d2 As Date)
       Dim v As Single, d As Date, j As Integer, samedi As Boolean, rT As Range
       Dim dJF As Date, rJF As Range
       v = 0
       samedi = False
       Set rT = Range("Tarif")          '--- tarifs --- attention, ne pas changer l'ordre !
       If Hour(d2) = 0 Then d2 = d2 - 1 '--- fin à minuit = fin à la date précédente
       '--- astreinte
       For d = d1 To d2                 '--- parcourir les dates
          j = Weekday(d, vbMonday)      '--- lundi = 1
          If j < 6 Then
             v = v + rT.Cells(1, 1)     '--- prime Lu-Ven
          ElseIf j = 6 Then
             v = v + rT.Cells(2, 1)     '--- prime Samedi
             samedi = True
          Else
             If samedi Then             '--- déjà en astreinte le samedi
                v = v - rT.Cells(2, 1)  '--- retire la prime du samedi
                v = v + rT.Cells(4, 1)  '--- ajoute la prime weekend
                samedi = False          '--- pour éviter double comptage sur dimanche à dimanche
             Else                       '--- pas en astreinte le samedi
                v = v + rT.Cells(3, 1)  '--- ajout prime dimanche seul
             End If
          End If
       Next d
       '--- jours fériés
       With Range("Tableau_JF")
          For j = 1 To .Count
             dJF = .Cells(j, 1)
             If dJF >= d1 And dJF <= d2 Then
                v = v + 96              '--- majoration jour férié
             End If
          Next j
       End With
       Astr = v
    End Function
    
    Public Function Interv(d1 As Date, d2 As Date)
       Dim nbH As Double, nbHJ As Double, nbHN As Double
       Dim d1hN As Date, d1hJ As Date, d2hN As Date, d2hJ As Date
       '---
       If d2 < d1 Then
          Interv = "d2 < d1 !"    '--- non admis
          Exit Function
       End If
       nbH = d2 - d1
       If nbH > 1 Then
          Interv = "> 24h !"      '--- non admis --- entrer 2 interventions
          Exit Function
       End If
       nbHJ = 0
       nbHN = 0
       d1hJ = Int(d1) + Range("Tarif").Cells(9, 1)      '--- date1-heure début jour
       d1hN = Int(d1) + Range("Tarif").Cells(8, 1)      '--- date1-heure début nuit
       d2hJ = Int(d2) + Range("Tarif").Cells(9, 1)      '--- date2-heure début jour
       d2hN = Int(d2) + Range("Tarif").Cells(8, 1)      '--- date2-heure début nuit
       'Debug.Print d1hJ, d1hN, d2hJ, d2hN
       '---
       If d1 < d1hJ Then
          '--- commence entre minuit et 6h du jour 1
          If d2 < d1hJ Then
             nbHN = d2 - d1
             d1 = d2
          Else
             nbHN = nbHN + d1hJ - d1
             d1 = d1hJ
          End If
       End If
       If d1 < d1hN Then
          '--- commence entre 6h et 22h du jour 1
          If d2 < d1hN Then
             nbHJ = nbHJ + d2 - d1
             d1 = d2
          Else
             nbHJ = d1hN - d1
             d1 = d1hN
          End If
       End If
       If d2 <= Int(d1) + 1 Then
          '--- intervention se termine à minuit ou avant
          nbHN = nbHN + d2 - d1
       Else
          '--- intervention sur 2 jours (dates) consécutifs
          If d1 < d2hJ Then
             '--- commence entre 22h et 6h du jour 2
             If d2 < d2hJ Then
                nbHN = nbHN + d2 - d1
                d1 = d2
             Else
                nbHN = nbHN + d2hJ - d1
                d1 = d2hJ
             End If
          End If
          If d1 < d2hN Then
             '--- commence entre 6h et 22h du jour 2
             If d2 < d2hN Then
                nbHJ = nbHJ + d2 - d1
                d1 = d2
             Else
                nbHJ = nbHJ + d2hN - d1
                d1 = d2hJ
             End If
          End If
       End If
       'Debug.Print "Jour: "; nbHJ * 24, "Nuit: "; nbHN * 24
       nbHJ = Hour(nbHJ) + IIf(Minute(nbHJ) > 0, 0.5, 0) + IIf(Minute(nbHJ) > 30, 0.5, 0)
       nbHN = Hour(nbHN) + IIf(Minute(nbHN) > 0, 0.5, 0) + IIf(Minute(nbHN) > 30, 0.5, 0)
       'Debug.Print "Jour: "; nbHJ, "Nuit: "; nbHN
       Interv = (nbHJ + nbHN * (1 + Range("Tarif").Cells(7, 1))) * Range("Tarif").Cells(6, 1)
    End Function
    Le code suppose qu'il y a 2 tableaux définis, l'un avec les jours fériés, l'autre avec les tarifs. Comme toujours, bien vérifier!
    Bonne continuation.
    Fichiers attachés Fichiers attachés

  3. #3
    Nouveau Candidat au Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    novembre 2019
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux

    Informations forums :
    Inscription : novembre 2019
    Messages : 5
    Points : 1
    Points
    1
    Par défaut
    Salut,

    Merci beaucoup EricDgn !
    Tu as dû y passer pas mal de temps.
    Je n'avais pas du tout conscience de la complexité de ma demande. Je me disais qu'en jonglant avec 2-3 formules on y serait arrivé.
    En tout cas, merci, je vais tester et je te fais un retour.

    Bonne journée !!

  4. #4
    Nouveau Candidat au Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    novembre 2019
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux

    Informations forums :
    Inscription : novembre 2019
    Messages : 5
    Points : 1
    Points
    1
    Par défaut
    EricDgn,

    Je viens de parcourir le fichier que tu as attaché.
    J'ai quelques remarques :
    - Je me suis peut être mal exprimé, mais en fait, il ne faut pas appliquer une majoration de 96€ sur le jour férié, mais un jour férié est rémunéré 96€ (et pas 45€ + 96€ pour un jour en semaine par exemple), quel que soit le jour de la semaine.
    - En ce qui concerne la partie intervention, je ne comprends pas pourquoi tu as supprimé toutes mes colonnes et les as remplacées par ta la tienne, car j'ai vraiment besoin de toutes ces colonnes.
    * J'ai besoin de comptabiliser le temps de travail global (heures de nuit comprises)
    * J'ai besoin de comptabiliser mon temps de travail de nuit

    Le but n'est pas d'additionner ces heures, mais d'additionner le produit de mon nombre d'heure total travaillé par mon taux horaire avec le produit du nombre d'heure de nuit travaillé par 33% de mon taux horaire

    J'aime bien comment est découpé mon exemple car on voit bien chaque étape de calcul, ce qui permet une meilleure vérification.
    Les valeurs attendues sont celles en rouge dans mon exemple.

    Je vous remercie déjà beaucoup pour votre aide.

    Cordialement,
    Korbandia

  5. #5
    Membre du Club
    Homme Profil pro
    Étudiant
    Inscrit en
    avril 2019
    Messages
    110
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 22
    Localisation : France, Bouches du Rhône (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Étudiant

    Informations forums :
    Inscription : avril 2019
    Messages : 110
    Points : 65
    Points
    65
    Par défaut
    tu à ceci

    v = v + 96

    à la place écris

    v = v + 51

  6. #6
    Nouveau Candidat au Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    novembre 2019
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux

    Informations forums :
    Inscription : novembre 2019
    Messages : 5
    Points : 1
    Points
    1
    Par défaut
    Citation Envoyé par sorbetframboise Voir le message
    tu à ceci

    v = v + 96

    à la place écris

    v = v + 51
    J'y ai pensé, mais malheureusement, si le jour férié tombe un samedi il faut rajouter 48 et si il tombe un dimanche il faut rajouter 0

  7. #7
    Membre expérimenté
    Homme Profil pro
    retraité
    Inscrit en
    juin 2012
    Messages
    1 026
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : retraité
    Secteur : Associations - ONG

    Informations forums :
    Inscription : juin 2012
    Messages : 1 026
    Points : 1 670
    Points
    1 670
    Par défaut
    Bonjour,

    S'il te faut conserver toutes les colonnes, je proposerais de changer seulement la colonne K en y utilisant la formule personnalisée hNuit() suivante:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    58
    59
    60
    61
    62
    63
    64
    65
    66
    67
    68
    69
    70
    Public Function hNuit(d1 As Date, d2 As Date)
       Dim nbH As Double, nbHJ As Double, nbHN As Double
       Dim d1hN As Date, d1hJ As Date, d2hN As Date, d2hJ As Date
       '---
       If d2 < d1 Then
          hNuit = "d2 < d1 !"    '--- non admis
          Exit Function
       End If
       nbH = d2 - d1
       If nbH > 1 Then
          hNuit = "> 24h !"      '--- non admis --- entrer 2 interventions
          Exit Function
       End If
       nbHJ = 0
       nbHN = 0
       d1hJ = Int(d1) + Range("Tarif").Cells(9, 1)      '--- date1-heure début jour
       d1hN = Int(d1) + Range("Tarif").Cells(8, 1)      '--- date1-heure début nuit
       d2hJ = Int(d2) + Range("Tarif").Cells(9, 1)      '--- date2-heure début jour
       d2hN = Int(d2) + Range("Tarif").Cells(8, 1)      '--- date2-heure début nuit
       'Debug.Print d1hJ, d1hN, d2hJ, d2hN
       '---
       If d1 < d1hJ Then
          '--- commence entre minuit et 6h du jour 1
          If d2 < d1hJ Then
             nbHN = d2 - d1
             d1 = d2
          Else
             nbHN = nbHN + d1hJ - d1
             d1 = d1hJ
          End If
       End If
       If d1 < d1hN Then
          '--- commence entre 6h et 22h du jour 1
          If d2 < d1hN Then
             nbHJ = nbHJ + d2 - d1
             d1 = d2
          Else
             nbHJ = d1hN - d1
             d1 = d1hN
          End If
       End If
       If d2 <= Int(d1) + 1 Then
          '--- intervention se termine à minuit ou avant
          nbHN = nbHN + d2 - d1
       Else
          '--- intervention sur 2 jours (dates) consécutifs
          If d1 < d2hJ Then
             '--- commence entre 22h et 6h du jour 2
             If d2 < d2hJ Then
                nbHN = nbHN + d2 - d1
                d1 = d2
             Else
                nbHN = nbHN + d2hJ - d1
                d1 = d2hJ
             End If
          End If
          If d1 < d2hN Then
             '--- commence entre 6h et 22h  du jour 2
             If d2 < d2hN Then
                nbHJ = nbHJ + d2 - d1
                d1 = d2
             Else
                nbHJ = nbHJ + d2hN - d1
                d1 = d2hJ
             End If
          End If
       End If
       hNuit = nbHN
       'Debug.Print Hour(nbHN) + IIf(Minute(nbHN) > 0, 0.5, 0) + IIf(Minute(nbHN) > 30, 0.5, 0)
    End Function
    Pour la prime jour férié, que se passe-t-il quand le 11 novembre tombe un dimanche? Quid si samedi+dimanche?
    Pâques, il y a dimanche et lundi. Quid pour samedi+dimanche+lundi dans ce cas?

    Cordialement.

  8. #8
    Nouveau Candidat au Club
    Homme Profil pro
    Administrateur systèmes et réseaux
    Inscrit en
    novembre 2019
    Messages
    5
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Hauts de Seine (Île de France)

    Informations professionnelles :
    Activité : Administrateur systèmes et réseaux

    Informations forums :
    Inscription : novembre 2019
    Messages : 5
    Points : 1
    Points
    1
    Par défaut
    Merci beaucoup pour vos réponses.

    Si le jour férié tombe un dimanche, c'est 96€
    Si le jour férié tombe un samedi, c'est 96€, du coup on ne compte plus le forfait de 120€ pour samedi dimanche, mais 96 + 96.
    Pour pâques, seul le lundi est férié, du coup ça ferait 120 (forfait samedi + dimanche) + 96 pour le lundi férié.

    D'avance merci pour votre aide.

  9. #9
    Membre expérimenté
    Homme Profil pro
    retraité
    Inscrit en
    juin 2012
    Messages
    1 026
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : Belgique

    Informations professionnelles :
    Activité : retraité
    Secteur : Associations - ONG

    Informations forums :
    Inscription : juin 2012
    Messages : 1 026
    Points : 1 670
    Points
    1 670
    Par défaut
    Bonjour,

    Une autre version avec heures 'jour' et heures 'nuit'.

    A noter: le problème 'classique' des arrondis avec les heures donnant parfois un arrondi vers le haut alors qu'il n'en faudrait pas (voir cellule I6). Cela doit provenir du fait que les heures sont en pratique contenues dans la partie décimale de nombres à virgule flottante. Pour éviter cela, il faut passer par ce genre de formule:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    hJN = Hour(nbHJ) + IIf(Minute(nbHJ) > 0, 0.5, 0) + IIf(Minute(nbHJ) > 30, 0.5, 0)
    Bonne continuation.
    Fichiers attachés Fichiers attachés

Discussions similaires

  1. Réponses: 3
    Dernier message: 05/08/2015, 17h09
  2. Horaires : Pourquoi faire plus de 35h par semaines ?
    Par the_ugly dans le forum Emploi
    Réponses: 112
    Dernier message: 16/07/2009, 18h55
  3. [OpenOffice][Tableur] Calcul horaires de nuit avec calc.
    Par Badole dans le forum OpenOffice & LibreOffice
    Réponses: 4
    Dernier message: 02/03/2009, 17h08
  4. calcul horaire sur 1 semaine
    Par wholehog2 dans le forum Macros et VBA Excel
    Réponses: 1
    Dernier message: 25/06/2007, 21h27
  5. [requête] cumul d'horaires par semaine
    Par Toff !!!!! dans le forum Requêtes et SQL.
    Réponses: 1
    Dernier message: 02/11/2005, 12h42

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo