Tracking important dates like birthdays and anniversaries is an excellent way to strengthen personal or workplace connections. Instead of manually tracking these important dates, Excel can do the work for you! This guide will help you create a dynamic method of detecting birthdays and work anniversaries between two dates. You can also use it to find them within a given month. This method utilizes both standard and advanced dynamic array formulas.
Setting Up the Data
Let’s start with a table of 20 fictitious names, birthdays, and work start dates.
Sample Data Table (A1:E21)
| Name | Birthday | Work Start Date |
|---|---|---|
| John McClane | 1955-03-15 | 1980-06-01 |
| Dutch Schaefer | 1948-11-20 | 1983-09-12 |
| Martin Riggs | 1957-07-08 | 1985-03-25 |
| Ellen Ripley | 1960-01-30 | 1986-12-05 |
| Frank Dux | 1959-05-22 | 1979-02-18 |
| Sarah Connor | 1965-12-10 | 1984-03-15 |
| John Rambo | 1947-04-25 | 1972-07-20 |
| Axel Foley | 1961-06-15 | 1984-09-25 |
| Marion “Cobra” Cobretti | 1955-09-05 | 1982-11-30 |
| Jack Burton | 1954-02-28 | 1986-08-01 |
| Snake Plissken | 1954-08-10 | 1981-05-10 |
| Tequila Yuen | 1962-07-18 | 1985-06-18 |
| Bennett | 1958-11-30 | 1983-03-08 |
| Chun-Li | 1968-05-09 | 1988-12-01 |
| Max Rockatansky | 1948-03-02 | 1975-04-25 |
| Nikki Wright | 1970-06-20 | 1991-07-19 |
| Dillon | 1957-10-12 | 1985-02-01 |
| Ivan Drago | 1963-12-15 | 1987-10-10 |
| James Braddock | 1951-01-25 | 1973-09-12 |
| George Nada | 1953-03-08 | 1978-04-15 |
Identifying Birthdays and Work Anniversaries Between Two Dates
Input Dates
Set up the following input cells:
- Start Date:
F2(e.g.,2025-01-01) - End Date:
G2(e.g.,2025-01-31)
Birthday Tracking Formula
To find birthdays between two dates, enter this formula in cell D2:
=IF(AND(TEXT(B2, "MM-DD") >= TEXT($F$2, "MM-DD"), TEXT(B2, "MM-DD") <= TEXT($G$2, "MM-DD")), "Yes", "No")
This formula checks if the month and day of the birthday fall within the date range. Copy this formula down the rows (to cell D21).
Work Anniversary Tracking Formula
To find work anniversaries between two dates, enter this formula in cell E2:
=IF(AND(TEXT(C2, "MM-DD") >= TEXT($F$2, "MM-DD"), TEXT(C2, "MM-DD") <= TEXT($G$2, "MM-DD")), "Yes", "No")
Identifying Birthdays and Anniversaries by Month
Input Month
To filter dates for a specific month, use H2 as the input cell for the month number (e.g., 1 for January).
Birthday Formula
For birthdays in a specific month, enter this formula in cell J2:
=IF(MONTH(B2)=$H$2, "Yes", "No")
Anniversary Formula
For work anniversaries in a specific month, enter this formula in cell K2:
=IF(MONTH(C2)=$H$2, "Yes", "No")
Advanced: Using Dynamic Array Formulas
Dynamic arrays simplify tracking by pulling matching data into a separate table automatically. Enter the formulas below into your spreadsheet. Because these are dynamic array formulas, place them far enough apart to avoid #SPILL! errors.
Birthdays Between Two Dates
=FILTER(A2:C21, (TEXT(B2:B21, "MM-DD") >= TEXT(F2, "MM-DD")) * (TEXT(B2:B21, "MM-DD") <= TEXT(G2, "MM-DD")), "No matches")
Work Anniversaries Between Two Dates
=FILTER(A2:C21, (TEXT(C2:C21, "MM-DD") >= TEXT(F2, "MM-DD")) * (TEXT(C2:C21, "MM-DD") <= TEXT(G2, "MM-DD")), "No matches")
Birthdays in a Specific Month
=FILTER(A2:C21, MONTH(B2:B21) = H2, "No matches")
Work Anniversaries in a Specific Month
=FILTER(A2:C21, MONTH(C2:C21) = H2, "No matches")
Example Outputs
Input:
- Start Date:
2025-01-01 - End Date:
2025-02-28 - Month:
2
Output for Birthdays between Two Months:
| Name | Birthday | Work Start Date |
|---|---|---|
| Ellen Ripley | 1960-01-30 | 1986-12-05 |
| James Braddock | 1951-01-25 | 1973-09-12 |
| Jack Burton | 1954-02-28 | 1986-08-01 |
Output for Anniversaries in a Specific Month (2):
| Name | Birthday | Work Start Date |
|---|---|---|
| Frank Dux | 1959-05-22 | 1979-02-18 |
| Dillion | 1957-10-12 | 1985-02-01 |
Bonus: Visualize with Conditional Formatting
Highlight Dates in Range
- Select the Birthday or Work Start Date column.
- Go to Home > Conditional Formatting > New Rule.
- Add the formula below.
- Add a fill format to highlight cells, like a light green fill color.
=AND(
OR(AND(MONTH(B2) = MONTH($F$2), DAY(B2) >= DAY($F$2)),
MONTH(B2) > MONTH($F$2)),
OR(AND(MONTH(B2) = MONTH($G$2), DAY(B2) <= DAY($G$2)),
MONTH(B2) < MONTH($G$2))
)
Highlighting ensures you can quickly spot relevant dates.
Conclusion
With these formulas and dynamic arrays, you can efficiently manage and track important dates. Whether you’re organizing celebrations or improving team morale, Excel’s powerful tools guarantee you never miss a birthday or work anniversary.
