How to Highlight Birth Months and Age 60 Using Conditional Formatting
Question details
The user needs to create conditional formatting rules to highlight specific birth months and identify the exact month a person turns 60 across a worksheet containing monthly columns.

- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Tracking employee or client ages and birth months across a timeline represented by monthly header columns.
- Observed behavior
- The goal is to automatically color or highlight cells representing the birth month and the first instance the person reaches age 60 without manually checking each row.
Ensure your spreadsheet headers are formatted as valid dates rather than plain text, as this will make formula calculations like EDATE and MONTH much more reliable.
Highlight the Birth Month Using the MONTH Formula
Use this conditional formatting rule when you need to match a birth date column to the corresponding monthly headers across a timeline.
This method relies on the MONTH function to extract the month number from the birth date and compares it to the month number of your column headers.
Highlight the cell range where you want the monthly data to be formatted, typically starting from the first data row under your monthly headers.
Go to the Home tab on the ribbon, click on Conditional Formatting, and select 'New Rule'.
Choose 'Use a formula to determine which cells to format'. Assuming your birth date is in column A (e.g., A2) and headers start at B1, enter the formula: =MONTH($A2)=MONTH(B$1).
Click the 'Format' button, pick a highlight color from the Fill tab, and click OK to apply the rule to your timeline.

Identify the First Month Reaching Age 60
Apply a COUNTIFS formula to locate the exact cell where a person reaches age 60 for the first time in a row.
Easily Manage Conditional Formatting and Formulas in WPS Spreadsheet
WPS Spreadsheet offers powerful built-in tools for applying complex conditional formatting rules, such as tracking birth months and age milestones. It supports advanced date and logical formulas seamlessly.
- 1. Download and Install: Download WPS Office for free and open your date-tracking workbook in WPS Spreadsheet.
- 2. Select your range: Highlight your data timeline and navigate to the Home tab to find the Conditional Formatting tool.
- 3. Apply custom formulas: Select 'New Rule', choose the formula option, and input your date or age tracking formulas seamlessly.
- 4. Visualize data instantly: Set your custom highlight colors and instantly visualize the birth months and 60th birthdays across your sheet.

Frequently Asked Questions
Why is my conditional formatting applying to the wrong cells?
This usually happens due to incorrect absolute and relative cell references. Ensure you are using the dollar sign ($) correctly in your formula, such as $A2 to lock the column where the birth date is stored, and B$1 to lock the row where your headers are located.
Can I calculate the exact date someone turns 60 instead of just the age?
Yes, you can use the EDATE formula to find the exact milestone date. For example, using the formula =EDATE(A2, 60*12) will return the exact date 60 years from the original birth date located in cell A2.
Why is the MONTH formula returning an error for my headers?
The MONTH function requires actual date values to work. If your headers are formatted as plain text (for example, typing 'Jan' or 'Feb' manually), the formula won't recognize them. Convert your headers to valid dates (like 1/1/2024) and use custom number formatting (e.g., 'mmm yy') to display them as month names.




