QUERY: Return name if over a certain age from date of birth-CodePudding

Here is an example table:

     A        B
   ------  ----------
1 | Name | DOB
2 | John | 01/01/2011
3 | Tom  | 02/02/2002

I need to return the names of people over 11 years old, using only the two available columns.

In my mind, the query should be something along the lines of the following example, where [11 years old] represents the necessary magic to make the selection.

=QUERY(A1:B, "select A where B >[11 years old]", 1)

Unfortunately, I cannot add an age column with a formula to the data table.

Please feel free to suggest an alternate formula.

CodePudding user response：

try:

=ARRAYFORMULA(QUERY({A1:A, YEAR(TODAY())-YEAR(B1:B)}, "select Col1 where Col2 >11", 1))

or just:

={"Name"; FILTER(A2:A, YEAR(TODAY())-YEAR(B2:B)>11)}

CodePudding user response：

Depends how you read the question, if you take day and month into account and say that someone aged 11 years and one day is 'over 11 years old', then either

=ArrayFormula(QUERY({A2:A,date(year(B2:B) 11,month(B2:B),day(B2:B))},"select Col1 where Col2 < date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'", 0))

=ArrayFormula(QUERY({A2:A,edate(B2:B,12*11)},"select Col1 where Col2 < date '"&TEXT(TODAY(),"yyyy-mm-dd")&"'", 0))

Note that date and edate appear to give different results for someone born on 29th January in a leap year. Is a person born on 2004-02-29 'over 11' on 2015-03-01 or are they exactly 11 ? I'm not sure and I don't know if there is universal agreement on this.

Where Today() = 2022-05-13