Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

Christian0258

XLDnaute Accro
Bonsoir à tout le forum,

Je souhaiterais votre aide afin d'écrire, une formule ou fonction, pour calculer l'écart d'heures entre une fin d'horaire de la veille et le début d'horaire du lendemain.
Les horaires sont saisis de la manière suivante : expl d'horaire 8H00-13H00 14H00-18H00 (chaque horaire est séparé par un tiret, et le bloc par un espace...)

voir fichier joint...

Merci, par avance, pour le temps que vous voudrez bien vouloir m'accorder.

Bien amicalement,
Christian
 

Fichiers joints

job75

XLDnaute Barbatruc
Re : Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

Bonjour Christian,

Sur Excel 2003, en O6 à tirer vers le bas :

Code:
=SI(ESTNUM(GAUCHE(C5)*GAUCHE(C6));SUBSTITUE(SUBSTITUE(GAUCHE(MAJUSCULE(C6);5);"-";);"H";":")-SUBSTITUE(SUBSTITUE(DROITE(MAJUSCULE(C5);5);"-";);"H";":");"")
Ou sur les versions postérieures à 2003 :

Code:
=SIERREUR(SUBSTITUE(SUBSTITUE(GAUCHE(MAJUSCULE(C6);5);"-";);"H";":")-SUBSTITUE(SUBSTITUE(DROITE(MAJUSCULE(C5);5);"-";);"H";":");"")
A+
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re : Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

Bonsoir Christian0258, job75 :),

Puisque la formule c'est fait, un essai avec une fonction VBA. les cellules destination doivent être formatée en heure:minute (hh:mm).

J'avais lu trop vite l'énoncé :mad: et j'ai calculé la durée des intervalles pour une cellule. Bon, je laisse le code malgré tout; ça peut servir un jour...

VB:
Function vbduree(x)
Dim s, i&, h1, h2, m1, m2, hm1, hm2
  s = Split(x): s = Filter(s, "-")
  For i = 0 To UBound(s)
    h1 = CInt(Split(Split(s(i), "-")(0), "H", , 1)(0))
    m1 = CInt(Split(Split(s(i), "-")(0), "H", , 1)(1))
    hm1 = 60 * h1 + m1
    h2 = CInt(Split(Split(s(i), "-")(1), "H", , 1)(0))
    m2 = CInt(Split(Split(s(i), "-")(1), "H", , 1)(1))
    hm2 = 60 * h2 + m2
    vbTemps = vbTemps + hm2 - hm1 - 24 * 60 * (hm2 - hm1 < 0)
  Next i
  vbTemps = vbTemps / 60# / 24#
End Function
 
Dernière édition:

job75

XLDnaute Barbatruc
Re : Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

Re, hello mapomme,

En rouvrant le fichier du post #1 je vois qu'il ne faut pas d'heures négatives (O18), donc en O6 :

Code:
=SIERREUR(MOD(SUBSTITUE(SUBSTITUE(GAUCHE(MAJUSCULE(C6);5);"-";);"H";":")-SUBSTITUE(SUBSTITUE(DROITE(MAJUSCULE(C5);5);"-";);"H";":");1);"")
A+
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re : Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

(re)Bonsoir Christian0258, job75 :),

Un essai par macro pour calculer l'écart entre la fin de la veille et le début du lendemain. Les cellules résultats sont formatés en [h]:mm;@.

Le code de EcartVeille(x, y) (x est la veille de y):
VB:
Function EcartVeille(x, y)
Dim s, i&, h0, m0, h1, h2, m1, m2, hm0, hm1, hm2
  On Error GoTo EcartVeille_Err1
  s = Split(x): s = Filter(s, "-")
  s = s(UBound(s)): s = Split(s, "-")(0): s = Split(s, "h", , 1)
  h0 = CInt(s(0)): m0 = CInt(s(1))
  hm0 = h0 / 24# + m0 / 24# / 60#
  
  s = Split(x): s = Filter(s, "-")
  s = s(UBound(s)): s = Split(s, "-")(1): s = Split(s, "h", , 1)
  h1 = CInt(s(0)): m1 = CInt(s(1))
  hm1 = h1 / 24# + m1 / 24# / 60#
  
  If hm1 < hm0 Then hm1 = hm1 + 1
  
  s = Split(y): s = Filter(s, "-")
  s = s(0): s = Split(s, "-")(0): s = Split(s, "h", , 1)
  h2 = CInt(s(0)): m2 = CInt(s(1))
  hm2 = 1 + h2 / 24# + m2 / 24# / 60#
    
  EcartVeille = hm2 - hm1
EcartVeille_Err1:
End Function
 

Fichiers joints

Dernière édition:

Christian0258

XLDnaute Accro
Re : Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

Re, le forum, job75, mapomme,

Merci beaucoup, à vous, vos solutions fonctionnent parfaitement.
J'ai le choix, formule ou fonction, encore un grand merci.

Bien à vous,
Christian
 

Christian0258

XLDnaute Accro
Re : Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

Re, le forum, job75, mapomme,Modeste geedee,

Merci pour votre collaboration.
Après essai il s'avère que la formule, de job75 et la fonction de mapomme, ne fonctionnent pas dans tous les cas
expl : un horaire qui se termine à 16H30 la veille et qui commence à 20H30 le lendemain donne un résultat de 3H30 au lieu de 27H30...

Il est vrai qu'il faut prendre en compte la date de la veille avec son dernier horaire et la date du lendemain avec son premier horaire...

Les formules de Modeste geedee fonctionnent parfaitement mais sont plus ""lourdes"" à mettre en place...

Une fonction me plairait mieux...lol, car elle permet de mettre, éventuellement, du texte après le dernier horaire...

Bien à vous, à vous lire.

Merci à vous pour le boulot,
Bien amicalement,
Christian
 
Dernière édition:

Modeste geedee

XLDnaute Barbatruc
Re : Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

bonsour®
Re, le forum

Les formules de Modeste geedee fonctionnent parfaitement mais sont plus ""lourdes"" à mettre en place...
meuh... non ...:rolleyes:

VB:
Sub Macro1()
    Range("I4").FormulaR1C1 = "=R[1]C[-7]"
     Range("E5").FormulaR1C1 = _
        "=IFERROR(TIMEVALUE(LEFT(SUBSTITUTE(SUBSTITUTE(UPPER(RC[-2]),""H"","":""),""-"","" ""),5)),0)"
    Range("F5").FormulaR1C1 = _
        "=IFERROR(TIMEVALUE(RIGHT(SUBSTITUTE(SUBSTITUTE(UPPER(RC[-3]),""H"","":""),""-"","" ""),5)),0)"
    Range("G5").FormulaR1C1 = "=RC[-1]-RC[-2]+(RC[-1]<RC[-2])"
    Range("H5").FormulaR1C1 = "=IF(RC[-1]<>0,RC[-6]+RC[-3],RC[-6])"
    Range("I5").FormulaR1C1 = "=IF(RC[-2]<>0,RC[-7]+RC[-4]+RC[-2],R[-1]C)"
    Range("J5").FormulaR1C1 = "=IF(RC[-3]<>0,RC[-2]-R[-1]C[-1],"""")"
    Range("E5:J5").Select
    Selection.AutoFill Destination:=Range("E5:J369")    
End Sub
:( exception :
elle ne permet pas de mettre, éventuellement, du texte avant/après le dernier horaire...
 

job75

XLDnaute Barbatruc
Re : Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

Bonjour Christian, Modeste geedee, mapomme,

Cette fonction VBA règle je pense le problème soulevé au post #8 :

Code:
Function MaDif(r As Range)
Dim s1, s2, i%, h, hmem, jour%, h1, h2
MaDif = ""
s1 = Split(Replace(UCase(r(1)), "H", ":"))
s2 = Split(Replace(UCase(r(2)), "H", ":"))
'---dernière heure de la veille--
jour = -1 'la veille, justement
For i = 0 To UBound(s1)
  If InStr(s1(i), "-") Then
    h = Split(s1(i), "-")
    If IsDate(h(0)) And IsDate(h(1)) Then
      If CDate(h(1)) < CDate(h(0)) Or CDate(h(0)) < hmem _
        Then jour = jour + 1
      h1 = CDate(h(1))
      hmem = h1
    End If
  End If
Next
If IsDate(h1) Then h1 = h1 + jour Else Exit Function
'---première heure du jour---
For i = 0 To UBound(s2)
  If InStr(s2(i), "-") Then
    h2 = Split(s2(i), "-")(0)
    If IsDate(h2) Then
      h2 = CDate(h2)
      MaDif = h2 - h1
      If MaDif < 0 Then MaDif = MaDif + 1 'facultatif, à voir...
      Exit Function
    End If
  End If
Next
End Function
En O6 entrer cette formule, à tirer vers le bas :

Code:
=MaDif(C5:C6)
A+
 
Dernière édition:

job75

XLDnaute Barbatruc
Re : Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

Re,

Si l'on n'entre jamais un horaire comme 9H00-20H00 7H00-15H00, on peut se passer de hmem.

Par ailleurs j'oubliais qu'on peut avoir 24H00, voire 24H30, donc utiliser :

Code:
Function MaDif(r As Range)
Dim t$, s1, s2, i%, h, jour%, h1, h2
MaDif = ""
s1 = Split(Replace(Replace(UCase(r(1)), "H", ":"), "24:", "0:"))
s2 = Split(Replace(Replace(UCase(r(2)), "H", ":"), "24:", "0:"))
'---dernière heure de la veille--
jour = -1 'la veille, justement
For i = 0 To UBound(s1)
  If InStr(s1(i), "-") Then
    h = Split(s1(i), "-")
    If IsDate(h(0)) And IsDate(h(1)) Then
      If CDate(h(1)) <= CDate(h(0)) Then jour = jour + 1
      h1 = CDate(h(1))
    End If
  End If
Next
If IsDate(h1) Then h1 = h1 + jour Else Exit Function
'---première heure du jour---
For i = 0 To UBound(s2)
  If InStr(s2(i), "-") Then
    h2 = Split(s2(i), "-")(0)
    If IsDate(h2) Then
      h2 = CDate(h2)
      MaDif = h2 - h1
      If MaDif < 0 Then MaDif = MaDif + 1 'facultatif, à voir...
      Exit Function
    End If
  End If
Next
End Function
Edit : j'utilise aussi <= de manière que 18H00-18H00 soit considéré comme une période de 24 heures.

A+
 
Dernière édition:

Christian0258

XLDnaute Accro
Re : Calculer l'écart heures entre fin horaire veille et début horaire lendemain...

Re, le forum, mapomme, Modeste geedee, job75,

Je vous remercie infiniment pour votre contribution. Je garde, de côté, toutes les solutions proposées.

Je privilégie celle du post11...

Merci encore.

Salut les artistes.
Bien amicalement,

Christian
 

Discussions similaires


Haut Bas