WORKDAY()在空白单元格上返回意外值 [英] WORKDAY() returns unexpected value on blank cells

问题描述

我正在尝试使用以下公式设置条件格式以突出显示日期为周末:

I am trying to set up conditional formatting to highlight when a date is in the weekend with this formula:

=WEEKDAY(A1,2)>5

当应用于仅包含日期的范围时，此方法可以正常工作.但是，如果有任何空格或数字值，它们也会被格式化.我已经测试过，其中A1为空白的公式=WEEKDAY(A1)返回6，表示它是星期六.

This works fine when applied to a range only containing dates. However when there is any blanks or numeric values these also get formatted. I've tested and the formula =WEEKDAY(A1) where A1 is blank returns 6, implying it is a Saturday.

问题1: 有人知道为什么吗?

Question 1: Does anyone know why this is?

问题2: 如何解决此问题，并保留空白单元格的未格式化状态?我已经考虑过ISNUMBER和ISBLANK，它们适用于空格，但不适用于该范围内的数值.

Question 2: How do I get around this, and leave blank cells unformatted? I've considered ISNUMBER and ISBLANK which would work for blanks, but not for numeric values within the range.

推荐答案

要检测数量有限的日期样式的格式化为日期"的单元格，可以使用CELL函数:

To detect the cell formatted as Date for limited number of date styles you can use CELL function:

=CELL("format",A1)

例如对于日期格式m/d/yyyy，它将返回"D4"，可用于测试输入的值.

e.g. for date format m/d/yyyy it will return "D4" which can be used for testing the value being entered.

对于其他查询，请参考Foxfire和Burns and Burns提供的答案.

Please refer the answer provided by Foxfire and Burns and Burns for the other query.

这篇关于WORKDAY()在空白单元格上返回意外值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！