Calcul nombre d'heures par mois dans intervalle date

Bonjour,

En voilà un cas !

Un dossier est ouvert à une date x sous le format JJ/MM/AAAA HH:MM et clôturé plus tard JJ+n/MM+n/AAAA+n HH:MM,

Nous avons besoin de calculer le nombre d'heures allouées au traitement du du dossier par mois,

Cas de figure :

Pour un dossier 001 ouvert le 02/01/2019 15:33 clos le 14/04/2019 17:46, à savoir :

  • Planning quotidien : 07:30 - 11:30 // 13:00 - 17:00
  • Hors weekend et jours fériés

Q1 : Nous avons besoin de savoir le nombre d'heures allouées à ce traitement pour chaque mois.

Q2 : Quelle disposition pourrions nous avoir pour présenter les données,

Merci

Bonjour,

Concernant l'organisation des données, la bonne solution me semble être une base de données classique, avec 1 ligne = 1 dossier. Quelque chose du genre :

N°dossierNomdate_heure_debutdate_heure_fin...

Bien que ton sujet soit dans la section "calc", ton profile indiques que tu disposes d'Excel. Sous Excel, tu as une fonction appelée NB.JOURS.OUVRES() qui permet de calculer un nombre (entier) de jours ouvrés entre 2 dates, avec possibilité d'inclure une liste de jours fériés (3ème argument, facultatif). Cette formule peut t'aider, sinon je pense que c'est faisable avec une fonction personnalisée VBA (pour éviter une formule complexe et à rallonge).

Je revient avec une proposition...

Bonjour Pedro,

Nous sommes en phase, en effet c'est un sujet de calcul sous excel,

En ce qui concerne la disposition nous pouvons aussi lisser les mois de l'année en colonnes comme ça nous avons le nombre d'heures par mois...

La création de formules personnalisées n'est pas mon fort

Merci,

J'ai planché sur une formule (qui est assez compliquée !!) :

10taoufiq.xlsx (16.33 Ko)

Pour qu'elle fonctionne :

  • Les heures de début ou fin doivent être comprises dans une plage de travail (sinon les heures de cette journée sont omises)
  • Les horaires de travail sont à indiquer dans le fichier sous forme numérique (7,5 pour 7h30 par exemple), mais peuvent aussi être inscrites "en dur" dans la formule
  • Attention à tenir à jour la plage des jours fériés

J'ai aussi travaillé sur une macro qui fait le même travail pour avoir une formule beaucoup plus simple, mais elle ne donne pas toujours le résultat souhaité et je n'ai pas encore compris pourquoi. Elle n’apparaît donc pas dans ce premier fichier.

Merci pour ton investissement sur le sujet,

Je suis déjà parvenu à ce niveau de calcul avec une autre méthode

Le hic c'est le découpage de ces heures entre les mois,

Prenons l'exemple du dossier n° 2, combien de temps de traitement passé sur chaque mois à savoir Avril et Mai puis extrapolons sur une durée plus longue (ex. début en Janvier et fin en Mai)

Merci pour ton investissement sur le sujet,

Je suis déjà parvenu à ce niveau de calcul avec une autre méthode

Le hic c'est le découpage de ces heures entre les mois,

Prenons l'exemple du dossier n° 2, combien de temps de traitement passé sur chaque mois à savoir Avril et Mai puis extrapolons sur une durée plus longue (ex. début en Janvier et fin en Mai)

Si tu veux un décompte approximatif, tu peux diviser le résultat par la durée en mois du projet.

Si tu veux un décompte exact, il faudrait procéder autrement.

La solution la plus simple qui me vient est de garder la même structure, mais de découper un dossier en 1 ligne par mois à l'aide d'une macro. Le résultat pourrait être transféré sur un nouvel onglet pour ne pas écraser la structure initiale.

Un TCD te permet ensuite de visualiser tes données (par dossier, par mois... selon ce que tu souhaites).

Le décompte approximatif n'est pas la meilleure solution (nous cherchons des résultats précis)

L'idée de créer une table de retraitement me parait logique est intéressante reste à monter la macro adéquate (j'ai cherché mais en vain)

A tester :

Sub DiviserLignes()

Dim LigMax As Long, i As Long, m As Integer, j As Integer, D2 As Date

With Sheets("Feuil2")
    .Cells.ClearContents 'Suppression anciennes données
    Sheets("Feuil1").Range("Tableau1[#All]").Copy .Range("A1") 'Copie du tableau en feuille 2
    LigMax = .Range("A" & Rows.Count).End(xlUp).Row 'Détermine la dernière ligne
    For i = LigMax To 2 Step -1 'Boucle sur les lignes, en partant de la dernière
        m = Month(.Range("E" & i)) - Month(.Range("D" & i)) 'Calcul du nombre de mois du dossier
        If m > 0 Then 'Si plus d'un mois
            For j = 1 To m 'Boucle sur les mois
                .Range("A2").ListObject.ListRows.Add 'Ajout d'une ligne
                LigMax = LigMax + 1
                D2 = DateAdd("m", j, .Range("D" & i)) 'Calcul de la date du mois suivant
                .Range("A" & LigMax) = .Range("A" & i) 'Report du numéro de dossier
                .Range("D" & LigMax) = DateSerial(Year(D2), Month(D2), 1) + Sheets("Feuil1").Range("H2") / 24 'Ajout de la date de début de mois suivant
                If j = m Then .Range("E" & LigMax) = .Range("E" & i) Else .Range("E" & LigMax) = Int(DateAdd("m", 1, .Range("D" & LigMax))) + Sheets("Feuil1").Range("K2") / 24 - 1 'Ajout de la date de fin
            Next j
            D2 = DateAdd("m", 1, .Range("D" & i))
            .Range("E" & i) = DateSerial(Year(D2), Month(D2), 1) + Sheets("Feuil1").Range("K2") / 24 - 1 'Correction de la date de fin de la ligne d'origine
        End If
    Next i
End With

End Sub
8taoufiq2.xlsm (35.85 Ko)

Bonjour,

Pour ne parler que de l'allocation des heures par mois ... sans tenir compte des horaires de travail ...

un fichier test joint ...

Merci pour votre aide les gars,

J'ai les neurones qui chauffent, je continue les vérifications demain et vous reviens,

PS : @James007 c'est la disposition que j'ai imaginé avant de poster ma question (TOP) mais à première vue la somme des mois ne correspond pas à la valeur des cellules de la colonne F

@Pedro : sauf erreur la macro fonctionne mais il me semble avoir remarqué des écarts au niveau de la formule,

Anyway i'll check & tell you tomorrow

Re,

Il ne faut surtout pas me demander pourquoi un zéro est devenu 1 à la recopie ... !!!

A tester ...

hhhhhhh merci James,

oui j'ai vu le plus important c'est que ça marche

Merci

Content que cela fonctionne ... comme prévu ...

Hello James,

A vrai dire cela ne fonctionne pas comme prévu, je me suis précipité après avoir fait un test rapide,

La formule fonctionne dans certains cas mais pas pour d'autres! J'ai beau cherché l'erreur mais walou

Bonjour,

" çà ne marche pas ... " est un commentaire beaucoup trop vague ... pour être utile ...

Il serait plus judicieux de joindre un fichier test qui souligne les erreurs ... ainis que les résultats attendus ...

Hello,

Oui tu as bien raison ,

J'ai rajouté une colonne pour vérifier si la somme des heures par mois correspond à la différence entre date fin et date début, la MFC nous montre que pour certains cas il y a incohérence,

Merci,

Bonjour,

Merci pour ton fichier test ...

Dès que j'ai un petit moment ... cela va me permettre de creuser ...

Re,

LA seule question qui est vraiment importante :

Sommes-nous passés au Vert ... ???

Ci-joint ton Check File ...

Bonjour,

Je m'insère dans cette discussion car j'ai déjà eu à travailler sur des décomptes horaires de ce type.

J'ai une petite question concernant le décompte. En effet, au travers des réponses qui ont été faites, j'ai l'impression que l'on comptabilise des journées de 24 heures (ex sur le fichier joint en première ligne : 268:47 pour les dates du 12/09/2017 10:42 au 23/09/2017 15:29).

Dans la mesure où tu ne veux pas tenir compte des week-ends et jours fériés, je me demandais s'il ne fallait pas plutôt s'en tenir uniquement aux heures de travail possibles (sauf si vous travaillez la nuit ) correspondant à la plage que tu as précisée dans ton premier post soit 7h30 à 11h30 et 13h00 à 17h00. Ce qui représente 8 heures par jour ouvré.

Pourrais-tu répondre à cette question car le problème n'est plus tout à fait le même.

Cdt,

Bonjour GNIN,

Bien vu !

En effet mon besoin initial est de lisser la différence entre l’horodatage début et fin sur les mois en tenant compte du planning,

A ce stade, grâce à James007, nous avons réussi par avoir le décompte par mois (H24), je continue mes recherches pour coller au besoin initial, à moins que si tu peux nous éclairer GNIN,

@James007, je confirme que c'est vert, bravo ! Maintenant je dois caler cela aux plannings de travail

Merci,

Rechercher des sujets similaires à "calcul nombre heures mois intervalle date"