[VBA] calcul particulier heures de nuit à déduire

Bonjour à tous,

Je fais suite au sujet posté ici, pour lequel nous avions avancé sans que pour autant les choses soient réglées.

J'ai préparé un fichier exemple beaucoup plus proche de la réalité :

22exemple-forum.zip (596.23 Ko)

Dans les colonnes impaires, nous avons des horaires. De minuit (0) à 7 ou 8h du matin (lignes 4-5), et de 21h15 à minuit (1) lignes 8-9.
Dans les colonnes paires, un petit calcul qui ne nous occupera pas ici.

Il faut que je calcule du tant au tant de chaque mois le nombre d'heures effectuées (en saumon à la verticale ici). C'est donc la somme de toutes les données ligne 9 - ligne 8 + ligne 7 - 6 + ligne 5 - 4.

La ligne 11 sert à indiquer que la personne n'est pas là. Si elle est remplie par du texte, il faut donc ne pas additionner les heures de ce jour-là pour le soir, mais pas non plus pour le lendemain matin. Sauf que si on met le code "AM", les heures doivent compter quand même.

La solution de Bibunesco, que je remercie encore pour son aide, fonctionne très bien pour le 1er tronçon mais pas pour les suivants. Et puis le résultat se met en erreur dès qu'on effectue une action sur une autre feuille (double cliquez sur n'importe quelle cellule de la feuille NOM pour vous en rendre compte). Un double clic dans la feuille planning force le recalcul et l'erreur disparaît.

Je tourne en rond là-dessus et si quelqu'un a une solution viable, je suis vraiment preneur.

Par avance un immense merci.

Salut JoyeuxNoël,

à quelques mois de la retraite, je suis focus plein temps sur un projet pour les collègues pour pérenniser notre agenda Excel, raison pour laquelle je suis absent sur le forum depuis un bout de temps.

Cela dit, comme je comprends l'affaire, il te faut simplement le cumul d'heures entre "tant et tant", sans addition de "tronçon à tronçon" ?

Je regarde ça : je te le dois bien!


A+

Oh non, tu ne me dois vraiment rien ! Mais ça serait sympa de ta part de te pencher dessus effectivement 😁

C'est ça, il faut faire le total des heures effectuées du 21 au 20 de chaque mois. Sauf pour janvier où on fait du 1er au 20, et décembre où on termine l'année.

L'idée est de récupérer le nombre d'heures techniquement effectuées. Les congés du personnel de l'asso sont déjà déduits par ailleurs, il faut donc ne déduire que les heures techniquement non effectuées, donc d'arrêt maladie, motif AM.

Tout ceci, qui complique ce (vaste) projet dans des proportions ahurissantes, pour que les membres de l'asso aient leur paie quelques jours plus tôt que la normale 😊.

Encore merci par avance.

Bonjour,

Si dans un 1er temps quelqu'un voit pourquoi la fonction personnalisée renvoie une erreur, je suis preneur de l'info. La raison m'échappe totalement.

Salut JoyeuxNoël,

j'avoue être perplexe...

Sauf que si on met le code "AM", les heures doivent compter quand même.

j'aimerais être certain de bien comprendre les modalités de calcul en cas d'absence. Comme j'ai moi-même un horaire exclusif nuit, cela ne devrait pas être long à s'entendre.

En cas de code en ligne 11, ne pas additionner les heures de cette journée, donc celles après 8:00 du mat', et de la nuit à venir jusqu'au matin suivant ?


A+

Salut Curulis,

Merci de te pencher à nouveau dessus.

C'est exactement ça. Si on met une absence pour un jour, cela concerne de 21h15 à minuit du jour-même, et de minuit à 8h du lendemain.

S'il n'y avait pas eu cette histoire de lendemain à prendre en compte, c'était jouable via formules. Mais là, ça complique trop sans VBA.

D'ailleurs, les 2 lignes entre 0h00-8h00 et 21h15-0h00 sont utilisées pour marquer des temps de formation ou autre qui eux, seront à prendre en compte tout le temps.

Ce que fait quasiment la fonction actuelle d'ailleurs, à quelques soucis près :)

Salut JoyeuxNoël,

seront à prendre en compte tout le temps.

Chez nous, quand on est absent, on est absent à temps plein et pas à mi-temps !!
Je dois donc bien comprendre qu'absent ou non, ces heures de journée DOIVENT compter !?

Question code (p...t... d'heures et dates chez Microsoft ), grâce à un code de Theze de 2016, j'ai trouvé le moyen de calculer facilement les heures.
Bizarrerie, cela fonctionne d'un côté sans souci mais, d'un autre, quand j'utilise un tableau, ça ne veut plus rien entendre !!
Accessoirement, mes résultats ne coïncident pas avec la fonction de ton exemple mais bien avec le calcul manuel pur.

Donc, je me prends le chou pour mettre ça au point !

Joie!


A+

Chez nous, quand on est absent, on est absent à temps plein et pas à mi-temps !!

Et vous avez bien raison ! Sauf qu'ici, la cellule sera utilisée pour matérialiser des temps de formation, ou réunion, etc. Le code indiquera donc que la personne ne fait pas ses heures normales mais, à la place effectue les horaires indiqués au milieu. Donc tu as bien compris, les heures du milieu doivent compter tout le temps, qu'il y ait un code ou non.

Tu as bien résumé, c'est la misère ces horaires. Et étant plus à l'aise avec les formules qu'avec le VBA, je ne te raconte pas la galère que c'est pour moi, si toi tu t'en vois déjà

Bon, ça roule! Devrait être bon...

Dernière question qui me vient à l'esprit : comment repères-tu une absence en nuit le 31 décembre pour le calcul du 1er janvier ?
Inscris-tu un "AM" ou autre code en [A] sur la même ligne ?


A+

Les fichiers ne communiqueront pas d'une année sur l'autre, n'abusons pas des bonnes choses.

Si quelqu'un décide d'aller faire la fête le 31 décembre au lieu d'aller travailler (ça se fait encore, ça, la fête ?), on met le motif pour la nuit du 31 et je pense qu'on effacera sauvagement les horaires de la personne le 1er janvier sur le fichier de l'année d'après.

Qu'en penses-tu ?

C'est toi le patron... surtout dans cette période de fin d'année!

Salut JoyeuxNoël,

la fonction revisitée..

Public Function fctHeures()
'
Dim rCel As Range, rCel1 As Range, dDate As Date, iCol%, iOK%, iH%, iM%
'
Application.Volatile
Application.ScreenUpdating = False
Set rCel = Application.Caller
'
With Worksheets("Planning")
    iCol = .Range("A3:" & fctCol(rCel.Column - 1) & 3).Find(what:="Total heures", lookat:=xlWhole, LookIn:=xlValues, searchdirection:=xlPrevious).Column + 2
    For y = iCol To rCel.Column - 2 Step 2
        For x = rCel.Row To rCel.Row + 4 Step 2
            If .Cells(x, y) <> "" Then
                iOK = 0
                If x = rCel.Row Then _
                    If .Cells(rCel.Row + 7, IIf(y = iCol, IIf(iCol = 3, 1, iCol - 4), y - 2)) = "" Then iOK = 1
                If x = rCel.Row + 4 And .Cells(rCel.Row + 7, y) = "" Then iOK = 1
                If x = rCel.Row + 2 Then iOK = 1
                If iOK = 1 Then _
                    Set rCel1 = .Cells(x, y): _
                    dDate = CDate(rCel1.Offset(1, 0)) - CDate(rCel1): _
                    iH = iH + CInt(Split(dDate, ":")(0)): _
                    iM = iM + CInt(Split(dDate, ":")(1))
            End If
        Next
    Next
End With
fctHeures = Format(iH + Fix(iM / 60), "00") & " : " & Format(iM - IIf(iM < 60, 0, Fix(iM / 60) * 60), "00")
'
Application.ScreenUpdating = True
'
End Function

Pour info, j'ai prévu que tu puisses quand même signaler une absence le 31/12 en inscrivant un code sur la 2e ligne sous le NOM (donc, même ligne que les autres codes d'absence).

À toi de tester dans tous les sens.


A+

5joyeuxnoel.zip (577.35 Ko)

Hey, merci beaucoup pour ce travail !!

Plusieurs remarques :

1- J'en comprends quelques morceaux, mais certaines parties restent un peu obscures. Je vais essayer de tout décortiquer encore mieux ...

2- Une partie justement que j'ai du mal à bien détailler est :

fctHeures = Format(iH + Fix(iM / 60), "00") & " : " & Format(iM - IIf(iM < 60, 0, Fix(iM / 60) * 60), "00")

Si on regarde dans la colonne O de la feuille NOM, on voit que le résultat de cette fonction n'est pas numérique. L'addition de tout ceci donne 0 et ça risque de poser souci :)
Comme tout est déclaré en string, c'est normal. J'ai essayé de bidouiller par-ci par-là, mais ça n'est pas concluant.

3- Comprends-tu ce qui créait le plantage dans la fonction précédente ? Si non, pas grave, c'est juste pour essayer de comprendre.

4- SI quelqu'un est en arrêt maladie, c'est comme s'il avait travaillé ces heures-là. D'où la nécessité de ne pas déduire les heures si le motif est "AM". Ce critère n'est pas pris en compte actuellement et il est pile dans la partie que j'ai du mal à bien comprendre pour le moment. Mais je ne désespère pas :)

En tout cas, un immense merci. Les bases sont très bonnes et on touche au but. Enfin on ... toi !

Salut JoyeuxNoël,

image

Combien d'heures là-dedans ? 56:45, si AM ne doit pas être déduit et les heures de journée comptées quel que soit le code ?

Non, je n'ai pas cherché l'origine de l'erreur : pas assez calé pour comprendre ce genre de truc!

J'ai rectifié le résultat renvoyé par fctHeures() mais ton addition en 'NOM' [O:O] persiste sur 0... Va falloir tûzer l'affaire!


A+

C'est ça, 56:45.

Et si au lieu du 1er "AM" on avait un "CA" (ou autre), cela ferait 9:45 de moins.

Bon, j'aurai appris ce que veut dire tûzer, en plus du reste. Je n'ai vraiment pas perdu ma soirée ! :)

Tûzons alors, tûzons. Parce qu'il me le faut vraiment en valeur numérique. Parce que s'embêter à ce point pour savoir combien d'heures ça fait et ne pas pouvoir les additionner à la fin, c'est quand même dommage

Edit : Bon bah j'ai tûzé, j'ai mis *1 derrière ma formule en colonne O et c'est bon . Du coup tu peux continuer à générer du texte, c'est pas grave, on transforme ça en nombre à la fin. Avant de m'emballer pleinement, je vais quand même essayer de vérifier un peu mieux.

Voilà déjà la fonction corrigée...

Public Function fctHeures()
'
Dim rCel As Range, rCel1 As Range, dDate As Date, iCol%, iOK%, iH%, iM%
'
Application.Volatile
Application.ScreenUpdating = False
Set rCel = Application.Caller
'
With Worksheets("Planning")
    iCol = .Range("A3:" & fctCol(rCel.Column - 1) & 3).Find(what:="Total heures", lookat:=xlWhole, LookIn:=xlValues, searchdirection:=xlPrevious).Column + 2
    For y = iCol To rCel.Column - 2 Step 2
        For x = rCel.Row To rCel.Row + 4 Step 2
            If .Cells(x, y) <> "" Then
                iOK = 0
                If x = rCel.Row Then _
                    If .Cells(rCel.Row + 7, IIf(y = iCol, IIf(iCol = 3, 1, iCol - 4), y - 2)) = "" Or _
                        .Cells(rCel.Row + 7, IIf(y = iCol, IIf(iCol = 3, 1, iCol - 4), y - 2)) = "AM" Then iOK = 1
                If x = rCel.Row + 4 And (.Cells(rCel.Row + 7, y) = "" Or .Cells(rCel.Row + 7, y) = "AM") Then iOK = 1
                If x = rCel.Row + 2 Then iOK = 1
                If iOK = 1 Then _
                    Set rCel1 = .Cells(x, y): _
                    dDate = CDate(rCel1.Offset(1, 0)) - CDate(rCel1): _
                    iH = iH + CInt(Split(dDate, ":")(0)): _
                    iM = iM + CInt(Split(dDate, ":")(1))
            End If
        Next
    Next
End With
fctHeures = Format(iH + Fix(iM / 60), "00") & ":" & Format(iM - IIf(iM < 60, 0, Fix(iM / 60) * 60), "00")
'
Application.ScreenUpdating = True
'
End Function

Je cherche pour cette addition... la nuit est à nous!


A+

J'ai édité mon message pendant que tu écrivais. C'est bon pour l'addition.

Je regarde la fonction. Merci énormément !

Bon sang, je suis tout émotionné. Ça a l'air de fonctionner à merveille. Je vais essayer d'implémenter ça sur le vrai fichier.

Je nage complètement sur cette partie par contre , ça dépasse mon niveau de compréhension actuel.

 If x = rCel.Row Then _
                    If .Cells(rCel.Row + 7, IIf(y = iCol, IIf(iCol = 3, 1, iCol - 4), y - 2)) = "" Or _
                        .Cells(rCel.Row + 7, IIf(y = iCol, IIf(iCol = 3, 1, iCol - 4), y - 2)) = "AM" Then iOK = 1
                If x = rCel.Row + 4 And (.Cells(rCel.Row + 7, y) = "" Or .Cells(rCel.Row + 7, y) = "AM") Then iOK = 1
                If x = rCel.Row + 2 Then iOK = 1
                If iOK = 1 Then _

Si tu as le temps pour quelques explications, je suis preneur.

En tout cas, encore merci ! Je te redis si ça fonctionne en condition réelle !

Trouvé !
C'était bête comme chou!

C'est à présent correct dans la feuille 'NOM'
En prime, quelques commentaires...

Public Function fctHeures()
'
Dim rCel As Range, rCel1 As Range, dDate As Date, dDate1 As Date, iCol%, iOK%, iH%, iM%
'
Application.Volatile
Application.ScreenUpdating = False
'cellule appelante
Set rCel = Application.Caller
'
With Worksheets("Planning")
    'recherche de la 2e colonne (+2) après le précédent (searchdirection:=xlPrevious) "Total Heures" selon cellule appelante
    iCol = .Range("A3:" & fctCol(rCel.Column - 1) & 3).Find(what:="Total heures", lookat:=xlWhole, LookIn:=xlValues, searchdirection:=xlPrevious).Column + 2
    'boucle de iCol à la 2e colonne (-2) précédant le "Total heures" de la cellule appelante
    For y = iCol To rCel.Column - 2 Step 2
        'boucle du n° de ligne de rCel à cette ligne + 4 step 2
        For x = rCel.Row To rCel.Row + 4 Step 2
            'si cellule contient une heure
            If .Cells(x, y) <> "" Then
                'pas de calcul si iOK=0
                iOK = 0
                'si 1ère ligne de la journée et...
                '...si la cellule-code absence du jour précédant ="" ou "AM" -> calcul des heures
                '......si y=iCol et iCol=3 (1er jour de l'année) -> cells(ligne-code, 1)
                '...................iCol>3 (autre 1er jour d'un tronçon) -> cells(ligne-code, iCol - 4)
                '...........................car le dernier jour du tronçon précédant est 4 colonnes en arrière
                '......si y>iCol, donc en plein tronçon -> cells(ligne-code, y -2) car la journée précédante n'est que 2 colonnes en arrière
                If x = rCel.Row Then _
                    If .Cells(rCel.Row + 7, IIf(y = iCol, IIf(iCol = 3, 1, iCol - 4), y - 2)) = "" Or _
                        .Cells(rCel.Row + 7, IIf(y = iCol, IIf(iCol = 3, 1, iCol - 4), y - 2)) = "AM" Then iOK = 1
                '
                'si 5e ligne de la journée (début nuit) et cell(ligne-code, y) du jour : "" ou "AM" -> calcul des heures
                If x = rCel.Row + 4 And (.Cells(rCel.Row + 7, y) = "" Or .Cells(rCel.Row + 7, y) = "AM") Then iOK = 1
                '
                'si 3e ligne de la journée (milieu de la journée), d'office calcul des heures
                If x = rCel.Row + 2 Then iOK = 1
                'si calcul
                '...soustraction entre les horaires qui sont en fait des dates (1/1/1900)
                '...et addition de ces différences dans dDate1 = total
                If iOK = 1 Then _
                    Set rCel1 = .Cells(x, y): _
                    dDate = CDate(rCel1.Offset(1, 0)) - CDate(rCel1): _
                    dDate1 = dDate1 + dDate
            End If
        Next
    Next
End With
'finalement, trouvaille en trifouillant partout -> correct dans ta feuille 'NOM'
fctHeures = dDate1 - TimeValue("01/01/1900 00:00:00")
'
Application.ScreenUpdating = True
'
End Function

Il va falloir tûzer un peu plus pour bétonner le dernier calcul, histoire de pallier un inattendu changement de "date" dans la configuration de tes horaires (1/1/1900).


A+

Merci pour ces commentaires qui éclairent ma lanterne !

Jusqu'à preuve du contraire, ça répond parfaitement à ma demande. Je vais essayer de tester ça dans tous les sens, mais j'ai confiance !

Rechercher des sujets similaires à "vba calcul particulier heures nuit deduire"