出生日期范围的Excel IF公式 [英] Excel IF formula for birth date range

问题描述

我希望有人可以帮助我解决If/Then公式:

I was hoping someone could help me out with and If/Then formula:

我需要一个公式，该公式可以告诉我在单元格B3中输入的出生日期是否在8/01/2009到12/31/2012年之间的12-24个月之间.例如，某个出生于2008年11月1日的人在2009-2012年之间的12-24个月之间，因此该值应在单元格D4中返回是".如果孩子是12个月以下或24个月以上，则单元格值将为否.

I need a formula that will tell me if a birth date that is entered in Cell B3 is between the age 12-24 months of age in the years 8/01/2009 to 12/31/2012. For Example, someone who was born 11/01/2008 was between 12-24 months in the years 2009-2012 therefore the value should return a YES in Cell D4. If the child was under 12 months or over 24 months the Cell value will be no.

在单元格D4中，如果单元格B3和单元格B4中有两个出生日期且在8/01/2009年至2012年12月31日之间都未满6岁，则值应返回是".例如，在单元格B3中输入了出生日期2005年11月1日，在单元格B4中输入了出生日期2004年8月1日，因此单元格D4中的值应为yes.如果两个孩子都未满6岁，则单元格值将为否".

Also in Cell D4 a value should return a Yes if in Cell B3 and Cell B4 has two birth dates and both were under 6 years old in the years 8/01/2009 up to 12/31/2012. For Example, in cell B3 the birth date was entered 11/1/2005 and in cell B4 the birth date that was entered is 8/1/2004 so the Value in cell D4 should be yes. If both children were not under 6 years old then the cell value will be No.

希望有人能帮助我

谢谢

推荐答案

在创建Excel公式之前，您首先需要做一些数学运算来简化问题.

Before creating the Excel formula, you first simplify the problem by doing a bit of math.

属于该日期范围的最小的孩子在2012年12月31日恰好是12个月大，因此出生日期是2011年12月31日.

The youngest child to fall in the date range would be exactly 12 months old on 12/31/2012, so would have a birthdate of 12/31/2011.

该日期范围内的最大的孩子在2009年8月1日正好是24个月大，出生日期是2007年8月1日.

The oldest child in the date range would be exactly 24 months old on 8/01/2009, with a birthdate of 8/01/2007.

此公式将检查某个范围内的日期:

This formula will check for a date in a certain range:

=IF(AND(B3>=DATE(2007,8,1),B3<=DATE(2011,12,31)),"Yes","No")

您需要仔细检查边界条件.例如，如果孩子正好在2009年8月1日有1岁生日，您是否仍要包括他们?如果不是，请将>=更改为>.

You need to double-check your boundary conditions. For instance, if the child has a 1st birthday exactly on 8/01/2009 do you still want to include them? If not, change >= to >.

第二个问题也是四个日期比较的与"运算，您现在可以计算出. 唯一棘手的事情是B3或B4为空白时.使用日期比较时，空白或0等于12/31/1899.这将使当前年龄超过100岁，这将使年龄测试失败.

The second problem is also an AND'ing of four date comparisons, which you can now work out. The only tricky thing is when B3 or B4 are blank. With date comparisons, blank or 0 are equivalent to 12/31/1899. That will give a current age of over 100 years old, which will fail the age test.

这篇关于出生日期范围的Excel IF公式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！