[XL-2003] Formule pointage horaire !

[lechiffre2012]

bonjour

Je tiens à remercier tout les membre de ce forum que je trouve très intéressant et je profite de vous souhaiter une bonne année 2012.

Sinon j'ai vraiment besoin de votre aide, car je travaille dans une société et je m'en occupe de calculer le pointage donc je reçois chaque mois des feuilles de pointage de tout le personnel est je les calcule manuellement selon un barème mais comme l'effectif est beaucoup ça me prends du temps surtout que l'effectif augmente chaque mois !

donc j'aimerai bien votre aide a avoir une formule sous Excel pour me faciliter la tache; je vous donne le barème a suivre ainsi la feuille en pièce joint que je reçois chaque mois !

NB : pour la pause ils prenne soit le petit déjeuner ou déjeuner ou diner parce que il y a 3 équipes, et la pause c une heure sauf vendredi deux heure pour le déjeuner donc le basic est toujours 9H sauf vendredi c'est 8h en déduisant la pause.
exemple en PJ


Du lundi au Vendredi :
de 08:00 à 18:00 >> 0%
de 18:00 à 21:00 >> 25%
de 21:00 à 06:00 >> 50%
de 06:00 à 08:00 >> 25%

Samedi :
de 06:00 à 21:00 >> 25%
de 21:00 à 06:00 >> 50%

Dimanche :
de 06:00 à 21:00 >> 50%
de 21:00 à 06:00 >> 100%

Jour Férié :
100%

Merci beaucoup d'avance.

[Daniel.C]

Bonjour et bonne année.

Comment reconnait-on les jours fériés ?

[lechiffre2012]

on n'a pas des dates fixe pour les jour férié selon le calendrier hégirien ça se change chaque année donc si vous pouvez me faire une ligne séparé pour les jour férié et moi je vais le copier a chaque fois on aura ce jour férié

merci beaucoup Mr Daniel et bonne année !

[Daniel.C]

Bonjour,

Il y a une indétermination, pour moi, pour les jours fériés : Quelle est la durée de la pause de midi pour les jours fériés, par exemple si celui-ci tombe un vendredi ? D'autre part précise-moi si la durée de travail est toujours de 8 heures (est-ce qu'il faut ajouter un contrôle ?) ou s'il peut y avoir des retards ou des périodes de travail supérieures à 8 h.

[lechiffre2012]

la pause est toujours une heure sauf vendredi c'set deux heures et c'est même pour les jour férié ! et pour le basic ça dépasse pas 9h mais il se peut être moins parce que il n'a y a pas un horaire fixe ! et ya pas des retards

[Daniel.C]

la pause est toujours une heure sauf vendredi c'set deux heures et c'est même pour les jour férié !

C'est le problème; j'ajouterai donc une ligne "férié" et une autre "férié vendredi".

[ZebreLoup]

Je regardais aussi ton message et une question me vient :
Vu qu'on ne connait pas l'heure de la pause, comment fait-on pour savoir sur quel barème elle est déduite ?
Par exemple, quelqu'un travaille un jeudi de 14H à 23H. A priori, il va faire une pause pour le dîner. Comment savoir si elle a été faite avant 18h, entre 18h et 21h, ou après 21h ? Car cela amènera à des répartition différentes.

[lechiffre2012]

Envoyé par Daniel.C

C'est le problème; j'ajouterai donc une ligne "férié" et une autre "férié vendredi".

oui ça marche , merci beaucoup d'avance c'est vraiment gentil

[Daniel.C]

Tu dis qu'il n'y a pas d'horaire fixe ? Chacun arrive quand il veut ? Réponds également à la question de ZebreLoup.

[lechiffre2012]

Envoyé par Daniel.C

Tu dis qu'il n'y a pas d'horaire fixe ? Chacun arrive quand il veut ? Réponds également à la question de ZebreLoup.

non c'est pas que chacun, vient quand il veut c'est juste parce que il y a des personne qui vient à 08:00 mais ils sortent pas à 18h ils reste jusqu'a 20H OU 19H selon le besoin !

Envoyé par ZebreLoup

Je regardais aussi ton message et une question me vient :
Vu qu'on ne connait pas l'heure de la pause, comment fait-on pour savoir sur quel barème elle est déduite ?
Par exemple, quelqu'un travaille un jeudi de 14H à 23H. A priori, il va faire une pause pour le dîner. Comment savoir si elle a été faite avant 18h, entre 18h et 21h, ou après 21h ? Car cela amènera à des répartition différentes.

pour le déjeuner c'est toujours de 12H à 13H sauf vendredi c de 12H à 14H
pour le diner c'est toujours de 19h à 20H ( une heure)

[Daniel.C]

c'est juste parce que il y a des personne qui vient à 08:00

D'accord, est-ce qu'on peut dire qu'une équipe commence à 8:00 ? Quelle l'heure normale d'embauche pour les autres équipes ?

J'ai quand même besoin, pour ne pas trop compliquer les calculs, de connaître la durée maximale de la durée de travail. D'autre part, quand quelqu'un travaille de nuit et termine après 6h, le temps de travail est-il comptabilisé le jour de l'embauche ou le lendemain ? par exemple : embauche à 21:00, fin de travail à 07:00 le lendemain. Comment le temps est-il comptabilisé ?

[lechiffre2012]

Envoyé par Daniel.C

D'accord, est-ce qu'on peut dire qu'une équipe commence à 8:00 ? Quelle l'heure normale d'embauche pour les autres équipes ?

normalement il y a deux équipes, le premier commence de 08:00 à 18:00 ET LE DEUXIÈME DE 19:00 à 07:00

MERCI

[Daniel.C]

Au début, il y avait trois équipes et un petit déjeuner...

[lechiffre2012]

oui Mr Daniel il avait trois équipes mais dernièrement ils ont éliminer une équipe et ils ont laisser juste deux, j'ai cité trois équipe ou cas ou ils font revenir la troisième équipe mais si ça vous dérange pas vous pouvez travailler seulement sur deux équipe et seulement la pause de déjeuner et le diner pour bien faciliter la tache !

merci et je suis vraiment dessolé pour ce dérangement !

merci encore une fois

[Daniel.C]

OK, mais ce n'est pas hyper simple. Je pense que je vais devoir écrire une fonction VBA, c'est pourquoi je voulais être sûr d'avoir les bonnes données.

[lechiffre2012]

ok Mr Daniel, merci beaucoup pour l'effort

[Daniel.C]

Bonjour,
(note : oublie le "monsieur", j'ai l'impression de prendre 10 ans de plus )
Personnellement, je tutoies tout le monde, mais si ça te choques, dis-le.

J'ai écrit la fonction suivante. Elle est construite avec les éléments :
- date de début
- date de fin
- jour de la semaine
-pourcentage du working time
- facultatif, un "F" pour les jours fériés
Par exemple, la cellule J3 contient :

Code :

=SI($I3<>"";calcul($D3;$E3;$C3;J$2);"")

Toute la feuille est remplie avec les formules. la seule chose que tu aies à faire est d'ajouter un "F" si le jour est férié pour la formule de la colonne 100% :
la cellulle L3 :

Code :

=SI($I3<>"";calcul($D3;$E3;$C3;L$2);"")

deviendra :

Code :

=SI($I3<>"";calcul($D3;$E3;$C3;L$2;"F");"")

Bon ceci dit, j'ai à peine testé et je suis sûr qu'il y a plein d'erreurs. Je te joins le classeur et je te laisse le soin de tester à fond. Signale-moi toutes les anomalies que tu détecteras.

Code :

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
Function Calcul(Deb As Range, Fin As Range, Jour As Range, bareme As Single, Optional F As String) As Date
Dim h25 As Date, h50 As Date, h100 As Date, Teste As Date
Application.Volatile
If Fin.Value < Deb.Value Then
    Teste = Fin.Value + 1
Else
    Teste = Fin.Value
End If
'jours fériés
If UCase(F) = "F" Then
    h100 = Teste - Deb.Value
    If Deb.Value <= #12:00:00 PM# And Teste >= #1:00:00 PM# Then h100 = h100 - #1:00:00 AM#
    If Deb.Value <= #7:00:00 PM# And Teste >= #8:00:00 PM# Then h100 = h100 - #1:00:00 AM#
    If Jour.Value = "VENDREDI" Then
    If Deb.Value <= #12:00:00 PM# And Teste >= #1:00:00 PM# Then h100 = h100 - #1:00:00 AM#
    End If
Else
    Select Case Jour.Value
        Case "SAMEDI"
            Select Case Deb.Value
                Case Is <= #6:00:00 AM#
                    h50 = h50 + Application.Min(Teste, #6:00:00 AM#) - Deb.Value
                    h25 = h25 + Application.Min(Teste, #9:00:00 PM#) - #6:00:00 AM#
                    h50 = h50 + Application.Min(Teste, #6:00:00 AM# + 1) - #9:00:00 PM#
                Case Is <= #9:00:00 PM#
                    h25 = h25 + Application.Min(Teste, #9:00:00 PM#) - Deb.Value
                    h50 = h50 + Application.Min(Teste, #6:00:00 AM# + 1) - #9:00:00 PM#
                    h25 = h25 + Application.Min(Teste, #6:00:00 AM# + 1) - #6:00:00 AM# - 1
            End Select
            If Deb.Value <= #12:00:00 PM# And Teste >= #1:00:00 PM# Then h25 = h25 - #1:00:00 AM#
            If Deb.Value <= #7:00:00 PM# And Teste >= #8:00:00 PM# Then h25 = h25 - #1:00:00 AM#
        Case "DIMANCHE"
            Select Case Deb.Value
                Case Is <= #6:00:00 AM#
                    h100 = h100 + Application.Min(Teste, #6:00:00 AM#) - Deb.Value
                    h50 = h50 + Application.Min(Teste, #9:00:00 PM#) - #6:00:00 AM#
                    h100 = h100 + Application.Min(Teste, #6:00:00 AM# + 1) - #9:00:00 PM#
                Case Is <= #9:00:00 PM#
                    h50 = h50 + Application.Min(Teste, #9:00:00 PM#) - Deb.Value
                    h100 = h100 + Application.Min(Teste, #6:00:00 AM# + 1) - #9:00:00 PM#
                    h50 = h50 + Application.Min(Teste, #6:00:00 AM# + 1) - #6:00:00 AM# - 1
            End Select
            If Deb.Value <= #12:00:00 PM# And Teste >= #1:00:00 PM# Then h50 = h50 - #1:00:00 AM#
            If Deb.Value <= #7:00:00 PM# And Teste >= #8:00:00 PM# Then h50 = h50 - #1:00:00 AM#
        Case Else
            If Deb.Value <= #7:00:00 PM# And Teste >= #8:00:00 PM# Then h25 = h25 - #1:00:00 AM#
            Select Case Deb.Value
                Case Is <= #6:00:00 AM#
                    h50 = h50 + Application.Min(Teste, #6:00:00 AM#) - Deb
                    h25 = h25 + Application.Min(Teste, #8:00:00 AM#) - #6:00:00 AM#
                    If Fin.Value > #6:00:00 PM# Then
                            h25 = h25 + Application.Min(#9:00:00 PM#, Teste) - #6:00:00 PM#
                    End If
                Case Is < #8:00:00 AM#
                    h25 = h25 + Application.Min(Teste, #8:00:00 AM#) - Deb.Value
                    If Teste > #9:00:00 PM# Then
                        h50 = h50 + Teste - #9:00:00 PM#
                        h25 = h25 + #3:00:00 AM#
                    ElseIf Teste <= #9:00:00 PM# Then
                        h25 = h25 + Teste - #6:00:00 PM#
                    End If
                Case Is < #6:00:00 PM#
                    If Teste >= #6:00:00 AM# + 1 Then
                        h25 = h25 + Application.Min(Teste, #6:00:00 AM# + 1) - #6:00:00 AM# + 1
                    ElseIf Teste > #9:00:00 PM# Then
                        h50 = h50 + Application.Min(Teste, #6:00:00 AM# + 1) - #9:00:00 PM#
                        h25 = h25 + #3:00:00 AM#
                    ElseIf Teste <= #9:00:00 PM# Then
                        h25 = h25 + Teste - #6:00:00 PM#
                    End If
                Case Is < #9:00:00 PM#
                    If Teste >= #6:00:00 AM# + 1 Then
                        h25 = h25 + Application.Min(#8:00:00 AM# + 1, Teste) - #6:00:00 AM# - 1
                        h50 = h50 + #9:00:00 AM#
                    ElseIf Teste <= #6:00:00 AM# + 1 And Teste > #9:00:00 PM# Then
                        h50 = h50 + #9:00:00 AM#
                    ElseIf Teste < #9:00:00 PM# Then
                        h25 = h25 + Teste - #6:00:00 PM#
                    End If
                Case Is >= #9:00:00 PM#
                    If Teste >= #6:00:00 AM# + 1 Then
                        h25 = h25 + Application.Min(#8:00:00 AM# + 1, Teste) - #6:00:00 AM# - 1
                        h50 = h50 + #9:00:00 AM#
                        h25 = h25 + #3:00:00 AM#
                    ElseIf Teste <= #6:00:00 AM# + 1 And Teste > #9:00:00 PM# Then
                        h50 = h50 + #9:00:00 AM#
                        h25 = h25 + #3:00:00 AM#
                    ElseIf Teste < #9:00:00 PM# Then
                        h25 = h25 + Teste - #6:00:00 PM#
                    End If
            End Select
    End Select
End If
If bareme = 0.25 Then
    Calcul = h25
ElseIf bareme = 0.5 Then
    Calcul = h50
ElseIf bareme = 1 Then
    Calcul = h100
End If
End Function