workday接口_使用Excel WORKDAY函数自定义周末

workday接口

If you're trying to figure out when a project will be completed, you can use the WORKDAY.INTL function (Excel 2010 or later). It's more flexible than the older WORKDAY function – it doesn't assume that you work Monday to Friday any more!

如果您想弄清楚何时完成一个项目，可以使用WORKDAY.INTL函数(Excel 2010或更高版本)。 它比以前的WORKDAY函数更灵活-它不认为您从星期一到星期五再工作！

获取项目结束日期 (Get the Project End Date)

To use the basic features of the WORKDAY.INTL function, you can enter 2 pieces of information:

要使用WORKDAY.INTL函数的基本功能，您可以输入2条信息：

• project start date
  项目开始日期
• number of days required to complete the project
  完成项目所需的天数

Then, WORKDAY.INTL will calculate the working date that is the specified number of after the start date. In the screen shot below, we start on Thursday, December 15th (cell C8), and the project will take 2 days (cell C10).

然后，WORKDAY.INTL将计算开始日期之后指定数量的工作日期。 在下面的屏幕截图中，我们从12月15日(星期四)开始(单元格C8)，该项目将需要2天(单元格C10)。

The WORKDAY.INTL formula in cell C12 is:

C12单元格中的WORKDAY.INTL公式为：

=WORKDAY.INTL(C8,C10)

= WORKDAY.INTL(C8，C10)

The table below shows why the result is December 14th. We work on the project on Thursday and Friday (2 days). Since we didn't specify weekend days, it assumes that we don't work on Saturday or Sunday. The next working day is Monday, December 14th, so that is the formula result.

下表显示了为什么是12月14日。 我们在周四和周五(2天)进行该项目。 由于我们未指定周末，因此假设我们不在周六或周日工作。 下一个工作日是12月14日，星期一，因此是公式结果。

调整结束日期 (Adjust the End Date)

I'd rather see the date that the project will be completed, instead of the next working day. So, to adjust the result, I subtract 1 from the number of working days in the formula.

我希望看到项目完成的日期，而不是下一个工作日。 因此，要调整结果，我需要从公式中的工作日数中减去1。

=WORKDAY.INTL(C8,C10-1)

= WORKDAY.INTL(C8，C10-1)

That changes the formula result to Friday, December 11th – the day we stop working on the project.

这将公式结果更改为12月11日(星期五)，即我们停止从事该项目的日期。

排除假期 (Exclude Holidays)

Just like the older WORKDAY function, you can create a list of holidays, and tell the WORKDAY.INTL function to exclude those dates.

就像以前的WORKDAY函数一样，您可以创建假期列表，并告诉WORKDAY.INTL函数排除这些日期。

If you use a named Excel table, it's easy to add or remove dates, when necessary. In the screen shot below, the table is named tblHol, and the date column is used as the Holiday List.

如果使用命名的Excel表，则在必要时可以轻松添加或删除日期。 在下面的屏幕快照中，表名为tblHol，日期列用作假日列表。

To exclude those holidays from the date calculations, I'll add that reference in the fourth argument of the function.

为了从日期计算中排除那些假期，我将在函数的第四个参数中添加该引用。

=WORKDAY.INTL(C8,C10-1,,tblHol[Date])

= WORKDAY.INTL(C8，C10-1 ,, tblHol [Date] )

With that adjustment, if we start a project on Thursday, December 24th, the end date will be Monday, December 28th.

经过调整后，如果我们在12月24日星期四开始一个项目，则结束日期将在12月28日星期一。

Here is the table that shows the working days, with the holidays highlighted in orange.

这是显示工作日的表格，假期以橙色突出显示。

指定非工作日 (Specify Non-Working Days)

If you don't specify which days are the weekend, the WORKDAY.INTL function automatically excludes Saturday and Sunday as weekend (non-working) days. However, you can select different days as the weekend, with either of the following methods:

如果您没有指定哪几天是周末，WORKDAY.INTL函数会自动将星期六和星期日排除为周末(非工作日)。 但是，可以使用以下两种方法之一将不同的日期选择为周末：

• select from the drop down list of options
  从选项下拉列表中选择
• create a string with 1 and 0 characters
  创建一个包含1个和0个字符的字符串

从下拉列表中选择 (Select from Drop Down List)

The easier way to specify weekend days is to select from the drop down list of options. The list will appear automatically when you start the 3rd argument in the function.

指定周末的更简单方法是从选项的下拉列表中进行选择。 当您在函数中启动第三个参数时，该列表将自动出现。

NOTE: If the list doesn't appear, press Alt + Down Arrow

注意：如果没有出现列表，请按Alt +向下箭头

If I choose option 2 – Sunday, Monday – the project end date changes to Tuesday, December 29th.

如果我选择选项2 –星期一，星期一–项目结束日期将更改为12月29日，星期二。

=WORKDAY.INTL(C8,C10-1,2,tblHol[Date])

= WORKDAY.INTL(C8，-C 10 -1-链，2，tblHol [日期])

创建一个非工作日字符串 (Create a Non-Working Day String)

If none of the options in the drop down list are what you need, you can create your own string.

如果您不需要下拉列表中的任何选项，则可以创建自己的字符串。

• The string must be 7 digits, represent the weekdays, from Monday to Sunday.
  该字符串必须为7位数字，代表星期一至星期日的工作日。
• Use a 0 for working days
  在工作日使用0
• Use a 1 for non-working days
  在非工作日使用1

For example, if you only work Monday, Wednesday and Friday, use the string 0101011

例如，如果您仅在星期一，星期三和星期五工作，则使用字符串0101011

I'll change the start date to a working date in our new schedule – Wednesday, December 23rd. Here is the revised formula, with the 7-digit string in the 3rd argument, inside double quote marks:

我将在新时间表中将开始日期更改为工作日期，即12月23日，星期三。 这是修改后的公式，第3个参数中的7位数字字符串用双引号引起来：

=WORKDAY.INTL(C8,C10-1,"0101011",tblHol[Date])

= WORKDAY.INTL(C8，C10-1，“ 0101011”，tblHol [Date])

With those changes, the end date is Monday, December 28th.

更改后，结束日期为12月28日星期一。

计算无效字符串 (Calculate the Non-Working String)

If you want to make it easier to select the non-working days, you can use a table, like the one below. Mark the non-working days with an X, and use an IF formula to show 1 or 0 in each row.

如果您想更轻松地选择非工作日，可以使用一个表格，如下所示。 用X标记非工作日，并使用IF公式在每行中显示1或0。

=IF(K8="x",1,0)

= IF(K8 =“ x”，1,0)

Then, use a CONCATENATE formula to compile the string:

然后，使用CONCATENATE公式来编译字符串：

=CONCATENATE(M8,M9,M10,M11,M12,M13,M14)

= CONCATENATE(M8，M9，M10，M11，M12，M13，M14)

In the WORKDAY.INTL formula, refer to that cell in the 3rd argument.

在WORKDAY.INTL公式中，在第3个参数中引用该单元格。

=WORKDAY.INTL(C8,C10-1,M15,tblHol[Date])(C8,C10-1,M15,tblHol[Date])

= WORKDAY.INTL(C8，C10-1，M15，tblHol [Date])(C8，C10-1， M15 ，tblHol [Date])

The calculation columns could be hidden, to prevent people from messing up the formulas.

计算列可以隐藏，以防止人们弄乱公式。

关于WORKDAY.INTL的说明 (Notes About WORKDAY.INTL)

A few things to note about working with the WORKDAY.INTL function:

有关使用WORKDAY.INTL函数的一些注意事项：

• Make sure the start date is a working day, or the results will be different from what you expect.
  确保开始日期是一个工作日，否则结果将与您期望的不同。
• You can enter a negative number for days, to calculate a project start date, based on a known end date.
  您可以输入几天的负数，以根据已知的结束日期计算项目的开始日期。
• If you enter a non-integer value for the number of days, it will be truncated.
  如果您输入非整数值的天数，它将被截断。
• You must have at least one zero in the Weekend string – it can't be 1111111
  周末字符串中必须至少有一个零-不能为1111111

Are there any other things to watch out for when working with this function?

使用此功能时，还有其他需要注意的事情吗？

下载样本文件 (Download the Sample File)

You can download the sample file, to follow along with this tutorial. Go to the Excel Sample Files page on my Contextures website. In the Functions section, look for FN0043 – Calculate Project End Date with WORKDAY.INTL.

您可以下载样本文件，并随本教程一起学习。 转到我的Contextures网站上的“ Excel示例文件”页面。 在“功能”部分中，查找FN0043 –使用WORKDAY.INTL计算项目结束日期 。

The zipped file is in xlsx format, and does not contain macros.

压缩文件为xlsx格式，不包含宏。

翻译自: https://contexturesblog.com/archives/2015/12/10/customize-weekends-with-excel-workday-function/

workday接口