Re : Calcul de plage horaire excluant dédoublement
Afin de partager, je mets ici-bas la solution à laquelle nous sommes arrivé, merci de votre aide précieuse :
Option Explicit
'
Function CalculTemps(cel_Dep As Range, cel_Fin As Range) As Variant
Dim I As Range, nbl As Integer, ind As Integer, k1 As Integer
Dim Boucle As Integer, x As Single, y As Single
Dim T_Minute As String, Temps As Single
Dim Table() As Single, TableH() As Single
Dim TableR(96, 1) As Single
Application.Volatile
Erase TableR: Erase TableH: Erase Table
Temps = 0
For Boucle = 0 To 95
TableR(Boucle, 0) = Temps
Temps = Temps + 0.25
Next Boucle
ReDim Table(cel_Dep.Rows.Count - 1, 2)
For Each I In cel_Dep
Table(I.Row - cel_Dep.Row, 0) = Hour(I) + (Minute(I.Value) / 60)
Next
For Each I In cel_Fin
Table(I.Row - cel_Fin.Row, 1) = Hour(I.Value) + (Minute(I.Value) / 60)
Next
nbl = 0: ind = 0
For Boucle = 0 To UBound(Table, 1) - 1
y = 0: x = 0
While (TableR(x, 0) < Table(Boucle, 0))
x = x + 1
Wend
While (TableR(x, 0) < Table(Boucle, 1))
TableR(x, 1) = 1
x = x + 1
Wend
Next
For Boucle = 0 To UBound(TableR)
ind = (ind + TableR(Boucle, 1))
Next
Temps = (ind / 4)
Select Case (Temps - (Int(Temps)))
Case 0: T_Minute = "00"
Case 0.25: T_Minute = "15"
Case 0.5: T_Minute = "30"
Case 0.75: T_Minute = "45"
End Select
CalculTemps = Int(Temps) & " h " & T_Minute
End Function
'