[XL-2010] Calcul heures Excel avec plage horaire [Résolu]

[lafafmentvotre]

Bonjour

Je suis à la recherche de formule afin de calculer le temps d'ouverture d'une action entre la date/heure de début et la date/heure de fin avec une plage d'heures travaillées.

Par exemple, cette action peut commencer à le 29 novembre 2011 à 15h00 et terminer le 30 novembre à 9h00 avec une plage horaire de travail de 8h00 à 18h00. Donc cette action a été ouverte 4 heures.

Je voudrais exclure aussi les samedi et dimanche non travaillés ainsi que les jours fériés dans mon calcul (que je peux définir depuis une table).

Est-ce possible et comment faire ?

Merci par avance pour votre aide.

[Daniel.C]

Bonjour,

Tu veux dire que si un jour est férié, tu n'as que l'heure d'embauche de la veille et l'heure de débauche du lendemain; eg. pour le 14/07 :
embauche : 13/07 17:00
débauche : 15/07 10:00
?

[ZebreLoup]

Tu calcules le nombre de jours ouvrés (cf de nombreux posts sur ce forum), tu multiplie par 10 (8h-18h), tu rajoutes les heures d'ouverture du premier jour et tu enlèves ceux du dernier jours après l'heure indiquée. Il faut peut-être rajouter une petite vérification si le premier ou dernier jour est aussi un jour non ouvré.

[lafafmentvotre]

Bonjour Daniel.C

C'est une action sur l'ouverture d'un ticket d'incident.
J'ai une colonne avec date/heure d'ouverture du ticket et une colonne avec date/heure fermeture du ticket.
Donc oui, c'est cela pour ta demande :
ouverture ticket : 13/07 17:00
fermeture ticket: 15/07 10:00
avec le 14/07 férié, cela donne : 3 heures d'ouverture de ticket.

[MarcelG]

Bonjour à vous deux,

Si je puis me permettre de m'immiscer, on pourrait envisager une fonction basée sur cette formule.

Avec
prise de fonction : heure1 date1
fin de fonction : heure2 date2

temps de fonction = [(nombre de jours ouvrés entre date2 et date1) * (nombre d'heures disponibles d'une journée)] - [heure1 - heure2]

Pour le calcul du nombre de jours ouvrés entre 2 dates, voir la FAQ

Nb jours ouvrés entre 2 dates

[Daniel.C]

@ MarcelG :

Sans utiliser VBA, tu as la fonction : NB.JOURS.OUVRES. Pour les versions d'Excel antérieures à XL2007, il faut activer la macro complémentaire "Utilitaire d'analyse".

[lafafmentvotre]

J'ai essayé mais cela n'est pas concluant (cf PJ)

[MarcelG]

Salut Daniel,

Je suis d'accord. Une réserve cependant consiste en l'obligation de créer une zone comportant la liste des jours fériés.

Dans le code, ta liste est gérée et s'adapte à toutes les années.

Pour ma part, j'ai codé :

avec, dans la plage A1:B2

31/10/2011 17
02/11/2011 12

Code :

1
2
3
4
5
6
7
8
9
10
Public Function HeuresTravail(date1 As Date, heure1 As Long, date2 As Date, heure2 As Long) As Long
diff = (Work_Days(date1, date2) - 1) * 10 - (heure1 - heure2)
HeuresTravail = diff
End Function
 
Public Sub ESSAI()
With Sheets(1)
        MsgBox "Temps " & HeuresTravail(.Range("A1"), .Range("B1"), .Range("A2"), .Range("B2"))
End With
End Sub

Ici 10 (en dur) représente le nombre d'heures disponibles dans une journée.

La procédure me retourne bien le nombre 5.

Bon appétit. A tout à l'heure.

[lafafmentvotre]

Bonjour MarcelG

Peux-tu m'expliquer comment mettre cela en place dans mon exemple ?

Merci

[Daniel.C]

Dans le code, ta liste est gérée et s'adapte à toutes les années.

Ça me paraît un peu risqué, cette liste variant suivant les branches d'activité et suivant les pays...

[MarcelG]

A Daniel,

Si les règles changent, alors ta plage de jours fériés doit aussi changer. Cette méthode n'est donc pas plus dangereuse. Le code aussi peut être adapté et gérer le pays en question.

A Lafafmentvotre,

Dans un module standard, tu copies les 3 fonctions décrites dans la FAQ.
Tu y ajoutes ensuite le code que j'ai reporté plus haut. Tout dépend de la présentation de tes données (cellules début-fin avec date et heure soit 4 au total, userform, résultat dans msgbox ou dans une cellule).

N'hésite pas à revenir si nécessaire.

[lafafmentvotre]

Re

J'ai essayé ce que tu m'as dit (cf PJ) mais il m'annonce 12 au lieu des 5 que je devrais avoir car ma plage de travail est de 8H00 à 18h00

Début 31/10/2011 17:00, fin 02/11/2011 12:00 avec férié sur 01/11

Help please !!!!

[MarcelG]

Lafafment,

J'ai effectué un essai sur ces données, avec succès (Résultat : 5)

Je n'arrive pas à ouvrir ton fichier. Pourrais-tu l'enregistrer avec l'extension zip, s'il te plaît. En dernier recours, tu peux le joindre en l'épurant au maximum.

[lafafmentvotre]

Hello

Le voici

[MarcelG]

Bonsoir,

Tes cellules sont informées au format date entière (date et heure) et non comme date d'une part et entier d'autre part (pour les heures) ainsi que décrit dans l'exemple ci-dessus.

Ta rencontre de difficultés est donc logique.

Dans ces conditions, les cellules A1 et A2 sont suffisantes. Il te faut seulement en extraire les nombre de séries et les heures.

Soit

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
Public Sub essai2()
 
Dim lib_date1 As Range, lib_date2 As Range
Dim date_1 As Date, date_2 As Date
Dim heure_1 As Long, heure_2 As Long
 
'Affectation des variables date
With Sheets(1)
        Set lib_date1 = .Range("A1")
        Set lib_date2 = .Range("A2")
End With
 
'Calcul des Serial de chaque date
date_1 = DateSerial(Year(lib_date1), Month(lib_date1), Day(lib_date1))
date_2 = DateSerial(Year(lib_date2), Month(lib_date2), Day(lib_date2))
 
'Calcul des heures de chaque date
heure_1 = Hour(lib_date1)
heure_2 = Hour(lib_date2)
 
'Calcul de la durée effective
MsgBox "Temps " & HeuresTravail(date_1, heure_1, date_2, heure_2)
 
'Réinitialisation des variables
Set ib_date1 = Nothing
Set ib_date2 = Nothing
 
End Sub

Reviens si nécessaire.

[lafafmentvotre]

Hello

Quel talent, cela fonctionne

Par contre, afin de pouvoir utiliser tout cela dans mon fichier global, comment vais je pouvoir faire afin de trouver le nombre rechercher dans chaque ligne ? Car je ne vais pas avoir qu'une ligne...

Dans le fichier joint, c'est le format qui va être utilisé :
Colonne A : début
Colonne B : fin
Colonne C : résultat

Merci par avance pour ton aide

[MarcelG]

Bonjour Lafafmentvotre, Bonjour au Forum,

Ici, tu ne raisonnes pas en heures entières mais en temps rééel (heures, minutes, secondes).

Auusi, faut-il revoir la fonction de base afin de raisonner en secondes puis convertir le résultat en hh:mm:ss.

Autre chose : il est bien convenu que les plages horaires sont de 8 à 18 heures, soit 10 heures au total.

La fonction :

Code :

1
2
3
4
5
Public Function HeuresTravail(date1 As Date, heure1 As Long, date2 As Date, heure2 As Long) As Long
'36000 = 10 heures * 60 minutes * 60 secondes
diff = ((Work_Days(date1, date2) - 1) * 36000 - (heure1 - heure2))
HeuresTravail = diff
End Function

Le code qui complète ta plage de la colonne C.

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
Public Sub essai3()
 
Dim lib_date1 As Range, lib_date2 As Range
Dim date_1 As Date, date_2 As Date
Dim heure_1 As Long, heure_2 As Long
Dim dercel As Range, c As Range
 
With Sheets(1)
        Set dercel = .Cells(.Rows.Count, 2).End(xlUp)
        For Each c In .Range("C2", dercel.Offset(0, 1))
                With c
                        Set lib_date1 = .Offset(0, -2)
                        Set lib_date2 = .Offset(0, -1)
                        'Calcul des Serial de chaque date
                        date_1 = DateSerial(Year(lib_date1), Month(lib_date1), Day(lib_date1))
                        date_2 = DateSerial(Year(lib_date2), Month(lib_date2), Day(lib_date2))
                        'Calcul des heures de chaque date
                        heure_1 = Hour(lib_date1) * 3600 + Minute(lib_date1) * 60 + Second(lib_date1)
                        heure_2 = Hour(lib_date2) * 3600 + Minute(lib_date2) * 60 + Second(lib_date2)
                        '86400 = 60 minutes * 60 secondes * 24 heures
                        .Value = Format(HeuresTravail(date_1, heure_1, date_2, heure_2) / 86400, "hh"" h ""mm"" m ""ss"" s""")
                End With
                'Réinitialisation des variables
                Set ib_date1 = Nothing
                Set ib_date2 = Nothing
        Next c
End With
 
 
End Sub

Pour exemple,

A2 = 03/10/2011 08:58:42
B2 = 05/10/2011 10:00:09

Résultat

C2 = 21 h 01 m 27 s

[lafafmentvotre]

Re bonjour à tous

Merci pour cette solution qui fonctionne parfaitement. je vais tester avec le fichier initial mais je suis plus que confiant.

J'ai juste une question, suis je obligé de lancer la macro à chaque calcul ou existe-t-il un moyen que cela se calcul en automatique ?

Merci Marcel

[MarcelG]

Salut Lafafmentvotre,

Je te conseillerais de positionner l'appel de cette procédure au sein d'une procédure évènementielle. (Save, Change....).

Elle s'exécuterait ainsi sans autre intervention.

Reviens si nécessaire.

[lafafmentvotre]

Ok, je vais faire cela

Encore mille merci

J'ai parlé trop vite

Dans le fichier global, date 1 est en colonne B et date 2 en colonne M.
Le résultat devant se trouver dans colonne Z

J'ai donc logiquement modifié pour

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
Public Sub Calc_Hours()
 
Dim lib_date1 As Range, lib_date2 As Range
Dim date_1 As Date, date_2 As Date
Dim heure_1 As Long, heure_2 As Long
Dim dercel As Range, c As Range
 
With Sheets(1)
Set dercel = .Cells(.Rows.Count, 2).End(xlUp)
For Each c In .Range("Z2", dercel.Offset(0, 1))
With c
Set lib_date1 = .Offset(0, -24)
Set lib_date2 = .Offset(0, -13)
'Calcul des Serial de chaque date
date_1 = DateSerial(Year(lib_date1), Month(lib_date1), Day(lib_date1))
date_2 = DateSerial(Year(lib_date2), Month(lib_date2), Day(lib_date2))
'Calcul des heures de chaque date
heure_1 = Hour(lib_date1) * 3600 + Minute(lib_date1) * 60 + Second(lib_date1)
heure_2 = Hour(lib_date2) * 3600 + Minute(lib_date2) * 60 + Second(lib_date2)
'86400 = 60 minutes * 60 secondes * 24 heures
.Value = Format(HeuresTravail(date_1, heure_1, date_2, heure_2) / 86400, "hh"" h ""mm"" m ""ss"" s""")
End With
'Réinitialisation des variables
Set ib_date1 = Nothing
Set ib_date2 = Nothing
Next c
End With
 
 
End Sub

Mais j'ai une erreur sur

Code :

Set lib_date1 = .Offset(0, -24)

Je comprends pas