How do I filter a range that contains a date of birth column for birthdays that fall in the next 30 days or the next ‘n’ days in Google Sheets?

To filter a range for upcoming birthdays, we can use the FILTER function along with various date functions in Google Sheets.

For this example, we will use the following sample data which contains names in A1:A and dates of birth in B1:B.

Step 1: Converting Birthdays to This Year’s Birthdays

The following array formula will convert the birthdays to this year’s birthdays:

=ArrayFormula(IFERROR(DATE(YEAR(TODAY()), MONTH(DATEVALUE(B2:B)), DAY(DATEVALUE(B2:B)))))

You can enter it in cell C2, provided C2:C is blank. Then select C2:C and click Format > Number > Date to apply the date formatting.

How does this formula work?

The formula uses the DATE function to convert the birthdays to this year’s birthdays.

Syntax:

DATE(year, month, day)

Where:

• year: YEAR(TODAY()) – returns the current year from today’s date.
• month: MONTH(DATEVALUE(B2:B)) – returns the months from the birthdays.
  The DATEVALUE function ensures that blank cells return errors; otherwise, the MONTH function will return #12 for blank cells.
• day: DAY(DATEVALUE(B2:B)) – returns the days from the birthdays.
  The DATEVALUE function ensures that blank cells return errors, thus preventing the DAY function from returning #30 for blank cells.

The IFERROR function removes errors, and the ArrayFormula ensures that the date functions work in an array.

We have completed the key step in filtering upcoming birthdays in Google Sheets. Let’s move to the next step now.

Step 2: Checking if This Year’s Birthdays Fall Within N Days

We want to filter the upcoming birthdays that fall within 30 days. So here, ‘n’ is 30.

Enter the following formula in cell D2 to return TRUE or FALSE Boolean values. TRUE means the birthdays fall within 30 days; FALSE means they do not.

=ArrayFormula(IF(C2:C="",,ISBETWEEN(C2:C, TODAY(), TODAY()+30, FALSE)))

Note: To filter upcoming birthdays that fall within the next week, replace +30 in the formula with +7.

The core part of this test is the ISBETWEEN function, and the syntax of this function is as follows:

ISBETWEEN(value_to_compare, lower_value, upper_value, [lower_value_is_inclusive], [upper_value_is_inclusive])

Where:

• value_to_compare: C2:C – This year’s birthdays.
• lower_value: TODAY() – today’s date.
• upper_value: TODAY()+30 – today’s date plus 30 days.
• lower_value_is_inclusive: FALSE.

The ISBETWEEN function returns TRUE for the dates that fall between today’s date and today’s date plus 30 (inclusive).

Step 3: Filtering for Upcoming Birthdays

We are in the final step. You just need to filter the range using a condition. Let’s move on to that formula.

=FILTER(A2:B, D2:D)

Output:

The formula adheres to the syntax FILTER(range, condition1, [condition2, …]).

The formula returns the rows wherever D2:D contains TRUE.

This way, we can filter data by upcoming birthdays in Google Sheets.

If you want to avoid using helper columns, delete the formulas in C2 and D2 and use the following formula directly:

=FILTER(A2:C, ISBETWEEN(IFERROR(DATE(YEAR(TODAY()), MONTH(DATEVALUE(B2:B)), DAY(DATEVALUE(B2:B)))), TODAY(), TODAY()+30, FALSE))

Resources

• How to Sort by Date of Birth in Google Sheets (Formula Options)
• Calculating Age from Birthdate (DOB) in Google Sheets
• AGE_CALC – A Named Function to Calculate Age or Duration in Google Sheets