Calcul nombre d'heures par mois dans intervalle date
Bonjour,
Content que ta première étape soit franchie ...
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,
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
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,
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
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:
A+
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)
Bonjour à tous,
Je repasse sur le sujet pour constater que vous êtes maintenant partis sur une fonction personnalisée ....
@ GNIN Très joli code ...
Alors pour le coup ... pourquoi ne pas rendre ta fonction totalement générique...
et extraire les horaires pour en faire des variables ...
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
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 !
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 !
Bonjour à tous,
Avant toute chose ... Trés Bonnes Vacances ...
Compte tenu du travail que tu as déjà effectué ... il serait vraiment dommage de ne pas mener ton idée à son terme .... en y intégrant tous les paramètres qui rendront ta fonction totalement générique ...
Bonjour,
On un un petit souci les gars, la fonction est Ko après applications sur la table
En effet, elle donne le bon résultat pour certains cas et des incohérences pour d'autres
En voilà quelques exemples :
@GNIN : aucune urgence on regarde ça après tes vacances
Bonjour,
Je viens de jeter un oeil à ton nouveau fichier ...
As-tu remarqué que toutes les erreurs ont un seul et unique point commun .....
L'heure de départ est systématiquement après la plage des horaires du jour ...
Ce n'est pas le cas James007,
Regarde les lignes 3 et 5,
Après ça reste aussi une hypothèse à traiter
Re,
Tu as entièrement raison ...
Et pour le coup, les erreurs à l'intérieur de la fonction sont plus ' graves ' que ce que je pensais ...
J'ai beau essayé de faire des modif (je suis loin de maîtriser le sujet VBA mais je tâtonne) sans résultat
Salut les gars,
Je consulte vos échanges depuis la salle d'embarquement à l'aéroport de Roissy. Mon avion est retardé. Je suis un peu frustré de ne pas pouvoir intervenir. Toutefois, avec une appli smartphone je peux voir la page du fichier transmis par taoufiq. A vue de nez je me rends compte que le problème apparaît lorsque l'ouverture et la clôture du dossier intervient le même jour. Ma fonction traite chaque journée indépendamment de JD a JF. Si on travaille sur le même jour, la boucle ne fait qu'un tour et du coup le calcul est faux. Cela ne me parait trop compliqué à traiter. Il faut tester en premier lieu si JD=JF. L'autre cas, comme l'écrit James doit être lié à l'heure de début hors de la plage quotidienne. Je vois ça à mon retour. A+
Salut GNIN
Le virus Excel est un truc vraiment Dingue ...
Surtout ne vas pas gâcher tes vacances pour des If ...Then ...
Ou soyons précis ...
If tu es En Vacances Then Enjoy ...
Bonjour à tous,
Avant d'investir du temps pour reprendre la fonction personnalisée ...
Une très rapide décomposition en formules ...
En espèrant que cela éclaire la question ...