公式检​​查工作时间是否超时 [英] Formula to check if working out of hours

问题描述

我正在尝试确定某人是否在非工作时间"内没有工作.

I'm trying to find out if someone was not working during 'out of hours'.

• 如果星期一至星期五不在上午8点至晚上9点之间，则答案为是".

• 如果是星期六，而不是在上午8点至下午5点之间的时间，则答案为 会是是"

• If Monday to Friday, not between the hours of, 8am to 9pm, then the answer would be "Yes"

• If it's a Saturday, not between the hours of, 8am to 5pm, the answer would be "Yes"

如果是星期天，则不在上午9点至下午5点之间，答案是 会是是".

If it's a Sunday, not between the hours of, 9am to 5pm, the answer would be "Yes".

否则答案将是否".

共有4列:1.人名(A)2.日期(B)3.时间(C)4.营业时间(D)

There are 4 columns: 1. Person's Name (A) 2. Date (B) 3. Time (C) 4. Out of Hours (D)

我在下面的公式中复制了该公式，该公式在星期一至星期五可以正常工作，但是我在周末的第二部分中苦苦挣扎.

I've copied in the formula below which is working correctly for Monday to Friday, but I'm struggling with the second part of the weekend one.

=IF(OR(WEEKDAY(B2)=1,WEEKDAY(B2)=7),"Yes",IF(AND(C2>=8/24,C2<21/24),"No","Yes"))

如果有人可以建议如何更改公式以使其在周六和周日的非工作时间"工作?

If anyone could advise on how to change the formula to work with 'out of hours' for Saturday and Sunday?

不胜感激.

推荐答案

=IF(WEEKDAY(B2)=7,IF(OR(C2<TIMEVALUE("8:00"),C2>TIMEVALUE("17:00"))=TRUE,"Yes","No"),IF(WEEKDAY(B2)=1,IF(OR(C2<TIMEVALUE("9:00"),C2>TIMEVALUE("17:00"))=TRUE,"Yes","No"),IF(WEEKDAY(B2)<>1,IF(OR(C2<TIMEVALUE("8:00"),C2>TIMEVALUE("21:00"))=TRUE,"Yes","No"),"No")))

您将必须使用嵌套的ifs.希望以上公式能起作用.

You will have to use nested ifs. Hope above formula will work.

这篇关于公式检​​查工作时间是否超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！