|
发表于 2016-6-6 21:32
|
显示全部楼层
本楼为最佳答案
我用的VBA,代码如下:- Sub xx()
- Dim n&, i&, arr(), 上班1 As Date, 下班1 As Date, 上班2 As Date, 下班2 As Date, x As Double
- Application.ScreenUpdating = False
- With Sheet1
- n = .Cells(.Rows.Count, 1).End(xlUp).Row
- arr = .Range("f4:i" & n)
- For i = 1 To n - 3
- If CVDate(arr(i, 1)) < CVDate("7:00:00") Then
- 上班1 = CVDate("7:00:00")
- Else
- 上班1 = CVDate(arr(i, 1))
- End If
- If CVDate(arr(i, 2)) > CVDate("11:50:00") Then
- 下班1 = CVDate("12:00:00")
- Else
- 下班1 = CVDate(arr(i, 2))
- End If
- If CVDate(arr(i, 3)) < CVDate("12:30:00") Then
- 上班2 = CVDate("12:30:00")
- Else
- 上班2 = CVDate(arr(i, 3))
- End If
- If CVDate(arr(i, 4)) > CVDate("15:30:00") Then
- 下班2 = CVDate("15:30:00")
- Else
- 下班2 = CVDate(arr(i, 4))
- End If
- x = ((下班1 - 上班1) + (下班2 - 上班2)) * 24
- If x < 0 Then
- .Cells(i + 3, 15) = ""
- Else
- .Cells(i + 3, 15) = x
- End If
- Next
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码 |
|