VBA - Calcul sur dates
Bonsoir,
EDIT
Donc le code ci-dessous est censé faire ça, seulement il ne prend pas en compte le jour. C'est à dire que si l'incident est à 23h et qu'il est résolu à 1h -> ne fonctionne pas.
Aussi, si l'incident est signalé à 23h le 5 du mois et est résolu à 23h30 le 6 du mois, il va donner une durée de rétablissement de 30 minutes (bien sûr si je donne une heure de rétablissement avant 23h il ne fonctionne pas non plus
Public Function dureeIncident(dateIncident, dateCloture, hOuverture, hFermeture)
Dim hIncident, hCloture, dureeJour, debugage As Double
hIncident = dateIncident - Int(dateIncident)
hCloture = dateCloture - Int(dateCloture)
If Day(dateIncident) < Day(dateCloture) Then
dureeIncident = (24 - (hFermeture - hOuverture)) + (hCloture - hIncident)
Else
If hIncident < hOuverture And hCloture > hFermeture Then
dureeIncident = (hCloture - hIncident) - (hFermeture - hOuverture)
Else
If hIncident > hOuverture And hIncident < hFermeture And dateCloture > hFermeture Then
dureeIncident = hCloture - hFermeture
Else: dureeIncident = hCloture - hIncident
End If
End If
End If
End FunctionVoilà mon problème, j'aimerai que mon code prenne correctement en compte ces contraintes simples mais je suis un grand débutant en info... La ligne : If Day(dateIncident) < Day(dateCloture) Then
dureeIncident = (24 - (hFermeture - hOuverture)) + (hCloture - hIncident)
ne fonctionne pas, je l'avais supprimé à l'origine mais finalement je me suis dit "pourquoi pas la laisser
Je laisse le fichier excel comportant la macro, le code concerné est à la fin.
Merci pour votre aide éventuelle.
Bien cordialement.
Bonjour,
dureeIncident = (24 - (hFermeture - hOuverture)) + (hCloture - hIncident)Ne serait-ce pas plutôt
dureeIncident = (1- (hFermeture - hOuverture)) + (hCloture - hIncident)ou bien mets ceci :
hIncident = 24 * (dateIncident - Int(dateIncident))
hCloture = 24*(dateCloture - Int(dateCloture))car dans ton code, les heures restent des fractions de jours
Bonjour,
Je viens d'essayer ta proposition mais ça ne fonctionne pas.
Avec ma correction ... peux-tu me donner un cas où cela ne fonctionne pas ?
Merci ça a l'air de fonctionner !
J'avais multiplié aussi par 24, ce qui ne fonctionnait pas.
Je vais tout de même multiplier les tests pour trouver une éventuelle faille
Alors un cas où ça ne fonctionne pas :
D:H signalement le 5 à 3h
D:H rétablissement le 6 à 6h01
Ne fonctionne pas, donne 00:01:00
ça ne fonctionne pas non plus quand je mets l'incident le 5 à 23h et la résolution le 6 à 1h.
Bonjour, Salut Steelson !
Pas vu dernière solution, mais je procèderais ainsi :
Function dureeIncident(dateIncident, dateCloture, hOuverture, hFermeture)
Dim hIncident, hCloture, duree
hIncident = dateIncident - Int(dateIncident)
hCloture = dateCloture - Int(dateCloture)
duree = IIf(hCloture >= hIncident, hCloture - hIncident, 1 + hCloture - hIncident)
If hIncident >= hFermeture And hCloture < hIncident And hCloture >= hFermeture Then _
duree = duree - (hFermeture - hOuverture)
If hIncident <= hOuverture And (hCloture < hIncident Or hCloture >= hFermeture) Then _
duree = duree - (hFermeture - hOuverture)
dureeIncident = duree + Int(dateCloture - dateIncident) * (1 - (hFermeture - hOuverture))
End FunctionNB- Si tu mets le résultat dans une cellule, format doit pouvoir afficher 24h et plus.
(Si tu dois typer des valeurs horaires sur un type numérique, inutile de typer Double, les valeurs horaires sont de type Single)
Cordialement.
edit :
Ne fonctionne pas, donne 00:01:00
Ça je pense que c'est dû au format de ta cellule, qui n'affiche pas 24:01:00 !
Piloob a écrit :Ne fonctionne pas, donne 00:01:00
Comme dit MFerrand (que je salue), question de format : [hh]:mm plutôt que hh:mm
Ensuite, je n'ai pas approfondi (je n'ai regardé que la colhrence et le 24 m'a alerté) ... quel est l'intérêt de le faire en VBA ?
Bonjour MFerrand,
Merci, ça semble fonctionner après avoir fait quelques tests.
Est-ce qu'il serait possible d'obtenir des commentaires sur le code s'il te plait ? Je ne comprends pas ce qu'il se passe après le IIF.
Steelson a écrit :Piloob a écrit :Ne fonctionne pas, donne 00:01:00
Comme dit MFerrand (que je salue), question de format : [hh]:mm plutôt que hh:mm
Ensuite, je n'ai pas approfondi (je n'ai regardé que la colhrence et le 24 m'a alerté) ... quel est l'intérêt de le faire en VBA ?
On peut le faire directement sous excel ? Je ne connais strictement rien à Excel/vba, ça me paraissait faisable très simplement en vba en fait (même si j'ai quasiment jamais codé de ma vie
Et il n'y a pas Python ou autre sur le pc du travail.
Bonjour,
Tu te fais un tableau de tous les cas qui peuvent se présenter, l'heureIncident pouvant se situer avant et jusqu'à l'heure d'ouverture (sur le même jour) ou à partir et après l'heure de fermeture (le même jour) ; ces 2 cas se subdivisent selon la position de l'heureCloture...
(NB- Il me semble en fait qu'on a tous inversé ouverture et fermeture ? Pas grave pour le résultat si l'inversion est générale...
Tu analyses chaque cas pour savoir quels cas impliquent de diminuer la durée de l'écart ouverture-fermeture, quand ce ne peut être comptabilisé par le dépassement d'une journée.
A partir de là tu peux donc faire la différence horaire brute, que tu diminues dans les cas analysés précemment et tu ajoutes la durée d'une journée diminuée de la période de fermeture autant de fois qu'il y a de jours (valeur entière de la différence DateheureCloture - DateheureIncident)
A noter que la durée brute calculée au départ doit rester par définition inférieure à 24 heures (ou 1). Si l'heureCloture est la même que l'heureIncident, la différence est 0 ! Mais écart Dates étant alors de 1 (ou plus) fera ajouter 21 heures (ou 21h * x)
On peut réaliser le calcul en formules... La formule a écrire sera plus longue que si elle résulte d'une fonction personnalisée !
J'avais fait une étude un peu comparable pour calculer la partie d'une durée se situant dans une plage horaire déterminée (qui présente un plus grand nombre de cas possibles), la fomule générale qui en est résultée donne certes le résultat, mais chaque fois que j'ai à l'écrire, je reprends mon modèle pour y substituer les références actuelles et même en faisant ainsi, je trouve que c'est assez long à faire et je me demande à chaque fois si je ne ferais pas mieux d'utiliser la fonction personnalisée qui la transcrit en VBA !
Je ne jugerais donc pas superflu le recours à VBA pour ce genre de formules, qui évite de recommencer la réflexion à chaque utilisation. Cependant le nombre de fonction de ton module m'a quelque peu effrayé ! L'utilisation de fonctions personnalisées n'est pas sans limite. A partir d'un certain seuil tu risques de voir tout se bloquer...
Cordialement.
Piloob a écrit :On peut le faire directement sous excel ?
OUI, il va falloir que je réfléchisse. C'est pas simple à transcrire mais faisable.
Et comme MFerrand, on peut quand même se poser la question : VBA ou formule. La formule a l'avantage de pouvoir la diffuser sur des classeurs sans macro (parfois du reste bloquées par des entreprises). La formule VBA est plus facilement manipulable.
Passer d'une formule Excel à un code VBA pour en faire une fonction personnalisée est plus aisé que l'inverse !
Et il convient ensuite de monter un tableau avec tous les cas de figure (sans oublier les valeurs bornes qui révèlent parfois des surprises...) pour la tester...
Je ne déteste pas... mais je n'ai pas prévu ça pour ma soirée !
Je vous souhaite une bonne soirée.
Voici la formule magique, sauf erreur de ma part (tiens au passage la formule macro me semble apporter des erreurs si plusieurs jours se passent !)
=MAX(0;SI(MOD(B5;1)>hFermeture;B5;MIN(B5;ENT(B5)+hOuverture))-SI(MOD(A5;1)<hOuverture;A5;MAX(A5;ENT(A5)+hFermeture)))-ENT(MAX(0;SI(MOD(B5;1)>hFermeture;B5;MIN(B5;ENT(B5)+hOuverture))-SI(MOD(A5;1)<hOuverture;A5;MAX(A5;ENT(A5)+hFermeture))))*(hFermeture-hOuverture)A vérifier quand même !!
Bonjour,
J'ai tout de même fini par faire ma traduction en formule. Voilà la formule générale :
DHd = Date-heure début (signalisation incident)
DGf = Dat-heure fin (clôture)
hi = heure d'interruption
hr = heure de reprise
=DHf-DHd-(SI(MOD(DHd;1)>=hr;ET(MOD(DHf;1)>=hr;MOD(DHf;1)<MOD(DHd;1));OU(MOD(DHf;1)>=hr;MOD(DHf;1)<MOD(DHd;1)))+ENT(DHf-DHd))*(hr-hi)Cette formule considère qu'il n'y aura pas de saisie d'incident entre 2h00 et 5h00. Pour tenir compte des erreurs possibles de saisie, il faudrait l'encadrer par une condition que les heures saisies ne soient pas situées dans la période d'interruption, car le résultat serait faux.
Je vous passe quelques soucis de réglages...
J'ai posé la formule de Steelson en regard : il semble qu'il y ait un défaut à rechercher (sous réserve que je n'aie pas fait d'erreur en l'adaptant) car elle donne 3 heures de trop dans certains cas... mais je n'ai pas cherché plus car je commençais à être à saturation.
Cordialement.
MFerrand a écrit :Cette formule considère qu'il n'y aura pas de saisie d'incident entre 2h00 et 5h00. Pour tenir compte des erreurs possibles de saisie, il faudrait l'encadrer par une condition que les heures saisies ne soient pas situées dans la période d'interruption, car le résultat serait faux.
C'est ce que j'ai introduit ...
MFerrand a écrit :J'ai posé la formule de Steelson en regard : il semble qu'il y ait un défaut à rechercher (sous réserve que je n'aie pas fait d'erreur en l'adaptant) car elle donne 3 heures de trop dans certains cas... mais je n'ai pas cherché plus car je commençais à être à saturation.
Bien vu MFerrand ! je rends mon tablier ... il me manque le retrait de 3h dans le cas où DHd est < 2h et DGf > 5h
Dans un exercice similaire (décompte des heures de voyages hors période normale de travail, mais avec prise en compte des jours de we ou jours fériés), j'avais décomposé la formule en mettant en exergue notamment pour le voyageur les heures prises en compte ! avec une bonne dizaine de cellules en plus ...
Bonjour Steelson,
On y reviendra, mais faut varier les plaisirs
Il n'y a que pour les heures multiples de 3 que l'on n'a pas d'écart (nombre de décimales fini).
Passe une bonne journée.
Bonsoir,
Merci pour la formule excel qui semble fonctionner !
Effectivement il semble y avoir une erreur dans le code vba pendant les heures de nuit provoquant une erreur d'une à deux heures. J'essaierai de mettre le nez dedans si le temps le permet.
Cordialement.
Bonjour,
J'ai vu l'erreur sur la fonction VBA dans le fichier de Steelson, sans y regarder de trop près, elle m'a paru logique car sur des heures situées dans la plage d'interruption... Il convient donc d'ajouter les conditions adéquates pour détecter ce qui est en fait saisie inadéquate d'heure d'incident ou de clôture, ce qui est aisé à faire en VBA : une ou deux lignes de code... alors qu'en formule ça allonge quelque peu la formule.
Cordialement.
J'ai repris le problème à zéo ... cela me donne une formule plus simple
=(MAX(0;hi-MOD(A7;1))-MAX(hr;MOD(A7;1)))-(MAX(0;hi-MOD(B7;1))-MAX(hr;MOD(B7;1)))+(ENT(B7)-ENT(A7))*(1-(hr-hi))qui tient compte de la déclaration pendant la période à neutraliser (et par simplification -si, si- idem pour la résolution)
J'ai repris le jeu d'essai et le fichier de MFerrand.
Je calcule en fait le temps entre déclaration et minuit, idem résolution, et j'ajoute le nbre d'heures / jour écoulé.
En macro, cela sera maintenant facile en partant de ce principe.
@Piloob : Voici une double version : formules excel et fonction personnalisée. Si tu trouves des erreurs, signale-les. Cela fonctionne y compris avec des dates dans la plage neutralisée.
Public Function dureeIncident(dateIncident, dateCloture, hOuv, hFerm)
dureeIncident = TimeToMidnight(dateIncident, hOuv, hFerm) - _
TimeToMidnight(dateCloture, hOuv, hFerm) + _
(Int(dateCloture) - Int(dateIncident)) * (1 - (hFerm - hOuv))
End Function
Function TimeToMidnight(quand, hO, hF)
TimeToMidnight = 1 + WorksheetFunction.Max(0, hO - (quand - Int(quand))) - WorksheetFunction.Max(hF, (quand - Int(quand)))
End Function@MFerand : je n'ai pas l'habitude de mettre des Application.Volatile dans les fonctions; quelle est la règle et l'effet ?