教师节感恩_在Excel中计算感恩节日期

教师节感恩

Recently, Jerry Latham showed us how to use Excel to calculate the date of Easter in any year, by using a worksheet formula or Excel User Defined Function (UDF). Now, it's getting close to Thanksgiving in the USA, so lets see how to calculate that date, with an Excel worksheet formula.

最近，Jerry Latham向我们展示了如何通过使用工作表公式或Excel用户定义函数(UDF) 使用Excel计算任何一年中的复活节日期 。 现在，它已经接近美国的感恩节，因此，让我们看看如何使用Excel工作表公式来计算该日期。

感恩节的日期范围 (Date Range for Thanksgiving)

First, you can use simple arithmetic to figure out the possible date range for the US Thanksgiving, which falls on the fourth Thursday in November.

首先，您可以使用简单的算法来计算美国感恩节的可能日期范围，该日期范围是11月的第四个星期四。

The first Thursday could occur anytime during the first 7 days of November, and if we add 21 days to that date range, the fourth Thursday falls between November 22nd and November 28th each year.

第一个星期四可能在11月的前7天中的任何时间发生，如果我们在该日期范围内增加21天，则第四个星期四将在每年的11月22日至11月28日之间。

工作日号码 (Weekday Numbers)

As part of the formula to calculate the Thanksgiving date, we'll use the WEEKDAY function, which assigns a number to each day of the week. The default setting is to start with Sunday, as weekday number 1. In the table below, you can see that Thursday is weekday number 5.

作为计算感恩节日期的公式的一部分，我们将使用WEEKDAY函数，该函数为一周中的每一天分配一个数字。 默认设置是从星期日开始，作为工作日编号1。在下表中，您可以看到星期四是工作日编号5。

感恩节公式 (Thanksgiving Formula)

The Thanksgiving formula that I'm using was posted by Daniel.M in the old Excel newsgroups. The formula starts with the DATE function, using the year in cell C2, 11 as the month number, and 29 as the day. That is the first date after the latest possible Thanksgiving date (November 22-28).

我使用的感恩节公式由Daniel.M发布在旧的Excel新闻组中。 该公式以DATE函数开头，使用单元格C2中的年份，11作为月份号和29作为一天。 那是最晚的感恩节日期(11月22日至28日)之后的第一个日期。

• DATE(C2,11,29)

  日期(C2,11,29)

We want the result to have a WEEKDAY value of 5 – a Thursday. So, the formula finds the weekday number for November 24th, which is 5 days prior to November 29th.

我们希望结果的WEEKDAY值为5 –一个星期四。 因此，该公式将查找11月24日的工作日编号，该日期是11月29日之前的5天。

• WEEKDAY(DATE(C2,11,24))

  WEEKDAY(DATE(C2,11,24))

To calculate the Thanksgiving date, the completed formula is:

要计算感恩节的日期，完成的公式为：

=DATE(C2,11,29)-WEEKDAY(DATE(C2,11,24))

= DATE(C2,11,29)-WEEKDAY(DATE(C2,11,24))

其他感恩节计算 (Other Thanksgiving Calculations)

There are other ways to calculate the date for Thanksgiving, and other floating holidays. For a few more examples, you can visit the Excel Holidays page on Chip Pearson's website.

还有其他方法可以计算感恩节和其他浮动假期的日期。 有关其他示例，您可以访问Chip Pearson网站上的Excel Holidays页面。

Do you have another favourite method for calculating floating holidays?

您还有另一种最喜欢的浮动假期计算方法吗？

下载Excel Christmas Planner (Download the Excel Christmas Planner)

If you're ready to start shopping and organizing your holiday activities, you can visit the Contextures website, and download the Excel Christmas Planner. It has sheets to help you with your Christmas budget planning, gift lists, dinner preparation, and much more.

如果您准备开始购物和组织假期活动，则可以访问Contextures网站，并下载Excel Christmas Planner 。 它提供了一些表格，可帮助您制定圣诞节预算计划，礼品清单，晚餐准备等。

Please let me know if you have suggestions for improving the planner! _________________

如果您有改善规划师的建议，请告诉我！ _________________

翻译自: https://contexturesblog.com/archives/2011/11/02/calculate-thanksgiving-date-in-excel/

教师节感恩