用EXCEL计算起止时间在各个时间段内的时长

用EXCEL计算起止时间在各个时间段内的时长

EXCELL中，常遇到这样的问题：已知起始时间和结束时间，如何计算该起止时间在指定时间段上的时间长度？

比如：

由于起止时间有多种跨越情况，且有零点转换，用EXCEL的自带公式和函数很难实现。下面这个VBA自定义函数，能够轻松解决上面的难题。

函数名tj(t1,t2,n)

3个参数：t1-开始时间，t2-结束时间，为“时分秒”时间格式，可直接引用单元格

n-整数{1|2|3}，(分别代表峰平谷的时间段)

返回值：以“时分秒”形式返回起(t1)止(t2)时间在参数n所代表的时间段内的时长。

在EXCEL工作表中，打开VBA编辑器，将下列代码作为模块插入，保存后即可在单元格中直接调用，格式开如：=Tj($A2,$B2,1)，返回开始时间A2、结束时间B2在7-11点时间段内的时长。

以下代码，在解决不同问题时，对部分参数适当修改即可实现。

Function Tj(t1, t2, n As Integer)

Dim f(2) As Integer, Ti(2), arr(2, 1) As Date

n = n - 1

arr(0, 0) = TimeValue("7:00:00")

arr(0, 1) = TimeValue("4:00:00")

arr(1, 0) = TimeValue("11:00:00")

arr(1, 1) = TimeValue("8:00:00")

arr(2, 0) = TimeValue("19:00:00")

arr(2, 1) = TimeValue("12:00:00")

s = t2 - t1 '总时长

If s < 0 Then

s = TimeValue("23:59:59") + s + TimeValue("00:00:01")

End If

'------------计算开始时间属于哪一时间段，存储于f(0)，并将其后的时间段存储于f(1)、f(2) Select Case t1

Case arr(0, 0) To arr(1, 0) - TimeValue("00:00:01")

f(0) = 0

f(1) = 1

f(2) = 2

t1_ = arr(0, 1) - (t1 - arr(0, 0)) 't1_用于记录开始时间至该时间段结束点的时长

Case arr(1, 0) To arr(2, 0) - TimeValue("00:00:01")

f(0) = 1

f(1) = 2

f(2) = 0

t1_ = arr(1, 1) - (t1 - arr(1, 0))

Case Else

f(0) = 2

f(1) = 0

f(2) = 1

If t1 > arr(2, 0) Then

t1_ = arr(2, 1) - (t1 - arr(2, 0))

Else

t1_ = arr(2, 0) - arr(2, 1) - t1

End If

End Select

'-------------计算总时长s在各时间段内的时长

arr(f(0), 1) = t1_

i = 0

While (s > 0 And i < 3)

Ti(f(i)) = WorksheetFunction.Min(arr(f(i), 1), s)

s = s - Ti(f(i))

i = i + 1

Wend

Ti(f(0)) = Ti(f(0)) + s '如果s在分配至其他时间段后仍有剩余

Tj = Ti(n) '返回指定时间段时长

If Tj = TimeValue("00:00:00") Then

Tj = ""

End If

End Function