excel - 计划结束时间计算，考虑营业时间

Question

我正在尝试寻求一种解决方案来计算考虑到工作时间的目标的计划结束时间。它不应该考虑周末和节假日。

登录时间:上午 8:00
登出时间:下午 5:00

解决时间 - 40 小时，开始 - 01/10/2015 08:00:00 AM，计划结束时间:07/10/2015 12:00:00 PM(预计时间)

Answer 1

考虑到:

A 列有您的变量的描述(A1 -> 开始，A2 -> 实际开始，A3 -> 解决时间，A4 -> 登录时间，A5 -> 注销时间和 A6 -> 计划结束时间)

B 列有变量本身(B1 -> 01/10/2015 08:00:00(格式为 TT.MM.JJJJ hh:mm)，B3 -> 40:00(格式为 [h]:mm:ss) , B4 -> 08:00 (格式化为 hh:mm) & B5 -> 17:00 ((格式化为 hh:mm))

C 列有一系列国定假日(这必须自定义，因为不同国家/地区的假期不同)。对于这个例子，我刚刚为圣诞节定义了 C1，为圣诞节后的一天定义了 C2(在德国也是一个假期)。格式:TT.MM.JJJJ

在单元格 B2 中，您可以使用以下公式获取实际开始日期/时间，以绕过定义晚于注销、周末(从周六到周日的欧洲格式)或本身是假期的一天(从第 3 点定义的日期开始):
=IF(OR(HOUR(B1)>=HOUR(B5),WEEKDAY(B1,11)>5,COUNT(VLOOKUP(B1,C1:C2,1,1))>0),DATE(YEAR(WORKDAY(B1,1,C1:C2)),MONTH(WORKDAY(B1,1,C1:C2)),DAY(WORKDAY(B1,1,C1:C2)))+TIME(HOUR(B4),MINUTE(B4),SECOND(B4)),B1)

在单元格 B5 中，您可以使用以下公式来获得计划的
时间结束
=WORKDAY(B2,CEILING((B3+MOD(B2,1)-B$4)/(B$5-B$4),1)-1,$C$1:$C$2)+MOD(B2,1)+B3-CEILING(MOD(B2,1)+B3-B$4,B$5-B$4)+B$5-B$4

当然，您可以根据自己的结构调整公式。