Formatting Issues

How to Highlight Birth Months and Age 60 Using Conditional Formatting

Adam DavisAdam Davis Nov 11, 2026 869 views

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.

How to Highlight Birth Months and Age 60 Using Conditional Formatting
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the target data range

Highlight the cell range where you want the monthly data to be formatted, typically starting from the first data row under your monthly headers.

2
Open Conditional Formatting

Go to the Home tab on the ribbon, click on Conditional Formatting, and select 'New Rule'.

3
Enter the formula

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).

4
Apply the format

Click the 'Format' button, pick a highlight color from the Fill tab, and click OK to apply the rule to your timeline.

Highlight the Birth Month Using the MONTH Formula
Cell Referencing: Using the dollar sign ($) correctly is crucial. $A2 locks the column so the formula always checks the birth date, while B$1 locks the row so it always compares against the header.
Advanced Data Visualization

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. 1. Download and Install: Download WPS Office for free and open your date-tracking workbook in WPS Spreadsheet.
  2. 2. Select your range: Highlight your data timeline and navigate to the Home tab to find the Conditional Formatting tool.
  3. 3. Apply custom formulas: Select 'New Rule', choose the formula option, and input your date or age tracking formulas seamlessly.
  4. 4. Visualize data instantly: Set your custom highlight colors and instantly visualize the birth months and 60th birthdays across your sheet.
Fully compatible with Microsoft Excel formulas like MONTH, COUNTIFS, and EDATE.Intuitive Conditional Formatting manager to easily edit and visually track important dates.Free and lightweight, ensuring large data-tracking spreadsheets run smoothly.Supports advanced date formatting for custom monthly header displays.
microsoft office alternative - wps office

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.