excel日期相减去除周末_仅在Excel中允许周末日期

excel日期相减去除周末

With Excel Data Validation, you can add rules to a data entry sheet, and control what people put in the cells. In today's example, we'll set up a cell that only allows you to enter a weekend date. Just remember that Data Validation isn't foolproof, and people can find ways around your rules.

使用Excel数据验证，您可以将规则添加到数据输入表中，并控制人们在单元格中放置的内容。 在今天的示例中，我们将设置一个仅允许您输入周末日期的单元格。 请记住，数据验证并非万无一失，人们可以找到绕过您规则的方法。

周末约会挑战 (Weekend Date Challenge)

Here's what we want in our data entry sheet. People can enter a date in cell B2, but we want them to enter weekend dates only.

这就是我们在数据输入表中想要的。 人们可以在单元格B2中输入日期，但是我们希望他们仅输入周末日期。

If they put a Monday to Friday date in cell B2, a warning message should appear, and block that date from being entered.

如果他们在单元格B2中输入了星期一到星期五的日期，则会出现一条警告消息，并阻止该日期的输入。

数据验证下拉列表 (Data Validation Drop Downs)

One way to make sure that people make valid entries in a cell, is to set up a drop down list, and only allow those items. That's handy for a short list of items, such as employee names, or product categories.

确保人们在单元格中进行有效输入的一种方法是设置一个下拉列表 ，并仅允许这些项目。 对于简短的项目列表(例如员工姓名或产品类别)，这很方便。

A drop down list wouldn't be a good solution in this case though. We'd need to make a list of all possible weekend dates, and who has time for that?

但是，在这种情况下，下拉列表并不是一个好的解决方案。 我们需要列出所有可能的周末约会清单，谁来安排时间？

日期数据验证 (Data Validation for Dates)

Another Data Validation option is its set of built-in  Date Rules.

另一个数据验证选项是其内置的日期规则集 。

You can use those rules if you want to restrict dates  to a specific date range. Choose an operator from the drop down list, such as Greater Than, or Not Equal To.

如果要将日期限制为特定日期范围，则可以使用这些规则。 从下拉列表中选择一个运算符，例如“大于”或“不等于”。

Then, fill in the date boxes that appear for the selected option. For example, choose Between, and set a start date and end date for the valid date range.

然后，填写为所选选项显示的日期框。 例如，选择“之间”，然后为有效日期范围设置开始日期和结束日期。

数据验证自定义规则 (Data Validation Custom Rule)

Unfortunately, none of those built-in date options let you limit dates to specific days of the week.

不幸的是，这些内置日期选项均无法将日期限制为一周中的特定日期。

To do that, you can set up a Data Validation Custom Rule.

为此，您可以设置数据验证自定义规则。

Next, you'll need to enter a formula for the custom rules, to tell Excel that only weekend dates are allowed.

接下来，您需要输入自定义规则的公式，以告诉Excel仅允许周末日期。

确定工作日 (Identify the Weekday)

I like to test formulas on the worksheet, before adding them to the Data Validation custom rule settings.

我喜欢先在工作表上测试公式，然后再将其添加到“数据验证”自定义规则设置中。

In the screen shot below, the date in cell B2 is Thursday, December 13th, formatted to show the weekday name.

在下面的屏幕快照中，单元格B2中的日期是12月13日星期四，其格式设置为显示工作日名称。

There are 7 days in a week, and with the Excel WEEKDAY function, we can get a number for the weekday that a date falls on.

一周中有7天，并且使用Excel WEEKDAY函数 ，我们可以获取日期所在的工作日的数字。

Here is my test formula, in cell B4:

这是我在B4单元格中的测试公式：

=WEEKDAY(B2)

=星期(B2)

The result is 5 – the weekday number for Thursday.

结果为5 –星期四的工作日数。

星期几编号 (WEEKDAY Numbering)

My formula uses the default setting for WEEKDAY, which numbers the days from Sunday (1) to Saturday (7).

我的公式使用WEEKDAY的默认设置，该设置从周日(1)到周六(7)进行编号。

So, we could make a rule that only allows dates with a weekday number of 1 (Sunday) OR 7 (Saturday).

因此，我们可以制定一条规则，只允许工作日编号为1(星期日)或7(星期六)的日期。

=OR(WEEKDAY(B2)=1,WEEKDAY(B2)=7)

= OR(WEEKDAY(B2)= 1，WEEKDAY(B2)= 7)

The date is B2 is not a Sunday or Saturday, so the formula result is FALSE. That date would not be allowed.

日期为B2而不是星期日或星期六，因此公式结果为FALSE。 该日期将不被允许。

WEEKDAY功能选项 (WEEKDAY Function Options)

Instead of checking for 1 and 7, we could make a change to the WEEKDAY formula, so that we only have to do one check.

无需检查1和7，我们可以更改WEEKDAY公式，因此我们只需要进行一次检查。

There is an optional argument for WEEKDAY – return_type. If you omit that argument, the default (1) is used.

WEEKDAY有一个可选参数– return_type 。 如果省略该参数，则使用默认值(1)。

更改退货类型 (Change the Return Type)

If we use 2 as the return_type, the numbers start with Monday as 1. That puts Saturday and Sunday together at the end of the list, with numbers 6 and 7.

如果我们使用2作为return_type，则数字从星期一开始为1。这将星期六和星期日放在列表的末尾，分别是数字6和7。

That lets us create a simpler formula – we can check for numbers greater than 5 (Friday).

这使我们可以创建一个更简单的公式–我们可以检查大于5(星期五)的数字。

=WEEKDAY(B2,2)>5

=星期(B2,2)> 5

With the Thursday date in B2, the formula returns FALSE – it is not a valid weekend date.

对于B2中的星期四日期，该公式将返回FALSE –这不是有效的周末日期。

If I change the date in B2 to December 15th (a Saturday), the result is TRUE. That is a weekend date, and would be allowed.

如果我将B2中的日期更改为12月15日(星期六)，则结果为TRUE。 那是周末，可以允许。

创建数据验证自定义规则 (Create the Data Validation Custom Rule)

After you've tested the formula on the worksheet, and it works correctly, set up the Data Validation Custom Rule.

在工作表上测试了公式之后，该公式可以正常工作，然后设置“数据验证自定义规则”。

• Select cell B2, and on the Data tab, click Data Validation
  选择单元格B2，然后在“数据”选项卡上，单击“数据验证”
• From the Allow drop down, choose Custom
  从“允许”下拉列表中，选择“自定义”

• In the Formula box, type the formula that you tested:  =WEEKDAY(B2,2)>5

  在“公式”框中，键入您测试的公式： = WEEKDAY(B2,2)> 5

• To show an error message, click the Error Alerts tab and set up an Error message

  要显示错误消息，请单击“错误警报”选项卡并设置错误消息

• Click OK, to apply the custom rule
  单击确定，以应用自定义规则

Test the rule in cell B2, by entering a few weekend dates, and non-weekend dates.

通过输入一些周末日期和非周末日期来测试单元格B2中的规则。

更多自定义规则 (More Custom Rules)

Go to my Contextures website, to see more Custom Rules for Data Validation. There is a sample file that you can download, with those examples.

转到我的Contextures网站，查看更多有关数据验证的自定义规则 。 您可以下载带有这些示例的样本文件。

使用Excel数据验证来防止无效日期 (Prevent Invalid Dates With Excel Data Validation)

Here's another example of using data validation to control which dates can be entered in Excel. In this video, 3 different methods are used to validate dates.

这是使用数据验证来控制可以在Excel中输入哪些日期的另一个示例。 在此视频中，使用了3种不同的方法来验证日期。

• Specify a starting date and an ending date. (Date option)
  指定开始日期和结束日期。 (日期选项)
• Show a drop down list of valid dates (List option)
  显示有效日期的下拉列表(列表选项)
• Create a rule in a custom formula (Custom option)
  在自定义公式中创建规则(“自定义”选项)

Written instructions, and the sample file, are on the Data Validation for Dates page, on my Contextures site.

书面说明和示例文件位于Contextures网站上的“ 日期数据验证”页面上。

演示地址

翻译自: https://contexturesblog.com/archives/2018/12/13/allow-weekend-dates-only-in-excel/

excel日期相减去除周末