基于一系列单元格的动态日期范围 [英] Dynamic date range based a range of cells

问题描述

我有一系列单元格存储美国联邦假期。下面的公式计算一个日期21个工作日。联邦假期可以有动态的日期吗？所以我不必每年改变他们。 O290-O310是存储日期的地方。

  = WORKDAY（A305,21，$ O $ 290：$ O $ 310）
  

解决方案

如果您只处理检查日期，以下内容将会起作用在给出的一年中，如果您正在使用WORKDAY公式计算跨越多年的日期，则需要重复列表中添加+1的列表，否则您将需要开发不同的功能来计算工作日这可能需要一些VBA。

您需要做的是确定如何计算日期的逻辑，并将这些计算值存储在工作表中。现在假设你的工作表中有某个日期，或者你提到你将要处理的某个人的年份，你可以尝试以下几点。我将在1月份的第3个星期一开发逻辑。然后，您可以使用此过程来开发自己的方法来提供类似的假期。看看DATE，YEAR，MONTH，DAY，WEEKDAY，TODAY，NOW，EOMONTH等Excel内置的各种内置日期操作公式，可能对您很有帮助。

处理日期X日或星期的月份

我在这样的表格中使用7个步骤制定了日期：

 （A）| （B）
年| 2014 
月的第一天的平日| 4 
星期一的平日号码2 
差异| -2 
第三个星期一| 21 
从月初开始的天数| 20 
目标日期| 14/01/20 
  

步骤1）确定年份。

在单元格B1中，我用提供的2014硬编码，但这也可能是

  = YEAR（Q10）
  

其中Q10是您的工作表上的一些单元格日期在你正在工作的范围内，如果你只有一个年号，说在2017年的单元格，你可以指出那个单元格没有一年。

步骤2）确定一个月的第一天的星期几。

为了做到这一点，我们结合嵌入在WEEKDAY函数中的DATE函数。由于我们知道在处理1月份和第一天，我们可以硬编码。在B2中使用：

  = WEEKDAY（DATE（B1,1,1））
  

步骤3）说明星期一的星期几。

这很重要做为依靠你的工作日功能如何定义一周的开始，星期一的星期几可以改变。由于我没有在B2公式中更改日期，我们可以将星期一的默认值提供为2.所以在B3中：

  = 2 
  

步骤4）查找月份和星期一的第一个工作日之间的差异

本月第一个工作日与星期一之间的差异需要了解，以便调整自第三个星期一开始的天数以后的天数。在B4中使用：

  = B3-B2 
  

步骤5）确定周三到周三有多少个星期。

如果差异是步骤4为负数或0，则另外21天的工作，如果它的积极的你需要工作14天。在B5中使用：

  = if（B4< = 0,21,14）
  

步骤6）第3周一的一个月的日子

所以基本上我们需要广告B5与B4，这将告诉我们从第3个星期一开始的几天。在B6中使用：

  = B5 + B4 + 1 
  

步骤7）确定第三个星期一的日期

我们知道一年，我们知道这个月，现在我们知道那天。我们拿这三个已知的，再次使用DATE函数。在B7中使用：

  = date（B1,1，B6）
  pre> 
 
 
现在，如果您想将所有内容替换为单个单元格中的一个公式，它将如下所示：
  = DATE（2014,1，IF（2- WEEKDAY（DATE（2014,1,1））≤= 0,21,14）+ 2-WEEKDAY（DATE（ 2014,1,1））+ 1）
  
记住2014年将来自您的电子表格，一年中的单元格，或使用带有日期的单元格，并在该单元格上使用YEAR（）公式。
 
 
 
处理周六或周日的假期
 
 
 
再次，我们需要在一年的电子表格中引用一些单元格，所以我将再次使用Q10作为示例，我们将假定2014/10/24的日期。 / p> 
 
 
  = IF（WEEKDAY（DATE（YEAR（Q10），12,25））= 7，DATE（YEAR（Q10） 12,24），IF（WEEKDAY（DATE（YEAR（Q10），12,25））= 1，DATE（YEAR（Q10），12,26），DATE（YEAR（Q10），12,25）））
  
公式首先检查周日是星期六。 W使用将返回星期几的功能参见上面的步骤2）。这个部分来自上述方程：
 周末（DATE（YEAR（Q10），12,25））
  
它将返回与日期函数导致的星期几相对应的单个整数1到7，在此案件。如果它是1，我们知道它的星期天，如果它的7我们知道它的星期六。所以星期六的支票是：
  WEEKDAY（DATE（YEAR（Q10），12,25））= 7 
  
如果WEEKDAY（）= 7是真的，那么我们提供一天之前的日期真的只是从我们正在看的日期。我们使用公式的这一部分来计算：
  DATE（YEAR（Q10），12,24）
注意我如何将一天从25变为24个。另一种方法是回收我们的日期，使计算机做使用以下公式计算一次：
  DATE（YEAR（Q10），12,25）-1 
或
 DATE（YEAR（Q10），12,25-1）
  
 if语句的TRUE部分。所以如果日期不在星期六，那么我们在IF语句的FLASE部分结束。在这里，我们检查一个第二个IF为周日的日期。我们使用与星期六检查相同的理论和过程。 
  IF（WEEKDAY（DATE（YEAR（Q10），12,25））= 1，DATE（YEAR（Q10） 12,26），DATE（YEAR（Q10），12,25））
  
放置IF语句IF语句中通常称为嵌套。这个整个IF声明发生在前一个IF的FALSE部分，检查它是否是星期六。这次我们检查了星期天：
  WEEKDAY（DATE（YEAR（Q10），12,25））= 1 
  
当这是真的时候，我们需要将日期增加1天，而不是像周六那样减少：
  DATE（YEAR（Q10），12,26）
或
 DATE（YEAR ），12,25）+1 
或
 DATE（YEAR（Q10），12,25 + 1）
  
所以这是星期日检查的真正部分。在逻辑上，到这个嵌套IF语句的FALSE部分的唯一方法是在星期六检查失败，然后在星期日检查失败。这意味着你不需要经过，检查周二是否出现在2,3,4,5或6！其中一个是消除星期日和Satruday（1和7）的过程。如果日期是星期一至星期五，我们不需要更改日期，并且可以保持原样：
  DATE YEAR（Q10），12,25）
  
 
 
 
 HOLIDAY TABLE 
 
 
 
这是一个潜在的方式来布局假日表。
 
 
 
  
 
 
 
这里有一点解释。第3行和第4行是相同的信息。区别是第4行被格式化为仅显示年份。它实际上是一个完整的日期，如第3行。这也与进入2014年或2016年的数字非常不同。这些只是数字。你可以去任何你想要的路线，但你需要使你的公式与你提供的方式一致。
 
 
 
在这个例子中，我刚刚使用了两个假期。 1月份的第三个星期一，我们之前制作的例子和圣诞节的例子。假期在一年之内垂直铺设，所有公式均为Q $ 4。年份标题被复制到右边，每年增加1个列。假日公式被复制到右边。当你这样做时，他们会自动将Q改为R然后S然后T等，所以他们将始终引用他们上面的一年。
 
 
 
然后当你使用你的工作日公式，而不是引用$ Q $ 5：$ Q $ 6，在这个例子中，您将引用$ Q $ 5：$ V $ 6。
 
 
 
提高R3或R4是：
  = DATE（YEAR（Q4）+1,1,1）
  
 
I have a range of cells where US federal holidays are stored. The formula below then calculates a date 21 workdays out. Is it possible to have a dynamic date for the federal holidays. So I don't have to change them every year. O290-O310 is where the dates are stored.
=WORKDAY(A305,21,$O$290:$O$310)

解决方案
The following will work if you are only dealing with checking dates in a giving year,  if you are looking at calculating date spanning multiple years with the WORKDAY formula, you will need to either repeat the list adding +1 to the year in the function, or you will need to develop a different function for counting workdays and that would probably entail some VBA.

What you need to do is work out the logic on how the dates are figured out and store those calculated values in your sheet.  Now assuming you have a date somewhere in your sheet or you refer to the year someone in your sheet that you will be dealing with, you can try the following.  I will develop the logic for 3rd Monday in January.  you can then use this though process to develop your own method for coming up with similar holidays.  Take a look at the various built in date manipulation formulas built into excel such as DATE, YEAR, MONTH, DAY, WEEKDAY, TODAY, NOW, EOMONTH, etc.  they may be of great help to you.

Dealing with dates that fall on X day or week of the month

I worked out the date using 7 steps in a table like this:
(A)                         |     (B)
Year                        |     2014
Weekday of 1st day of month |        4
Weekday number for Monday   |        2
Difference                  |       -2
3rd Monday                  |       21
Days from start of month    |       20
Target date                 | 14/01/20
Step 1)  Determine the year.  

In cell B1 I hard coded this with a supplied 2014, but this could also be
=YEAR(Q10)
Where Q10 is some cell on your sheet with a date in the range you are working with of if you just have a year number say 2017 in a cell you could just point at that cell without the year.

Step 2)  Determine what day of the week the first day of the month is.

In order to do this we incorporate the DATE function imbedded in the WEEKDAY function.  Since we know were are dealing with January and the first day we can hard code that.  In B2 use:
=WEEKDAY(DATE(B1,1,1))
Step 3)  State the day of the week a Monday is.

This is important to do as depending no how your weekday function defines the start of the week, the day of the week that Monday is can vary.  since I did not date a change in the B2 formula we can supply the default value for Monday as 2.  so in B3:
=2
Step 4)  Find the difference between first weekday of month and Monday

The difference between the first weekday of the month and Monday need to be know in order to make an adjustment to the number of days since the start of the month the third Monday will fall.  In B4 use:
=B3-B2
Step 5)  Determine how many full weeks to third Monday.

If the difference is step 4 is negative or 0 you have another 21 days to work with, if its positive you need to work with 14 days. in B5 use:
=if(B4<=0,21,14)
Step 6)  Day of the month for 3rd Monday

So basically we need to ad B5 with B4, and that will tell us how many days from the start of the month the 3rd Monday will be.  In B6 use:
=B5+B4+1
Step 7)  Determine the date of 3rd Monday

We know the year, we know the month and now we know the day.  We take those three knowns and use the DATE function again. In B7 use:
=date(B1,1,B6)
Now if you want to back substitute that all into one formula in a single cell it would look something like:
=DATE(2014,1,IF(2-WEEKDAY(DATE(2014,1,1))<=0,21,14)+2-WEEKDAY(DATE(2014,1,1))+1)
Remember the 2014 would be from your spread sheet either using a cell with the year or using a cell with the date and using the YEAR() formula on that cell.

Dealing with a holiday falling on a Saturday or Sunday

Again we need to refer to some cell in your spreadsheet with the year so I will again use Q10 as the example and we will assume a date of 2014/10/24.
=IF(WEEKDAY(DATE(YEAR(Q10),12,25))=7,DATE(YEAR(Q10),12,24),IF(WEEKDAY(DATE(YEAR(Q10),12,25))=1,DATE(YEAR(Q10),12,26),DATE(YEAR(Q10),12,25)))
The formula checks first if the weekday is a Saturday.  W do this using a function that will return the day of the week  see step 2) above.  Which is this part from the equation above:
WEEKDAY(DATE(YEAR(Q10),12,25))
It will return a single integer 1 through 7 corresponding to the day of the week the date function results in, in this case.  If its a 1 we known its Sunday, if its 7 we know its Saturday.  So the check for saturday is:
WEEKDAY(DATE(YEAR(Q10),12,25))=7
If WEEKDAY()=7 is true then we provides the date of the day before which is really just subtracting 1 from the date we were looking at.  We use this part of the formula to calculate that:
DATE(YEAR(Q10),12,24)
notice how I changed the day from 25 to 24.  An alternate way would be to recycle our date and make the computer do one more calculation using this formula:
DATE(YEAR(Q10),12,25)-1
or
DATE(YEAR(Q10),12,25-1)
That all sits in the TRUE portion of the if statement.  so if the date does not fall on Saturday then we wind up in the FLASE portion of the IF statement.  Here we check with a second IF for the date falling on a Sunday.  we use the same theory and process as we did for the Saturday check. 
IF(WEEKDAY(DATE(YEAR(Q10),12,25))=1,DATE(YEAR(Q10),12,26),DATE(YEAR(Q10),12,25))
Placing an IF statement inside an IF statement is commonly referred to as "nesting".  This whole IF statement happens in the FALSE portion of the previous IF that checked to see if it was Saturday.   This time we checked for Sunday:
WEEKDAY(DATE(YEAR(Q10),12,25))=1
When this is true, then we need to increase the date by 1 day instead of decreasing it like was done for Saturday:
DATE(YEAR(Q10),12,26)
or
DATE(YEAR(Q10),12,25)+1
or
DATE(YEAR(Q10),12,25+1)
So that was the true portion of the Sunday check.  Logically speaking the only way to get to the FALSE portion of this nested IF statement is to fail the Saturday check and then fail the Sunday check.  Which means you do not need to go through and check if is the WEEKDAY comes out as 2, 3, 4, 5 or 6!  Its one of those by the process of eliminating Sunday and Satruday (1 and 7).  And if the date falls on Monday-Friday we dont need to change the date and can leave it just as is:
DATE(YEAR(Q10),12,25)


HOLIDAY TABLE

This is a potential way you could layout a holiday table.



little explanation here.  Row 3 and row 4 are the same information.  The difference is row 4 was formatted to only display the year.  Its actually a full date like row 3.  This is also very much different than enter the year as a number say 2014 or 2016.  Those are just numbers.  You can go any route you want, but you need to make your formulas work with what you are supplying them with.

In this example, I just used two holidays.  The third Monday in January example we worked out before and the Christmas case.  The holidays were layed out vertically beneath the year with all formulas referencing Q$4.  The year headers were copied to the right increasing the year by 1 for each column.  The holiday formulas were copied to the right.  As you do this they will automatically change the Q to R then S then T etc. so they will always be referencing the year above them.

Then when you use your workday formula, instead of referencing $Q$5:$Q$6 for this example you would reference $Q$5:$V$6.

The formula for advancing the date in R3 or R4 is:
=DATE(YEAR(Q4)+1,1,1)


                        
这篇关于基于一系列单元格的动态日期范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！