Discussion :

[korbandia]

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

[EricDgn]

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.

[korbandia]

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 !!

[korbandia]

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

[sorbetframboise]

tu à ceci

v = v + 96

à la place écris

v = v + 51

[korbandia]

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

[EricDgn]

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.

[korbandia]

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.

[EricDgn]

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.