概括:
我知道该函数NETWORKDAYS
可用于查找业务数量天(不包括周末和节假日)两个日期之间的交易量。但如果我想知道营业时间小时比如说,昨天 14:00 和今天 10:00 之间?
-->我需要一个函数NETWORKINGHOURS
不存在(Excel 2003)。本质上,取NETWORKDAYS
并添加两个附加参数start of working day
和end of working day
。听起来很简单,但我尝试通过公式创建它,很快就变得非常复杂。
问题:networkhours
即使结束时间早于开始时间, 我怎样才能完成工作?
细节:
我有一张包含两个日期+时间列的 Excel 表。我想计算时间戳之间的天数+小时数+分钟数,但只计算营业时间。
该函数NETWORKDAYS
可用于查找除周末和节假日之外的工作日(通过从其结果中减去 2,因为该函数会计算开始日和结束日,而不管时间如何)。到目前为止一切顺利。它看起来像这样(灵感来自这里):
=CONCATENATE(NETWORKDAYS($A6;B6;holidays)-1
;"d "
;HOUR(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))
;":"
;IF(MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))>9
;MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))
;CONCATENATE("0";MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1))))))
给我很好的格式结果1d 2:30
假设我已将单元格命名为startofday
(08:00)、endofday
(16:00)和holidays
(带有日期值的列)。
这很有效不同之处在于,当结束时间早于开始时间时,公式会中断。以下是我的测试数据和公式:
网络天数-2=NETWORKDAYS($A6;B6;holidays)-2
小时=MIN($B$3;MOD(B6;1))-MAX($A$3;MOD(A6;1))
分钟=MINUTE(MIN(endofday;MOD(B6;1))-MAX(startofday;MOD($A6;1)))
网络小时数参见上面的代码块
答案1
整个工作日 = NETWORKDAYS - 2
第一天的工作时间 = MAX(0, endofday - MAX(starttime, startofday))
最后一天的工作时间 = MAX(0, MIN(entime, endofday) - startofday)
总工作日 = 整个工作日 + INT((第一天工作时间 + 最后一天工作时间) / (结束日 - 开始日))
额外时间(总天数之后)= MOD(第一天工作时间 + 最后一天工作时间,当天结束时间 - 当天开始时间)