1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
| Private Sub CommandButton3_Click()
Dim D1 As Date, D2 As Date, T1 As Date, T2 As Date
Dim H1 As Date, H2 As Date
Dim Diff As Integer, i As Integer
H1 = "06:00"
H2 = "23:00"
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row '<-- commence le calcul à la ligne 2
D1 = Format(Cells(i, 1), "dd/mm/yyyy")
D2 = Format(Cells(i, 2), "dd/mm/yyyy")
T1 = Format(Cells(i, 1), "h:mm")
T2 = Format(Cells(i, 2), "h:mm")
Select Case T1
Case Is < H1
T1 = "06:00"
Case Is < H2
T1 = T1
Case Is > H2
T1 = "06:00"
D1 = D1 + 1
End Select
Select Case T2
Case Is < H1
T2 = "23:00"
D2 = D2 - 1
Case Is < H2
T2 = T2
Case Is > H2
T2 = "23:00"
End Select
Diff = DateDiff("h", T1, T2) + (17 * (D2 - D1))
Cells(i, 3) = Diff
Next
End Sub |
Partager