Excel:计算两个日期之间的工作时间

Excel:计算两个日期之间的工作时间

概括:

我知道该函数NETWORKDAYS可用于查找业务数量(不包括周末和节假日)两个日期之间的交易量。但如果我想知道营业时间小时比如说,昨天 14:00 和今天 10:00 之间?

-->我需要一个函数NETWORKINGHOURS不存在(Excel 2003)。本质上,取NETWORKDAYS并添加两个附加参数start of working dayend 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(第一天工作时间 + 最后一天工作时间,当天结束时间 - 当天开始时间)

相关内容