Function Datefin(deb As Date, duree As Date) As Date
Dim hpr, dureeJ(0 To 6, 0 To 2) As Date, jo As Long, hdeb As Date, hfin As Date, rea As Date
Dim ferie As Boolean, fini As Boolean, i As Long
' tableau des heures début-fin (7 jours !)
hpr = Sheets("Listes").[hprod].Value
' tableau des durées journée-matin-am
For i = 1 To UBound(hpr)
dureeJ(i - 1, 1) = hpr(i, 2) - hpr(i, 1) ' durée matin
dureeJ(i - 1, 2) = hpr(i, 4) - hpr(i, 3) ' durée AM
dureeJ(i - 1, 0) = dureeJ(i - 1, 1) + dureeJ(i - 1, 2) ' durée journée
Next i
Datefin = Int(deb) ' h ramenée à 0h
Do
jo = Weekday(Datefin, vbMonday) - 1 ' jour (lundi=0 à dimanche=6)
ferie = Application.CountIf(Range("feries"), Datefin) > 0
If ferie Or dureeJ(jo, 0) = 0 Then ' fermé
' rien
ElseIf rea = 0 Then ' Jour 1
hdeb = deb - Int(deb)
If hpr(jo + 1, 2) > 0 And hdeb < hpr(jo + 1, 2) Then ' matin
hdeb = Application.Max(hdeb, hpr(jo + 1, 1))
hfin = Application.Min(hdeb + duree - rea, hpr(jo + 1, 2))
rea = rea + hfin - hdeb
If Round(duree, 7) - Round(rea, 7) <= 0 Then
Datefin = Datefin + hfin
fini = True
Else
hdeb = hpr(jo + 1, 3) ' pour préparer l'am
End If
End If
If hpr(jo + 1, 4) > 0 And rea < duree Then ' am
hdeb = Application.Max(hdeb, hpr(jo + 1, 3))
hfin = Application.Min(hdeb + duree - rea, hpr(jo + 1, 4))
rea = rea + hfin - hdeb
If Round(duree, 7) - Round(rea, 7) <= 0 Then
Datefin = Datefin + hfin
fini = True
End If
End If
ElseIf Round(duree, 7) - Round(rea, 7) >= dureeJ(jo, 0) Then ' jour intermédaire
rea = rea + dureeJ(jo, 0)
If Round(duree, 7) - Round(rea, 7) <= 0 Then Datefin = Datefin + Application.Max(hpr(jo + 1, 2), hpr(jo + 1, 4)): fini = True
ElseIf Round(duree, 7) - Round(rea, 7) <= dureeJ(jo, 1) Then ' fin matin
Datefin = Datefin + hpr(jo + 1, 1) + duree - rea: fini = True
Else ' fin am
rea = rea + dureeJ(jo, 1)
Datefin = Datefin + hpr(jo + 1, 3) + duree - rea: fini = True
End If
If Not fini Then Datefin = Datefin + 1
Loop Until fini
End Function