写一个Google工作表,需要公式来评估两个日期和时间,并确定后面的值是否超过2小时和一天。
也就是说,
In - Out
1/1/19 2:00 - 1/1/19 3:59
1/1/19 2:00 - 1/1/19 4:00
1/1/19 2:00 - 1/1/19 4:01
1/1/19 2:00 - 1/2/19 3:59
1/1/19 2:00 - 1/2/19 4:00
1/1/19 2:00 - 1/2/19 4:01
下面是我现在使用的公式
=IFERROR(
ARRAYFORMULA(
IF(ISBLANK($S3:$S),,
FLOOR(
IF(VALUE(TIME(HOUR($S3:$S),MINUTE($S3:$S),1)-TIME(HOUR($R3:$R),MINUTE($R3:$R),0))*24>2.01,
($S3:$S-$R3:$R)+1,
($S3:$S-$R3:$R)+1),1)
)))
其中'R‘在其中,而'S’在外面。目前,我不能让它排除第一天的2小时限制被超过,而试图让它在每隔一天完美地工作。
所以在最上面的例子中,理想情况下,每一行都应该在1天内作为'1‘返回,而不是最后一行,1/2/19 4:01。我正在运行的场景是,一个人可以租一天的东西,然后有2个小时的溢出,而不会因为迟到而被标记为额外的一天。目前,它会将最后4行示例计算为2天。
我尝试格式化公式以包括日期(Year(),Month(),Day() )。然而,我只是遇到了如此多的解析错误,并且无法找到解决这个问题的方法。
发布于 2019-06-27 06:45:55
=IFERROR(
ARRAYFORMULA(
IF(ISBLANK(T3:T),,
FLOOR(
IF(DATEVALUE($Q2:Q)+TIMEVALUE($R2:R)-DATEVALUE($N2:N)+TIMEVALUE($O2:O)>1,
IF(R2:R-O2:O>0.08263888889,(T2:T-S2:S)+2,(T2:T-S2:S)+1),
(T2:T-S2:S)+1)
,1
)
)
)
这似乎是我能让它工作的最好方法。我不得不嵌套一个IF语句,这样我就可以省去第一天的问题。那个奇怪的0.08是与假设的2小时的时间差。
我真的不明白Player0给我的答案。我的情况有三个不同的症结。如果第一天的时差超过2小时,则不能加+2。每隔一天都可以。他奇怪的数字是31.08,所以他的数字可能有几个月了。我不能让它工作,它只会返回一个'1‘。当我分解商并摆弄它时,大部分答案都是0。如果我在这个新公式中遗漏了什么。我很想得到一些意见。
发布于 2019-06-27 07:42:46
=ARRAYFORMULA(IF(LEN(S2:S),
IF((DATEVALUE(S2:S)+TIMEVALUE(S2:S))-
(DATEVALUE(R2:R)+TIMEVALUE(R2:R))<=31.0833333333285, 1,
QUOTIENT((DATEVALUE(S2:S)+TIMEVALUE(S2:S))-
(DATEVALUE(R2:R)+TIMEVALUE(R2:R))-31.0833333333285, 28)+2), ))
https://stackoverflow.com/questions/56781435
复制相似问题