我有一个自定义列表,我试图限制一周中有效日期和时间的数据输入。我当前的列验证适用于星期一、星期三或星期五的星期几。 它看起来像这样:
=CHOOSE(WEEKDAY([Requested date for approval]),FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE)
我正在尝试找出语法以添加它也必须在那些日子的上午 8 点到中午 12:00 之间。
任何帮助将不胜感激。
您可以使用 AND 语句来包含第二个条件
=AND(CHOOSE(WEEKDAY([Requested date for approval]),FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE),
AND(
[Requested date for approval]-INT([Requested date for approval])*24 >= 8,
[Requested date for approval]-INT([Requested date for approval])*24 <= 24
)
)
我承认,我从未听说过 SELECT 函数,但时间计算是基于Microsoft
转换时间要将小时从标准时间格式转换为十进制数,请使用 INT 功能。
列 1公式说明(可能的结果)上午 10:35 =([列 1]-INT([列 1]))*24 自凌晨 12:00 以来的小时数 (10.583333)下午 12:15 =([列 1]-INT([列 1]))*24 自上午 12:00 以来的小时数 (12.25)
编辑
要计算星期几,您可以使用 TEXT 函数返回星期几(即星期一)
=TEXT(WEEKDAY([ColumnName]), "dddd")
它不会很漂亮,但您可以使用一系列 AND 逻辑运算符
=AND(
TEXT(WEEKDAY([Requested date for approval]), "dddd") = "Monday",
AND(
TEXT(WEEKDAY([Requested date for approval]), "dddd") = "Wednesday",
AND(
TEXT(WEEKDAY([Requested date for approval]), "dddd") = "Friday",
AND(
[Requested date for approval]-INT([Requested date for approval])*24 >= 8,
[Requested date for approval]-INT([Requested date for approval])*24 <= 24
)
)
)
)
发布工作解决方案
=IF(
AND(
CHOOSE(
WEEKDAY([Requested date for approval]),FALSE,TRUE,FALSE,TRUE,FALSE,TRUE,FALSE
),
([Requested date for approval]-INT([Requested date for approval]))*24>=8,
([Requested date for approval]-INT([Requested date for approval]))*24<=12
),
TRUE
)