How to Display Birthdays for the Current Week in Excel
Question details
The user needs a dynamic formula to extract and display names from a master list whose birthdays fall within the current week, including weekend dates, formatted neatly in a single cell.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a weekly dashboard or schedule that automatically highlights upcoming birthdays by matching dates regardless of the year.
- Observed behavior
- Needs to correctly match the day and month from a birthday list to the current week's headers and display multiple resulting names stacked on separate lines within one cell.
Ensure your spreadsheet software supports Dynamic Array functions like FILTER and TEXTJOIN (available in modern Excel versions and WPS Office). Prepare a master list of names in one column and their corresponding birth dates in another.
Use TEXTJOIN and FILTER to Extract Matching Birthdays
This method combines the FILTER function to find matching month and day values, and the TEXTJOIN function to display multiple names in a single cell separated by line breaks.
By comparing only the DAY and MONTH values, this formula ignores the year of birth, ensuring the birthday matches the current calendar dates perfectly.
Select the row where you want to display the names (e.g., Row 15), right-click the row number on the left, and choose 'Row Height' to increase it so multiple names will fit.
Click the target cell (e.g., C15) under your first date header (C14) and enter the formula: =TEXTJOIN(CHAR(10),TRUE,FILTER($A$3:$A$6,(DAY($B$3:$B$6)=DAY(C14))*(MONTH($B$3:$B$6)=MONTH(C14)),"")). Adjust the $A$3:$A$6 to match your Names column and $B$3:$B$6 to match your Dates column.
With the cell still selected, navigate to the 'Home' tab, click 'Wrap Text', and set the vertical alignment to 'Top Align' so the names stack cleanly.
Click and drag the fill handle at the bottom right of the cell to copy the formula across the rest of the date columns. Add your weekend dates to the heading row and drag the formula into those columns as well.
Track Birthdays Effortlessly with WPS Spreadsheet
WPS Office fully supports advanced dynamic array formulas like FILTER and TEXTJOIN, making it incredibly easy to build dynamic dashboards, schedules, and track weekly events without compatibility issues.
- 1. Prepare your data: Open WPS Spreadsheet and format your master birthday list with Names in Column A and Dates in Column B.
- 2. Apply the formula: In your weekly view, select the cell under your target date and enter the =TEXTJOIN(CHAR(10), TRUE, FILTER(...)) formula.
- 3. Format for readability: Click the 'Wrap Text' icon on the Home tab to ensure multiple names are displayed neatly on separate lines.
- 4. Fill across dates: Drag the fill handle to apply the dynamic formula across the rest of the week's dates.
Frequently Asked Questions
Why does my TEXTJOIN formula show a #NAME? error?
This error occurs if your spreadsheet software version is older and does not support the TEXTJOIN or FILTER functions. Consider upgrading to the latest version of WPS Office or Microsoft 365, which fully support these dynamic functions.
Can I display the results horizontally instead of vertically?
Yes. Instead of using CHAR(10) for a line break in the TEXTJOIN formula, you can use a comma and a space (", ") as the delimiter. This will list the names side-by-side in the cell, fitting horizontally.
Why are the names not displaying on separate lines even with CHAR(10)?
For CHAR(10) to visually separate items onto new lines, the 'Wrap Text' feature must be enabled for that specific cell. Select the cell, navigate to the Home tab, and click the 'Wrap Text' button.
How do I add weekend dates if they are missing from my headings?
Simply insert new columns next to your Friday date, type in the respective weekend dates (Saturday and Sunday) following the same date format as the rest of the week, and drag the formula into the new cells beneath them.
