我需要一个公式来计算两个日期和时间之间不包括午餐时间，假期，周末和09：00和18：00之间的工作时间。例如，2022年7月25日12：00和2022年7月29日10：00，答案必须为1天06：00先谢谢你。我有一个公式，但它不工作时，大于24小时。
nvbavucw1#
我不知道你是如何得到1天6小时，但这里是一个自定义的方式来过滤您的时差计算：
=LET( start,E3, end,E4, holidays,$B$3:$B$5, array,SEQUENCE(INT(end)-INT(start)+1,24,INT(start),TIME(1,0,0)), crit_1,array>=start, crit_2,array<=end, crit_3,WEEKDAY(array,2)<6, crit_4,HOUR(array)>=9, crit_5,HOUR(array)<=18, crit_6,HOUR(array)<>13, crit_7,ISERROR(MATCH(DATE(YEAR(array),MONTH(array),DAY(array)),holidays,0)), result,SUM(crit_1*crit_2*crit_3*crit_4*crit_5*crit_6*crit_7), result )
此解决方案仅适用于小时级别，即开始和结束日期和时间将仅以整小时为基础进行考虑。当提供12：45这样的时间作为输入时，15分钟的增量将不会被考虑在内。
LET（）函数SEQUENCE(INT(end)-INT(start)+1,24,INT(start),TIME(1,0,0))中的第4项创建一个数组，其中包含该范围的开始日期和结束日期内的所有小时：
SEQUENCE(INT(end)-INT(start)+1,24,INT(start),TIME(1,0,0))
（为便于说明而调换）然后，基于该数组，不同的'crit_n'语句就是您提到的各个条件。例如，crit_1,array>=start表示只计算开始日期和时间之后的日期和时间，或者crit_6,HOUR(array)<>13是午休时间（假设第13个小时是午餐时间），...然后，所有的单个crit_n是包含TRUE和FALSE元素的相同大小的数组。在LET（）函数的末尾，通过将所有单独的crit_n数组相乘，乘积返回一个数组，该数组将只包含所有单独的criteria语句都为TRUE的那些小时：
crit_1,array>=start
crit_6,HOUR(array)<>13
因此，SUM（）函数只是返回符合所有条件的总小时数。示例我假设午餐时间是13点，并假设28日是给定范围内的假日。通过这些假设和您在上面指定的其他条件，我得到了以下结果：
进入公式栏时如下所示：
ctehm74n2#
在单元格G2中，可以放入以下公式：
G2
=LET(from,A2:A4,to,B2:B4,holidays,C2:C2,startHr,E1,endHr,E2, lunchS, E3, lunchE, E4, CALC, LAMBDA(date,isFrom, LET(noWkDay, NETWORKDAYS(date,date,holidays)=0, IF(noWkDay, 0, LET(d, INT(date), start, d + startHr, end, d + endHr, noOverlap, IF(isFrom, date > end, date < start), lunchDur, lunchE-lunchS, ls, d + lunchS, le, d + lunchE, isInner, IF(isFrom, date > start, date < end), diff, IF(isFrom, end-date-1 - IF(date < ls, lunchDur, 0), date-start-1 - IF(date > le, lunchDur, 0)), IF(noOverlap, -1, IF(isInner, diff, 0)))))), MAP(from,to,LAMBDA(ff,tt, LET(wkdays, NETWORKDAYS(ff,tt,holidays), duration, wkdays + CALC(ff, TRUE) + CALC(tt, FALSE), days, INT(duration), time, duration - TRUNC(duration), TEXT(days, "d") &" days "& TEXT(time, "hh:mm") &" hrs " ))) )
输出如下：
使用LET函数，便于阅读和排版。主要思想是首先计算从列值到到**列值不包括holidays**的工作日数。我们使用NETWORKDAYS函数。一旦我们有了每行的这个值，我们需要考虑间隔的第一天和最后一天来调整它。如果我们不能计算为一整天，而是考虑小时。对于内部天（不是间隔的开始/结束），它被计算为一整天。我们使用MAP函数对from和to名称的所有值进行计算。（ff，tt）我们计算工作日（wkdays）。一旦我们有了这个值，我们使用用户LAMBDA函数CALC来调整它。该函数具有第二个输入参数isFrom来考虑这两种情况，即，在间隔的开始（isFrom = TRUE）或到间隔的结束（isFrom=FALSE）的调整。第一个输入自变量是给定的date。如果CALC的输入date是非工作日，我们不需要做任何调整，我们用名称noWkDay来检查，如果不是这样，我们需要判断是否没有重叠（noOverlap）：
LET
holidays
NETWORKDAYS
MAP
from
to
ff
tt
wkdays
LAMBDA
CALC
isFrom
isFrom = TRUE
isFrom=FALSE
date
noWkDay
noOverlap
IF(isFrom, date > end, date < start)
其中，start、end名称对应于与date相同的日期，但对应于start Hr和end Hr的小时不同（E1:E2）。例如，对于第一行，没有重叠，因为结束日期没有小时信息，即（12:00 AM），在这种情况下，不应考虑相应的日期，CALC返回-1，即需要减去一天。如果我们有重叠，那么我们需要考虑工作时间低于最高工作时间的情况（从9:00到18:00）。它以名称isInner标识。如果是这种情况，我们计算实际工时。我们需要减去1，因为它将减少一个完整的工作日，而不是考虑相应的小时数（该值应小于9hrs，即最大工作日持续时间）。计算将在名称diff下进行：
start
end
E1:E2
12:00 AM
-1
9:00
18:00
isInner
1
9hrs
diff
IF(isFrom, end-date-1 - IF(date < ls, lunchDur, 0), date-start-1 - IF(date > le, lunchDur, 0))
如果实际开始时间在午餐时间开始之前（ls），那么我们需要减去午餐持续时间（lunchDur）。类似地，如果实际结束时间在午餐时间之后，我们也需要将其贴现。最后，我们用CALC来计算区间duration：
ls
lunchDur
duration
wkdays + CALC(ff, TRUE) + CALC(tt, FALSE)
一旦我们有了这些信息，它只是把在指定的格式表示天和小时。现在，让我们回顾一些示例输入数据和结果：
7/25
7/29
5
7/26
4
[7/25, 7/29]
3
[7/25 10:00, 7/29 17:00]
8hrs
2days
16hrs
4days
1hr
2 days 14hrs
2条答案
按热度按时间nvbavucw1#
我不知道你是如何得到1天6小时，但这里是一个自定义的方式来过滤您的时差计算：
限制
此解决方案仅适用于小时级别，即开始和结束日期和时间将仅以整小时为基础进行考虑。当提供12：45这样的时间作为输入时，15分钟的增量将不会被考虑在内。
说明
LET（）函数
SEQUENCE(INT(end)-INT(start)+1,24,INT(start),TIME(1,0,0))中的第4项创建一个数组，其中包含该范围的开始日期和结束日期内的所有小时：（为便于说明而调换）
然后，基于该数组，不同的'crit_n'语句就是您提到的各个条件。例如，
crit_1,array>=start表示只计算开始日期和时间之后的日期和时间，或者crit_6,HOUR(array)<>13是午休时间（假设第13个小时是午餐时间），...然后，所有的单个crit_n是包含TRUE和FALSE元素的相同大小的数组。
在LET（）函数的末尾，通过将所有单独的crit_n数组相乘，乘积返回一个数组，该数组将只包含所有单独的criteria语句都为TRUE的那些小时：
因此，SUM（）函数只是返回符合所有条件的总小时数。
示例
我假设午餐时间是13点，并假设28日是给定范围内的假日。通过这些假设和您在上面指定的其他条件，我得到了以下结果：
进入公式栏时如下所示：
ctehm74n2#
在单元格
G2中，可以放入以下公式：输出如下：
说明
使用
LET函数，便于阅读和排版。主要思想是首先计算从列值到到**列值不包括holidays**的工作日数。我们使用NETWORKDAYS函数。一旦我们有了每行的这个值，我们需要考虑间隔的第一天和最后一天来调整它。如果我们不能计算为一整天，而是考虑小时。对于内部天（不是间隔的开始/结束），它被计算为一整天。我们使用
MAP函数对from和to名称的所有值进行计算。（ff，tt）我们计算工作日（wkdays）。一旦我们有了这个值，我们使用用户LAMBDA函数CALC来调整它。该函数具有第二个输入参数isFrom来考虑这两种情况，即，在间隔的开始（isFrom = TRUE）或到间隔的结束（isFrom=FALSE）的调整。第一个输入自变量是给定的date。如果
CALC的输入date是非工作日，我们不需要做任何调整，我们用名称noWkDay来检查，如果不是这样，我们需要判断是否没有重叠（noOverlap）：其中，
start、end名称对应于与date相同的日期，但对应于start Hr和end Hr的小时不同（E1:E2）。例如，对于第一行，没有重叠，因为结束日期没有小时信息，即（12:00 AM），在这种情况下，不应考虑相应的日期，CALC返回-1，即需要减去一天。如果我们有重叠，那么我们需要考虑工作时间低于最高工作时间的情况（从
9:00到18:00）。它以名称isInner标识。如果是这种情况，我们计算实际工时。我们需要减去1，因为它将减少一个完整的工作日，而不是考虑相应的小时数（该值应小于9hrs，即最大工作日持续时间）。计算将在名称diff下进行：如果实际开始时间在午餐时间开始之前（
ls），那么我们需要减去午餐持续时间（lunchDur）。类似地，如果实际结束时间在午餐时间之后，我们也需要将其贴现。最后，我们用
CALC来计算区间duration：一旦我们有了这些信息，它只是把在指定的格式表示天和小时。
现在，让我们回顾一些示例输入数据和结果：
7/25开始，到星期五7/29结束，因此我们有5个工作日，但7/26是假日，因此最大工作日数为4天。[7/25, 7/29]，开始和结束于午夜（12:00 AM），因此不应考虑间隔的最后一天，因此实际工作日将为3。[7/25 10:00, 7/29 17:00]，对于区间的开始我们不能计算一天，而应该是8hrs，对于区间的结束，同样的情况是8hrs，所以我们应该是2days加上16hrs，而不是4days，但是在这两种情况下我们都需要减去午餐持续时间（1hr），因此最终结果将是2 days 14hrs。