我有一个日期列表,需要一个计算以下内容的公式:
如果 [DATE] 中的月份是 10 月、11 月或 12 月,则结果为 [DATE] 年份起 6 年后的 11 月 1 日;如果不是,则结果为 [DATE] 年份起 5 年后的 11 月 1 日
这是我目前的公式:
=IF(OR(MONTH(G2)=MONTH(10),MONTH(11),MONTH(12)),(DATE(YEAR(G2)+6,11,1)),(DATE(YEAR(G2)+5,11,1)))
问题是这样的:
G2 = 2014 年 11 月 19 日,结果是 2020 年 11 月 1 日...这是正确的。
但是如果我在下一行使用公式:
H2 = 2016 年 5 月 16 日,结果是 2022 年 11 月 1 日……这是不正确的。
我已经摆弄这个问题很长时间了,更改了公式等等,但要么出现错误,要么给出了错误的日期结果。
请帮忙!
答案1
Month() 函数返回一个整数。如果使用MONTH(10)
该函数,则返回 1 表示一月,因为数字 10 将转换为 1900 年 1 月 10 日,而不是十月。
使用 OR() 时,需要完整写出所有条件。不能仅用逗号分隔它们。
按照您编写的 OR 条件,它将始终返回 true,因为 Month(11) = 1 且 1 等于 TRUE。仅凭这一点就足以使整个 OR 函数返回 TRUE。
解决此类公式问题的提示:使用“公式”功能区上的“计算公式”命令并逐步执行公式。您可以看到每个元素解析的结果,然后可以采取纠正措施。
最后,不要使用过多的括号。这只会让公式变得复杂。
修正后的公式为:
=IF(OR(MONTH(G2)=10,MONTH(G2)=11,MONTH(G2)=12),DATE(YEAR(G2)+6,11,1),DATE(YEAR(G2)+5,11,1))
当然,您只需要检查月份是否晚于 9 月,因此您的公式可以缩短为
=IF(MONTH(G2)>9,DATE(YEAR(G2)+6,11,1),DATE(YEAR(G2)+5,11,1))
为了使其更高效,您可以将 IF 条件移到添加到年份的参数中。
=Date(YEAR(G2)+IF(MONTH(G2)>9,6,5),11,1)
答案2
由于您的标准仅为十月、十一月和十二月,因此我想建议两种可能的方法,使用 IF 和 OR,如果月份不在 1 到 12 之间则返回错误。
方法 1:
=IFERROR(IF(OR(MONTH(G2)={10,11,12}),DATE(YEAR(G2)+6,11,1),DATE(YEAR(G2)+5,11,1)),"Wrong month only 1-12")
方法 2:
=IFERROR(DATE(YEAR(G2)+IF(OR(MONTH(G2)={10,11,12}),6,5),11,1),"Wrong month only (1-12)")
笔记:
- 日期格式为
MM/DD/YY
。 IF(OR(MONTH(G2)={10,11,12})
检查月份是否是十月、十一月或十二月。IFERROR
如果月份不在 1 到 12 之间,则返回错误消息。