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

2022-07-23 13:01:35 文档大全网 [ 字体：小中大 ] [ 阅读： ]

【#文档大全网# 导语】以下是®文档大全网的小编为您整理的《用ECEL计算起止时间在各个时间段内的时长》，欢迎阅读！
起止,时间段,时长,各个,时间
用EXCEL计算起止时间在各个时间段内的时长

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

峰期平期谷期

开始时间结束时间

7:00-11:00 11:00-19:00 19:-07:00

13:35:00 21:10:00 5:25:00 2:10:00 23:10:00 08:25:00 1:25:00 9:50:00 12:00 08:00 1:00:00 7:00:00 12:00:00

由于起止时间有多种跨越情况，且有零点转换，用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点时间段内的时长。

以下代码，在解决不同问题时，对部分参数适当修改即可实现。 FunctionTj(t1,t2,nAsInteger)

Dimf(2)AsInteger,Ti(2),arr(2,1)AsDate 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'总时长 Ifs<0Then

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

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

Casearr(0,0)Toarr(1,0)-TimeValue("00:00:01") f(0)=0 f(1)=1

f(2)=2

t1_=arr(0,1)-(t1-arr(0,0))'t1_用于记录开始时间至该时间段结束点的时长 Casearr(1,0)Toarr(2,0)-TimeValue("00:00:01") f(0)=1 f(1)=2 f(2)=0

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

Ift1>arr(2,0)Then

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

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

'-------------计算总时长s在各时间段内的时长 arr(f(0),1)=t1_ i=0

While(s>0Andi<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)'返回指定时间段时长 IfTj=TimeValue("00:00:00")Then Tj="" EndIf

EndFunction