Calcul très complexe d'heures !

Créer une discussion
Répondre

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 !

S'inscrire
A

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:
Trier par date Trier par votes
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:
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
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
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
Re : Calcul très complexe d'heures !

Re...
Bonsoir JCGL
(...) fonctions qui vont m'être d'une grande utilité au travail (...)
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
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:
Vote positif 0 Vote négatif
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
Cliquez pour agrandir...

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
 
Vote positif 0 Vote négatif
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
Cliquez pour agrandir...
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
 
Vote positif 0 Vote négatif
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.
Cliquez pour agrandir...


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

Pièces jointes

Vote positif 0 Vote négatif
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
 
Vote positif 0 Vote négatif
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- 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

B
  • Résolu(e)
Autres Tableau D'Heures Intérimaire
Réponses
9
Affichages
1 K
sylvanu
sylvanu
B
  • Résolu(e)
Autres Tableau Heures
Réponses
5
Affichages
908
JHA
J
Swayzee
  • Résolu(e)
Microsoft 365 Calcul pourboire prorata heure par rapport au régime de base
Réponses
8
Affichages
2 K
Jacky67
Jacky67
Novice38
  • Question Question
XL 2016 Menus déroulants avec fonction "SI" + calendrier automatique
Réponses
2
Affichages
2 K
Novice38
Novice38
Novice38
  • Question Question
XL 2016 Menus déroulants avec formules "SI" + calendrier automatique
Réponses
4
Affichages
1 K
Novice38
Novice38
D
  • Question Question
XL 2019 Problème de portabilité Fichier EXCEL
2
Réponses
23
Affichages
3 K
Staple1600
Staple1600
Novice38
Novice38
Réponses
2
Affichages
895
Novice38
Novice38
E
  • Question Question
calcul d'heures
Réponses
3
Affichages
2 K
Hasco
Hasco
B
  • Question Question
Report d'heures travaillées
2
Réponses
16
Affichages
2 K
Victor21
Victor21
E
  • Question Question
XL 2019 Comment appliquer un pourcentage différent en fonction d'une intervalle de valeurs
Réponses
5
Affichages
2 K
Elmarabout
E
S
  • Question Question
Calcul d'heure majorées
Réponses
1
Affichages
915
Dranreb
Dranreb
M
  • Question Question
fonction RECHERCHE pour un total d'heures
Réponses
12
Affichages
2 K
moreau.did
M
Green_Lemon
  • Question Question
XL 2010 Planning- Gestion des mois et des semaines
Réponses
11
Affichages
5 K
Marcv
M
D
  • Question Question
VBA : Insérer fonction SI complexe dans macro
Réponses
5
Affichages
1 K
Dimebag
D
T
  • Question Question
Calcul amplitude horaire
Réponses
9
Affichages
3 K
ROGER2327
ROGER2327
P
  • Question Question
Attribuer un code couleur à un mot dans une cellule contenant des phrases
Réponses
5
Affichages
2 K
thebenoit59
T
A
  • Question Question
XL 2013 Nombre d'heures travaillées Dimanche et Jours Fériés
Réponses
0
Affichages
2 K
Averell1976
A
S
  • Question Question
sommes d'heures "conditionnés"
Réponses
8
Affichages
876
sebseb
S
P
  • Question Question
Décroisage de matrice + fonctions (recherchev/equiv): calcul trop long
Réponses
8
Affichages
2 K
Promax
P
A
  • Question Question
Planning horaire et calcul des heures effectuée
Réponses
10
Affichages
3 K
arabaca
A
Retour