Calcul nombre d'heures par mois dans intervalle date

Y compris Writer et toute autre question en lien avec les suites bureautiques Open Source
Avatar du membre
James007
Fanatique d'Excel
Fanatique d'Excel
Messages : 11'226
Appréciations reçues : 344
Inscrit le : 30 août 2014
Version d'Excel : 2007 EN

Message par James007 » 14 mai 2019, 10:26

Bonjour,

Content que ta première étape soit franchie ... :wink:
1 membre du forum aime ce message.
A+

:)

Quand on n’a qu’un marteau, tous les problèmes deviennent des clous…
G
GNIN
Membre habitué
Membre habitué
Messages : 73
Appréciations reçues : 6
Inscrit le : 6 février 2019
Version d'Excel : 2003
Version de Calc : 4.3

Message par GNIN » 14 mai 2019, 11:41

Re-Bonjour !

Suite à ta réponse et étant plutôt adepte du VBA, je verrais bien une fonction personnelle à laquelle on passerait 4 paramètres, à savoir: Date premier jour, Heure début, Date dernier jour, heure fin.

La fonction pourrait passer en revue au travers d'une boucle, toutes les dates une à une en commençant par premier jour + 1 en terminant par dernier jour - 1. Il s'agit là du cas trivial où on incrémente un compteur de 8h00 à chaque fois sauf s'il s'agit d'un week-end ou d'un jour férié. Pour les week-ends tu peux utiliser la fonction WeekDay avec 7 pour samedi et 1 pour dimanche.
Pour les jours fériés tu peux pour faire simple, créer une petite fonction qui te renvoie un booléen si c'est un jour férié. Dans cette fonction on peut enregistrer dans un tableau des jours fériés sur les 5 prochaines années par exemple. On peut aussi créer une fonction qui détermine automatiquement si une date quelle qu'elle soit est un jour férié, mais là c'est beaucoup plus compliqué car il faut déterminer le lundi de pâques, l'ascension et la pentecôte (pour les autres c'est invariable).

Concernant le premier et le dernier jour, il faut les traiter séparément en tenant compte des heures.

En appliquant cette méthode j'obtiens ceci (colonnes A à E et lignes 1 à 4):
Date début Heure début Date fin Heure fin Nb heures
02/01/2019 15:33 16/01/2019 17:46 76:41:00
12/09/2017 10:42 23/09/2017 15:29 68:48:00
02/01/2019 15:33 14/04/2019 17:46 577:27:00

La fonction est placée en colonne E (Nb heures) et l'appel se fait comme suit (par exemple):
=tempsdossier(A2;B2;C2;D2)

Question: Les résultats de la colonne "Nb heures" te paraissent-ils correspondre à ce que tu attends.

Cdt,
T
Taoufiq
Jeune membre
Jeune membre
Messages : 38
Inscrit le : 20 février 2019
Version d'Excel : 2010

Message par Taoufiq » 14 mai 2019, 12:33

Merci pour ton investissement sur le sujet GNIN,

Cette démarche est plutôt cohérente et correspond exactement à mon besoin de plus elle est simple d'utilisation (certes la conception doit être lourde)

Je check les résultats et te reviens,

à tte
T
Taoufiq
Jeune membre
Jeune membre
Messages : 38
Inscrit le : 20 février 2019
Version d'Excel : 2010

Message par Taoufiq » 14 mai 2019, 14:57

Re bonjour GNIN,

Après vérification, il me semble qu'il faut ajuster la fonction,

En partant du principe que le planning quotidien est : 08:30:00 - 12:30:00 // 14:00:00 - 18:00:00

Voilà ce que j'ai trouvé (sauf erreur)

02/01/2019 15:33 16/01/2019 17:46 82:13 (delta : 05:32:00) !
12/09/2017 10:42 23/09/2017 15:29 69:48 (delta : 01:00:00)
02/01/2019 15:33 14/04/2019 17:46 578:27 (delta : 01:00:00)

Merci,
G
GNIN
Membre habitué
Membre habitué
Messages : 73
Appréciations reçues : 6
Inscrit le : 6 février 2019
Version d'Excel : 2003
Version de Calc : 4.3

Message par GNIN » 14 mai 2019, 15:57

Ok ! En modifiant les plages horaires, j'obtiens les mêmes chiffres sauf sur une ligne. En creusant un peu je me suis rendu compte que j'avais fait une petite erreur dans le code. Sur une des lignes j'avais comme borne 18:00 au lieu de 14h00.
Du coup tout est réparé (enfin j'espère !) et je trouve exactement les mêmes chiffres que toi.

Je te communique ma fonction. Pour reprendre ton message précédent ce n'est pas de la programmation très lourde mais juste un peu complexe. J'ai mis quelques commentaires pour que ce soit un peu plus clair.
Function TempsDossier(JD As Range, HD As Range, JF As Range, HF As Range) As Variant
    Dim J As Date
    Dim H As Variant
    Dim TOT As Variant
    For J = JD To JF
        Select Case J 'selon la date
            Case JD 'Si premier jour
                If Not JF(J) And Weekday(J) <> 1 And Weekday(J) <> 7 Then 'Vérifie si jour férié ou WE
                    'Si heure début = Après-midi
                    If CDate(HD) >= CDate("14:00:00") Then
                        'TimeSerial pour totaliser en minutes
                        H = TimeSerial(0, DateDiff("n", CDate(HD), CDate("18:00")), 0)
                        TOT = TOT + H
                    Else
                    'Sinon heure début = Matin
                        H = TimeSerial(0, DateDiff("n", CDate(HD), CDate("12:30")), 0)
                        TOT = TOT + H + CDate("04:00:00")
                    End If
                End If
            Case JF 'Si dernier jour
                If Not JF(J) And Weekday(J) <> 1 And Weekday(J) <> 7 Then 'Vérifie si jour férié ou WE
                    'Si heure fin = Après-midi
                    If CDate(HF) >= CDate("14:00:00") Then
                        H = TimeSerial(0, DateDiff("n", CDate("14:00"), CDate(HF)), 0)
                        TOT = TOT + H + CDate("04:00:00")
                    Else
                        'Sinon heure fin = Matin
                        H = TimeSerial(0, DateDiff("n", CDate("08:30:00"), CDate(HF)), 0)
                        TOT = TOT + H
                    End If
                End If
            Rem
            Case Else
                'Dans tous les autres cas
                'on comptabilise une journée de 8:00
                If Not JF(J) And Weekday(J) <> 1 And Weekday(J) <> 7 Then 'Vérifie si jour férié
                    TOT = TOT + CDate("08:00:00")
                End If
        End Select
    Next J
    TempsDossier = TOT
End Function
Comme indiqué dans mon message précédent, il faut une petite fonction qui vérifie si un jour est férié ou pas. C'est une version simplifiée où il faut saisir les jours fériés. Je l'ai fait pour 2019 uniquement. Cela a l'avantage de pouvoir rajouter des jours autres (pont, journée offerte par le patron :mrgreen: , ...). Si tu en rajoutes pense à modifié le dimensionnement du tableau. Ici j'ai écrit la valeur 11 en dur.
Public Function JF(D As Date) As Boolean
    Dim TB(11) As Date
    Dim i As Integer
    TB(1) = "01/01/2019"
    TB(2) = "22/04/2019" 'Pâques
    TB(3) = "01/05/2019"
    TB(4) = "08/05/2019"
    TB(5) = "30/05/2019" 'ascension
    TB(6) = "10/06/2019" 'Pentecôte
    TB(7) = "14/07/2019"
    TB(8) = "15/08/2019"
    TB(9) = "01/11/2019"
    TB(10) = "11/11/2019"
    TB(11) = "25/12/2019"
    JF = False
    For i = 1 To 11
        If D = TB(i) Then
            JF = True
            Exit For
        End If
    Next i
End Function
Concernant les cellules sur la feuille excel, pense à les formater en heure "37:30:55" (je crois que c'est le format proposé).

Concernant le détail par mois, j'ai pensé à une modification de la fonction avec un paramètre optionnel du style:
Function TempsDossierMois(JD As Range, HD As Range, JF As Range, HF As Range, Optional M As Variant) As Variant
Si on ne précise rien, ce sera comme ci-dessus. Si par exemple on précise 2, la fonction ne totalisera que les jours du mois de février. Je te fais ça dès que j'ai un peu de temps, sauf évidemment si tu te sens de faire. Cela te permettra de créer une colonne par mois de janvier à décembre et d'avoir ainsi le détail.

Ce qu'on devrait obtenir après modification de la fonction:
CE Forum.jpg
A+
G
GNIN
Membre habitué
Membre habitué
Messages : 73
Appréciations reçues : 6
Inscrit le : 6 février 2019
Version d'Excel : 2003
Version de Calc : 4.3

Message par GNIN » 14 mai 2019, 16:11

Finalement c'est moins long que prévu:

Voici la fonction modifiée:
Function TempsDossierMois(JD As Range, HD As Range, JF As Range, HF As Range, Optional M As Variant) As Variant
    Dim J As Date
    Dim H As Variant
    Dim TOT As Variant
    For J = JD To JF
        Select Case J
            Case JD
                If Not JF(J) And Weekday(J) <> 1 And Weekday(J) <> 7 Then 'Vérifie si jour férié
                    If CDate(HD) >= CDate("14:00:00") Then
                        H = TimeSerial(0, DateDiff("n", CDate(HD), CDate("18:00")), 0)
                        If Not IsMissing(M) Then
                            If Month(J) = M Then
                                TOT = TOT + H
                            End If
                        Else
                            TOT = TOT + H
                        End If
                    Else
                        H = TimeSerial(0, DateDiff("n", CDate(HD), CDate("12:30")), 0)
                        If Not IsMissing(M) Then
                            If Month(J) = M Then
                                TOT = TOT + H + CDate("04:00:00")
                            End If
                        Else
                            TOT = TOT + H + CDate("04:00:00")
                        End If
                    End If
                End If
            Case JF
                If Not JF(J) And Weekday(J) <> 1 And Weekday(J) <> 7 Then 'Vérifie si jour férié
                    If CDate(HF) >= CDate("14:00:00") Then
                        H = TimeSerial(0, DateDiff("n", CDate("14:00"), CDate(HF)), 0)
                        If Not IsMissing(M) Then
                            If Month(J) = M Then
                                TOT = TOT + H + CDate("04:00:00")
                            End If
                        Else
                            TOT = TOT + H + CDate("04:00:00")
                        End If
                    Else
                        H = TimeSerial(0, DateDiff("n", CDate("08:30:00"), CDate(HF)), 0)
                        If Not IsMissing(M) Then
                            If Month(J) = M Then
                                TOT = TOT + H
                            End If
                        Else
                            TOT = TOT + H
                        End If
                    End If
                End If
            Rem
            Case Else
                If Not JF(J) And Weekday(J) <> 1 And Weekday(J) <> 7 Then 'Vérifie si jour férié
                    If Not IsMissing(M) Then
                            If Month(J) = M Then
                                TOT = TOT + CDate("08:00:00")
                            End If
                        Else
                            TOT = TOT + CDate("08:00:00")
                        End If
                End If
        End Select
    Next J
    TempsDossierMois = TOT
End Function
Attention, j'ai renommé la fonction pour éviter toute erreur de manip TempsDossierMois
Exemple d'appel pour un total général:
=tempsdossierMois(A4;B4;C4;D4)

Exemple d'appel pour totaliser un mois précis (ex: février)
=tempsdossierMois($A4;$B4;$C4;$D4;2)

Dans le cas où la durée serait sur plusieurs années, il faudrait faire une petite correction en incluant l'année, sinon la fonction totaliserait par exemple tous les mois de février de toutes les années dans l'intervalle;

Cdt,


=tempsdossierMois(A4;B4;C4;D4)
Avatar du membre
James007
Fanatique d'Excel
Fanatique d'Excel
Messages : 11'226
Appréciations reçues : 344
Inscrit le : 30 août 2014
Version d'Excel : 2007 EN

Message par James007 » 14 mai 2019, 17:53

Bonjour à tous,

Je repasse sur le sujet pour constater que vous êtes maintenant partis sur une fonction personnalisée ....

@ GNIN Très joli code ... :clap: ... que je n'ai pas encore eu l'occasion de tester ... Mais ...

Alors pour le coup ... pourquoi ne pas rendre ta fonction totalement générique...

et extraire les horaires pour en faire des variables ... ::)
A+

:)

Quand on n’a qu’un marteau, tous les problèmes deviennent des clous…
T
Taoufiq
Jeune membre
Jeune membre
Messages : 38
Inscrit le : 20 février 2019
Version d'Excel : 2010

Message par Taoufiq » 15 mai 2019, 09:45

Bonjour GNIN,

Le code est propre et le test est concluant, un joli travail Bravo !

En effet il serait bien d'adapter la fonction à la contrainte 'Année' histoire d'anticiper,

@James007 merci à toi pour ton implication et suivi,

Un grand merci
G
GNIN
Membre habitué
Membre habitué
Messages : 73
Appréciations reçues : 6
Inscrit le : 6 février 2019
Version d'Excel : 2003
Version de Calc : 4.3

Message par GNIN » 15 mai 2019, 09:55

Bonjour James,

Tout à fait exact, la fonction pourrait être totalement paramétrée. J'avoue ne pas aimer inscrire des valeurs en dur dans le code.
Dans ce cas précis je me suis concentré sur l'algorithme.

Je reverrai ça à mon retour de vacances. Je pars demain une semaine en Crète :)

Bien à vous tous !
T
Taoufiq
Jeune membre
Jeune membre
Messages : 38
Inscrit le : 20 février 2019
Version d'Excel : 2010

Message par Taoufiq » 15 mai 2019, 10:23

Bonnes vacances

Enjoy :)
Répondre Sujet précédentSujet suivant
  • Sujets similaires
    Réponses
    Vues
    Dernier message