Excel IF formula for birth date range

Question

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

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.

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.

Hopefully someone can help me out,

Thank you

Answer 1

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

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.

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")

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 >.

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.

Answer 2

I think I've got a solution that solves what I understand your problem to be. I used B6 and B7 to hold the years for your range. B6 has 2009 in it, and B7 has 2012 entered in it.

=IF(B4="",IF(AND((DATE(B6,1,1)-B3)/30<=12,(DATE(B7,1,1)-B3)/30>=24),"Yes","No"),IF(AND((DATE(B7,1,1)-B3)/30>=72,(DATE(B7,1,1)-B4)/30>=72),"Yes","No"))

Breaking it down a bit:

Test to see if there is a date in B4

=IF(B4=""

If not, then we need to see if the child was less than 12 months old at the beginning of the period AND greater than 24 months at the end of the period. (using AND lets us make sure that the child turned 12 months after the start and 24 months before the end)

IF(AND((DATE(B6,1,1)-B3)/30<=12,(DATE(B7,1,1)-B3)/30>=24),"Yes","No")

If there are 2 dates, then check to see if both children were less than 72 months by the end date (B7).

IF(AND((DATE(B7,1,1)-B3)/30>=72,(DATE(B7,1,1)-B4)/30>=72),"Yes","No")

Answer 3

Excel recognizes the date as a numerical value. Below are my defined date ranges. I needed to develop something that would reference different VLOOKUP data based on date range criteria. This can be utilized all in one big formula and you just add as you go depending on your needs. Plenty of ways to probably write this.

Here is another way:

"=IF(ISBLANK(C[-7]),""""," & _
"IF(AND(C[-7]>=42571,C[-7]<=42572),(VLOOKUP(C[9],'C:\Users\etabakman\Desktop\[BillNet Master List - current.xlsx]07-20-2016'!C39:C41,2,FALSE))," & _

"IF(AND(C[-7]>=42573,C[-7]<=42582),(VLOOKUP(C[9],'C:\Users\etabakman\Desktop\[BillNet Master List - current.xlsx]07-22-2016'!C39:C41,2,FALSE))," & _

"IF(AND(C[-7]>=42583,C[-7]<=42586),(VLOOKUP(C[9],'C:\Users\etabakman\Desktop\[BillNet Master List - current.xlsx]08-01-2016'!C39:C41,2,FALSE))," & _

"IF(AND(C[-7]>=42587,C[-7]<=42593),(VLOOKUP(C[9],'C:\Users\etabakman\Desktop\[BillNet Master List - current.xlsx]08-05-2016 V2'!C39:C41,2,FALSE))," & _

"IF(AND(C[-7]>=42594,C[-7]<=42735),(VLOOKUP(C[9],'C:\Users\etabakman\Desktop\[BillNet Master List - current.xlsx]BillNet Master list'!C39:C41,2,FALSE))))))))"