Calcul très complexe d'heures !

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

antho2bordo

XLDnaute Nouveau
Bonjour à tous.
Bon après avoir passé la nuit sur pour créer un joli tableau afin de m'avancer dans mon travail depuis chez moi, j'ai re créer une sorte de planning qui me permettra si j'arrive à mes fins à m'avancer dans mon travail afin de créer les heures de boulot de 60 à 70 personnes, mais là j'arrive à un problème !

Après plusieurs recherches sur le forum et sur le forum hardaware et chez notre ami commun, google, je me tourne vers vous afin de trouver une réponse à mon problème.



Voilà je suis entrain de faire un tableau d'employés avec les jours travaillés et les jours de repos, et les heures de travail bref, je vous passe le lien de la capture décran, afin que vous puissiez visualiser mon problème !

http://www.hiboox.fr/go/images/divers/calcul-excel,cf67aee88b514bcbec228b4ab7908627.png.html

Voilà,

Je souhaite que lorsque je tape par exemple : 09h00 - 14h00 et bien que, le total d'heures qui est donc de 5 heures s'affiche au niveau de la case signalé par la flèche rouge.

Je souhaite également étendre cette formule aux autres heures signalées par les fleches noir et que tout ce joli total se retrouve donc, dans la case à droite au niveau de la fleche rouge... et biensur étendre cette formule sur toutes les autres lignes de chaques employés.


COMMENT faire ?
Bon mercredi à tous !
 
Dernière édition:
Re : Calcul très complexe d'heures !

Bonjour Christian0258
Essayez :
Code:
[COLOR="DarkSlateGray"][B]Function TotHor(r As Range)
Application.Volatile
Dim oCel As Range, x, i As Long, y, z, t
   For Each oCel In r.Cells
      If oCel.Value <> "OFF" And Not IsEmpty(oCel) Then
         x = Split(Replace(Replace(Replace(Replace(Replace(oCel.Value, "H", ":", , , vbTextCompare), " - ", "#"), " -", "#"), "- ", "#"), "-", "#"))
         For i = 0 To UBound(x)
            y = Split(Trim(x(i)), "#")
            If y(0) = "24:00" Then y(0) = "00:00"
            If y(1) = "24:00" Then y(1) = "00:00"
            If CDate(y(1)) < CDate(y(0)) Then t = t + 1
            t = t + CDate(y(1)) - CDate(y(0))
         Next i
      End If
   Next oCel
   TotHor = t
End Function

Function TotHorJ(r As Range) [COLOR="SeaGreen"]'Heures entre a et b.[/COLOR]
Application.Volatile
Dim a!, b!
Dim oCel As Range, x, i&, y, z, t!
   a = CSng(CDate("06:00")) [COLOR="SeaGreen"]'Début des heures de jour.[/COLOR]
   b = CSng(CDate("21:00")) [COLOR="SeaGreen"]'Fin des heures de jour.[/COLOR]
   For Each oCel In r.Cells
      If oCel.Value <> "OFF" And Not IsEmpty(oCel) Then
         x = Split(Replace(Replace(Replace(Replace(Replace(oCel.Value, "H", ":", , , vbTextCompare), " - ", "#"), " -", "#"), "- ", "#"), "-", "#"))
         For i = 0 To UBound(x)
            y = Split(Trim(x(i)), "#")
            If y(0) = "24:00" Then y(0) = "00:00"
            If y(1) = "24:00" Then y(1) = "00:00"
            If CSng(CDate(y(1))) < CSng(CDate(y(0))) Then
               t = t + WorksheetFunction.Max(CSng(CDate(y(1))) - a, 0) + WorksheetFunction.Max(b - CSng(CDate(y(0))), 0)
            Else
               t = t + WorksheetFunction.Max(WorksheetFunction.Min(CSng(CDate(y(1))), b) - WorksheetFunction.Max(CSng(CDate(y(0))), a), 0)
            End If
         Next i
      End If
   Next oCel
   TotHorJ = t
End Function

Function TotHorN(r As Range) [COLOR="SeaGreen"]'Heures entre 0h00 et a plus heures entre b et 24h00.[/COLOR]
Application.Volatile
   TotHorN = TotHor(r) - TotHorJ(r)
End Function[/B][/COLOR]
ROGER2327
#3058
 

Pièces jointes

Dernière édition:
Re : Calcul très complexe d'heures !

Suite...
Merci Messieurs.

J'ai corrigé la fonction TotHorN : il manquait une ligne.
Code:
[COLOR="DarkSlateGray"][B]Function TotHorN(r As Range)
   [COLOR="Sienna"]Application.Volatile[/COLOR]
   TotHorN = TotHor(r) - TotHorJ(r)
End Function[/B][/COLOR]
ROGER2327
#3060
 
Re : Calcul très complexe d'heures !

Re...
Bonjour à tous,

Merci de ce complément de code mais nous avions rectifié... N'est-il point Christian... Arf Arf

A+ à tous
Cela va sans dire, mais cela va encore mieux en le disant...

Plus sérieusement, en relisant le code proposé, j'ai vu qu'un petit nettoyage de printemps s'imposait : inutile de déclarer z qui n'est jamais utilisé.
De plus, le calcul du nombre des heures de nuit par différence entre le nombre global d'heures et le nombre des heures de jour est une facilité qui coûte cher en temps d'exécution : il est plus propre d'écrire une fonction spécifique.

Voici donc un code plus correct :
Code:
[COLOR="DarkSlateGray"][B]Function TotHor(r As Range)
   Application.Volatile
Dim oCel As Range, i&, x, y, t!
   For Each oCel In r.Cells
      If oCel.Value <> "OFF" And Not IsEmpty(oCel) Then
         x = Split(Replace(Replace(Replace(Replace(Replace(oCel.Value, "H", ":", , , vbTextCompare), " - ", "#"), " -", "#"), "- ", "#"), "-", "#"))
         For i = 0 To UBound(x)
            y = Split(Trim(x(i)), "#")
            If y(0) = "24:00" Then y(0) = "00:00"
            If y(1) = "24:00" Then y(1) = "00:00"
            If CSng(CDate(y(1))) < CSng(CDate(y(0))) Then t = t + 1
            t = t + CSng(CDate(y(1))) - CSng(CDate(y(0)))
         Next i
      End If
   Next oCel
   TotHor = t
End Function

Function TotHorJ(r As Range) [COLOR="SeaGreen"]'Heures entre a et b.[/COLOR]
   Application.Volatile
Dim a!, b!
Dim oCel As Range, i&, x, y, t!
   a = CSng(CDate("06:00")) [COLOR="SeaGreen"]'Début des heures de jour.[/COLOR]
   b = CSng(CDate("21:00")) [COLOR="SeaGreen"]'Fin des heures de jour.[/COLOR]
   For Each oCel In r.Cells
      If oCel.Value <> "OFF" And Not IsEmpty(oCel) Then
         x = Split(Replace(Replace(Replace(Replace(Replace(oCel.Value, "H", ":", , , vbTextCompare), " - ", "#"), " -", "#"), "- ", "#"), "-", "#"))
         With WorksheetFunction
            For i = 0 To UBound(x)
               y = Split(Trim(x(i)), "#")
               If y(0) = "24:00" Then y(0) = "00:00"
               If y(1) = "24:00" Then y(1) = "00:00"
               If CSng(CDate(y(1))) < CSng(CDate(y(0))) Then
                  t = t + .Max(CSng(CDate(y(1))) - a, 0) + .Max(b - CSng(CDate(y(0))), 0)
               Else
                  t = t + .Max(.Min(CSng(CDate(y(1))), b) - .Max(CSng(CDate(y(0))), a), 0)
               End If
            Next i
         End With
      End If
   Next oCel
   TotHorJ = t
End Function

Function TotHorN(r As Range) [COLOR="SeaGreen"]'Heures entre 0h00 et a plus heures entre b et 24h00.[/COLOR]
   Application.Volatile
Dim a!, b!
Dim oCel As Range, i&, x, y, t!
   a = CSng(CDate("06:00")) [COLOR="SeaGreen"]'Début des heures de jour.[/COLOR]
   b = CSng(CDate("21:00")) [COLOR="SeaGreen"]'Fin des heures de jour.[/COLOR]
   For Each oCel In r.Cells
      If oCel.Value <> "OFF" And Not IsEmpty(oCel) Then
         x = Split(Replace(Replace(Replace(Replace(Replace(oCel.Value, "H", ":", , , vbTextCompare), " - ", "#"), " -", "#"), "- ", "#"), "-", "#"))
         With WorksheetFunction
            For i = 0 To UBound(x)
               y = Split(Trim(x(i)), "#")
               If y(0) = "24:00" Then y(0) = "00:00"
               If y(1) = "24:00" Then y(1) = "00:00"
               t = t + CSng(CDate(y(1))) - CSng(CDate(y(0)))
               If CSng(CDate(y(1))) < CSng(CDate(y(0))) Then
                  t = t + 1 - .Max(CSng(CDate(y(1))) - a, 0) - .Max(b - CSng(CDate(y(0))), 0)
               Else
                  t = t - .Max(.Min(CSng(CDate(y(1))), b) - .Max(CSng(CDate(y(0))), a), 0)
               End If
            Next i
         End With
      End If
   Next oCel
   TotHorN = t
End Function[/B][/COLOR]
ROGER2327
#3063
 
Re : Calcul très complexe d'heures !

Bonjour à tous,

Effectivement le temps de "recalcul" est considérablement diminué.

Je réitère mon compliment pour ces fonctions qui vont m'être d'une grande utilité au travail.

A++
A+ à tous
 
Re : Calcul très complexe d'heures !

Re...
Bonsoir JCGL
(...) fonctions qui vont m'être d'une grande utilité au travail (...)
Si vous utilisez ces fonctions dans un cadre professionnel, testez-les soigneusement. Ce sont des formules que je n'ai pas moi-même utilisées sur une grande échelle : je ne les ai testées que sur un petit nombre de cas (à peine une centaine) et je me méfie toujours dès qu'il s'agit de calculs sur des dates car Excel n'est pas avare en facéties dans ce domaine.
Et si vous découvrez des anomalies, ne manquez pas de me le faire savoir...
Merci d'avance.​
Bon dimanche.
ROGER2327
#3065
 
Re : Calcul très complexe d'heures !

Je tiens à remercier ROGER qui ma beaucoup aidé, je vais pouvoir avancer mon travail depuis chez moi, c'est très gentil de votre part.

Vous, et le forum m'avait été d'une grande aide, et c'est une mine d'information géniale.
 
Dernière édition:
Re : Calcul très complexe d'heures !

Bonjour à tous,

Avant d'éditer tu écrivais :


Il faut mettre les fonctions dans un module :


A+ à tous

Oui, mais c'était une erreur de ma part, je n'avais pas activé les macros donc la fonction ne fonctionné pas.

Une dernière question toujours dans mon tableau d'heures !
C'est là aussi hyper complexe, déjà pour vous expliquer je cherche mes mots.

Est il possible que lorsque je rentre par exemple 09h00-14h00 le lundi pour VANESSA que dans une case quelconque le chiffre 1 apparaisse et si je rentre 2 autres horaires similaire pour le lundi à deux autres personnes différentes cela s'additionne et me donne au final 3 dans la case quelconque ?


EN GROS je veux essayer de faire un tableau récapitulatif en dessous du tableau des horaires pour visiualiser plus facilement combien j'ai déjà mis de gens qui font l'ouverture, la fermeture etc...

Techniquement c'est possible ?
Car j'ai tenté quelques essais sans résultats
 
Re : Calcul très complexe d'heures !

Re...
(...)
Une dernière question toujours dans mon tableau d'heures !
C'est là aussi hyper complexe, déjà pour vous expliquer je cherche mes mots.

Est il possible que lorsque je rentre par exemple 09h00-14h00 le lundi pour VANESSA que dans une case quelconque le chiffre 1 apparaisse et si je rentre 2 autres horaires similaire pour le lundi à deux autres personnes différentes cela s'additionne et me donne au final 3 dans la case quelconque ?


EN GROS je veux essayer de faire un tableau récapitulatif en dessous du tableau des horaires pour visiualiser plus facilement combien j'ai déjà mis de gens qui font l'ouverture, la fermeture etc...

Techniquement c'est possible ?
Car j'ai tenté quelques essais sans résultats
C'est probablement possible, mais il serait utile de définir précisément le résultat attendu.Une demande EN GROS va donner une réponse approximative...​
ROGER2327
#3069
 
Re : Calcul très complexe d'heures !

Et bien enfaite j'aimerais que l'ordinateur me dise par jour combien j'ai de personnes qui font 09h00-14h00 par exemple.


J'ai fais un zip pour montrer le style de tableau que je "souhaite" faire si cela est possible.
 

Pièces jointes

Re : Calcul très complexe d'heures !

Bonjour à tout le forum,

Nouvelle demande à ROGER,

Toujours dans le même esprit (fonction), pensez-vous qu'il serait possible
de calculer les heures pour chaque tranche d'horaires....

Expl d'horaire ;
8:00-12:00 12:30-16:00 22:30-8:00

Total : 17:00
Jour : 9:30
Nuit : 7:30
4:00 pour (8:00-12:00)
3:30 pour (12:30-16:00)
9:30 pour (22:30-8:00)

Par avance merci pour votre réponse, et encore merci pour le travail déjà fourni.

Bien amicalement,
Christian
 
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD

Discussions similaires

Réponses
3
Affichages
2 K
Retour