Formula Errors

How to Highlight Upcoming Birthdays Within 7 Days in Excel

Huma Ashraf Ch Oct 9, 2026 871 views

Question details

The user wants to conditionally highlight dates representing birthdays that will occur within the next seven days, ignoring the original year of birth.

Highlight Upcoming Birthdays Within 7 Days Using Excel Formulas

Product: Excel
Device & OS: not provided
Scenario: Tracking upcoming events or birthdays using a spreadsheet to ensure no important dates are missed.
Observed behavior: Requires a single conditional formatting formula that normalizes different historical birth years to the current year and correctly highlights dates falling in the 7-day window ahead.

Before you start

Ensure your birthday data is formatted as valid Excel dates (e.g., MM/DD/YYYY) rather than text, so formulas can correctly extract the month and day.

Solution 1Recommended

Use MOD and DATE Formulas in Conditional Formatting

Create a custom conditional formatting rule using DATE, YEAR, TODAY, and MOD functions to calculate the distance to the next birthday while avoiding year-end transition errors.

Comparing historical dates directly to the current date will not work because the birth years are in the past. You must normalize the month and day to the current year.

By utilizing the MOD function combined with 365.25 (to account for leap years), the formula can perfectly handle year boundaries—such as when today is late December and the upcoming birthday is in early January.

Select the target date range

Highlight the column or range of cells containing the birthdays you want to format (for example, A2:A100).

Open the Conditional Formatting menu

Navigate to the Home tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the dropdown list.

Enter the custom formula

Choose 'Use a formula to determine which cells to format'. In the formula bar, enter: =MOD(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY(),365.25)<=7 (assuming A2 is the first cell in your selected range).

Set the highlight format

Click the 'Format' button, go to the 'Fill' tab, and choose a highlight color (e.g., yellow or green). Click 'OK' twice to apply the rule to your data.

Use MOD and DATE Formulas in Conditional Formatting

Pro Tip: This exact formula evaluates the number of days until the birthday occurs this year. If the result is 7 or less (including 0 for today), the cell will highlight.

Track Events Easily

Highlight Dates and Track Birthdays Effortlessly in WPS Office

WPS Spreadsheet offers powerful conditional formatting tools and full compatibility with Excel formulas. You can seamlessly apply this exact birthday-tracking method to monitor upcoming deadlines and events for free.

1. Open your workbook: Launch WPS Spreadsheet and open the document containing your birthday or event records.
2. Select your data: Click and drag to select the cells containing the dates you wish to monitor.
3. Apply Conditional Formatting: Go to the Home tab, click on 'Conditional Formatting', and select 'New Rule'.
4. Input the formula: Select the formula option, paste your MOD and DATE formula, set your preferred highlight color, and click 'OK'.

100% compatible with Microsoft Excel date formulas and conditional formattingEasy-to-use conditional formatting rules managerFree, lightweight, and fast spreadsheet processingBuilt-in functions like TODAY, MOD, and DATE operate exactly as expected

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting rule highlighting the wrong dates?

This usually happens if your dates are stored as text rather than actual date values, or if the formula references the wrong starting cell. Ensure your formula uses the active cell of your selection (e.g., A2) and that the data is formatted as Short Date.

How does the MOD function help with year-end birthdays?

When a birthday falls in early January and the current date is in late December, a simple subtraction results in a negative number. The MOD function wraps this negative difference around the 365-day year, correctly identifying it as being within the next 7 days.

Can I change the formula to highlight birthdays in the next 30 days instead?

Yes, simply change the '<= 7' at the end of the formula to '<= 30'. The conditional formatting rule will then automatically highlight any dates falling within the upcoming 30-day window.

Excel & Data Problems