WPS Spreadsheets: Transfer Birthdays Between Yearly Excel Calendars
Question details
The user needs a flexible way to reuse birthdays from a 2023 Excel calendar in a similar 2024 calendar without copying separate monthly sheets by hand.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The birthdays are currently organized across yearly or monthly calendar worksheets, and the user wants one reusable calendar that can display events for a selected year and month.
- Observed behavior
- Birthdays must be manually copied between yearly calendar worksheets, which makes updates difficult and can miss multiple events on the same date.
Review the existing 2023 calendar and collect each birthday or event into a list with a name, month, and day. In the new workbook, reserve cells for selecting the calendar year and month before building the display formulas.
Create one birthday table and display events with selectable calendar formulas
Use this approach when you want one source of truth that can show birthdays for any year or month without copying monthly worksheets.
A single Excel table is easier to maintain than separate monthly sheets. Store each event as a row, then let the calendar sheet use the selected year and month to calculate dates and return matching names.
For multiple birthdays on one date, use TEXTJOIN with FILTER so every matching event appears in the same calendar cell. Recurring events can remain in the table with their month and day, without requiring a new row for every year.
In WPS Spreadsheets, open a blank or existing workbook and choose Insert > Table. Add columns such as Name, Month, Day, and Event, then enter all birthdays copied from the 2023 calendar. Name the table Birthdays from the Table Design tab if the table name is not already set.
On the calendar sheet, enter the target year in cell B1 and the target month number in cell B2. Use Data > Validation, or Data > Dropdown, to create selectable values for the month and year when those commands are available in your WPS version.
In the first date cell, such as A4, enter =DATE($B$1,$B$2,1). In the cells below or across the calendar, use =A4+1 and fill the formula to create the dates for the selected month. Format the cells as dates from Home > Number Format.
In the event cell next to a date in A4, enter =IFERROR(TEXTJOIN(", ",TRUE,FILTER(Birthdays[Name],(Birthdays[Month]=MONTH(A4))*(Birthdays[Day]=DAY(A4)))),""). Replace A4 with the relevant date cell, then fill the formula across the calendar. The result lists all birthdays matching that date.
Use a helper list when FILTER is unavailable
Use this fallback in an older WPS Spreadsheets version that does not support the FILTER function or structured table references.
Build a reusable birthday calendar in WPS Spreadsheets
WPS Spreadsheets lets you keep birthdays in one structured table and use formulas to display matching events for a selected month and year. This avoids manually maintaining separate 2023 and 2024 worksheets while preserving familiar spreadsheet editing and calendar formatting.
- 1. Open WPS Spreadsheets: Launch WPS Office and select Spreadsheets to open the existing Excel calendar or create a new workbook.
- 2. Convert the event list to a table: Select the birthday data and choose Insert > Table, then add Name, Month, Day, and Event columns.
- 3. Add calendar selectors: Enter a year and month on the calendar sheet and use Data > Validation or Data > Dropdown to make them selectable.
- 4. Insert the matching formula: Use DATE to generate calendar dates and FILTER with TEXTJOIN to display all birthdays matching each date.
Frequently Asked Questions
Can I use the same birthday list for both 2023 and 2024 calendars?
Yes. Store each birthday with its month and day in the Birthdays table, then change the calendar year selector from 2023 to 2024. The calendar dates update while recurring birthdays remain in the same table.
How do I show two birthdays that fall on the same date?
Use a TEXTJOIN and FILTER formula in the event cell, such as =IFERROR(TEXTJOIN(", ",TRUE,FILTER(Birthdays[Name],(Birthdays[Month]=MONTH(A4))*(Birthdays[Day]=DAY(A4)))),""). This combines all matching names in one cell.
Do I need a separate worksheet for every month?
No. A single calendar sheet can calculate the selected month from the year and month cells. The DATE formula generates the dates, while FILTER returns the relevant events from the central table.
How can I support recurring events such as the third Saturday in August?
Add recurrence information to the event table, such as event type, weekday, occurrence number, and month. Use a separate formula that checks the generated date's weekday and occurrence number, because a fixed month-and-day lookup does not identify relative dates such as the third Saturday.
What if my WPS version does not recognize FILTER?
Use the helper-list fallback with Data > Filter, or replace FILTER with a compatible INDEX and SMALL array formula. Older versions may also require Ctrl+Shift+Enter to confirm array formulas.
