Calcul très complexe d'heures !

antho2bordo · 24 Février 2010

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 !

ROGER2327 · 3 Avril 2010

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

JCGL · 3 Avril 2010

Re : Calcul très complexe d'heures !

Bonjour à tous,
Salut Christian 🙂,
Salut Roger 🙂,

Roger : Pfiou, jolies fonctions... Du grand art... Merci de partager

A++ 🙂 et 🙂
A+ à tous

Christian0258 · 3 Avril 2010

Re : Calcul très complexe d'heures !

Re, ROGER2327, JCGL

Merci, ROGER.
C'est super.

En effet, du GRAND ART, mais par surpris, avec ROGER c'est toujours du haut niveau...

à+
Christian

ROGER2327 · 3 Avril 2010

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

JCGL · 3 Avril 2010

Re : Calcul très complexe d'heures !

Bonjour à tous,

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

A+ à tous

ROGER2327 · 3 Avril 2010

Re : Calcul très complexe d'heures !

Re...

JCGL à dit:
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

JCGL · 4 Avril 2010

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

ROGER2327 · 4 Avril 2010

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

antho2bordo · 5 Avril 2010

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.

JCGL · 5 Avril 2010

Re : Calcul très complexe d'heures !

Bonjour à tous,

Avant d'éditer tu écrivais :

Je ne sais toujours pas renrter une fonction dans excel

Il faut mettre les fonctions dans un module :

A+ à tous

antho2bordo · 5 Avril 2010

Re : Calcul très complexe d'heures !

JCGL à dit:
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

ROGER2327 · 5 Avril 2010

Re : Calcul très complexe d'heures !

Re...

antho2bordo à dit:
(...)
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

antho2bordo · 5 Avril 2010

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.

antho2bordo · 5 Avril 2010

Re : Calcul très complexe d'heures !

antho2bordo à dit:
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.

Christian0258 · 6 Avril 2010

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

Calcul très complexe d'heures !

antho2bordo

XLDnaute Nouveau

ROGER2327

XLDnaute Barbatruc

Pièces jointes

JCGL

XLDnaute Barbatruc

Christian0258

XLDnaute Accro

ROGER2327

XLDnaute Barbatruc

JCGL

XLDnaute Barbatruc

ROGER2327

XLDnaute Barbatruc

JCGL

XLDnaute Barbatruc

ROGER2327

XLDnaute Barbatruc

antho2bordo

XLDnaute Nouveau

JCGL

XLDnaute Barbatruc

antho2bordo

XLDnaute Nouveau

ROGER2327

XLDnaute Barbatruc

antho2bordo

XLDnaute Nouveau

antho2bordo

XLDnaute Nouveau

Pièces jointes

Christian0258

XLDnaute Accro

Discussions similaires

Calcul très complexe d'heures !

XLDnaute Nouveau

XLDnaute Barbatruc

Pièces jointes

XLDnaute Barbatruc

XLDnaute Accro

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Barbatruc

XLDnaute Nouveau

XLDnaute Nouveau

Pièces jointes

XLDnaute Accro

Discussions similaires

Privacy & Transparency

Privacy & Transparency