使用公式建立假日表 [英] building a holiday table using a formula

问题描述

这是后续的

原始公式和导致问题的地方是位置AC3是静态日期&位置S2是动态的。 S2拥有一个允许闰年的动态公式：

  = DATE（YEAR（Q3）+ 1，MONTH（Q3） ，DAY（Q3-1））
  

位置Q3是指基本的日期公式：

  = TODAY（）
  

我希望这有帮助。

解决方案

将S9中的公式更改为：

  =日期（年（s2），1，S8）
  

这样做的原因是Date正在寻找要传递给它的3个整数。你所做的是把一个日期传给一个真正没有意义的一年的数字。您需要发送的是：

  =日期（2016,1,22）
  

您发送的是：

  =日期（5/5 / 2016,1,22）
或更准确地
 =日期（42496,1,22）
  

42496是excel如何存储日期。年度功能是删除日期的一部分，并将其作为整数返回。

  =年（5/6/2016）
这真的等于
 2016'一个整数
  

希望澄清一些事情。

你将所有的公式汇总到一个单元格并指出一年，考虑安排它们如下：

或

您只需要第3行或第4行，你喜欢什么此外，一旦您设置了一列假期，您应该可以将其复制到右侧。

This is a follow up to the earlier question.

The only differences between the original formula & the one that is causing a problem is location AC3 is a static date & location S2 is dynamic. S2 houses a dynamic formula that allows for a leap year:

=DATE(YEAR(Q3)+1,MONTH(Q3),DAY(Q3-1))

Location Q3 refers to a basic date formula:

=TODAY()

I hope this helpful.

解决方案

Change your formula in S9 to:

=Date(year(s2),1,S8)

The reason for this is Date is looking for 3 integers to be passed to it. What you did was pass it a date in the place of a year number that really did not make sense to excel. What you needed to send is:

=Date(2016,1,22)

What you sent was:

=Date(5/5/2016,1,22)
or more accurately
=Date(42496,1,22)

42496 is how excel stores a date. What the year function does is strip the year part of the date and return it as an integer.

=Year(5/6/2016)
this really equates to
2016 'an integer

Hope that clarifies some things.

When you have all your formulas condensed down to a single cell and pointed at one year, consider arranging them as follows:

or

You should only need row 3 or row 4, which ever you prefer. Also once you have one column of holidays set up, you should just be able to copy them to the right.

这篇关于使用公式建立假日表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！