Formatting Issues

How to Apply Conditional Formatting Based on Name and Date in Excel

Tauseeq Magsi Sep 30, 2026 868 views

Question details

The user needs to highlight names in a specific column if they appear more than a designated number of times within the last month, using dates located in another column.

How to Apply Conditional Formatting Based on Name and Date in Excel

Product: Excel
Device & OS: not provided
Scenario: Tracking and highlighting frequent occurrences in a dataset over a rolling 30-day period, such as employee attendance, sales activities, or event participation.
Observed behavior: Requires a custom formula-based conditional formatting rule that evaluates both a text string (name) and a dynamic date range (last 30 days) to trigger the cell highlight.

Before you start

Ensure your date column is formatted correctly as Dates (not text) so the formula can accurately calculate the rolling 30-day time range.

Solution 1Recommended

Use a Custom COUNTIFS Formula for Conditional Formatting

Apply a custom formula rule to count occurrences based on multiple criteria, triggering the formatting only when both the name matches and the date falls within the last 30 days.

The COUNTIFS function allows you to set multiple criteria across different ranges. By combining this with the TODAY() function, you can create a dynamic rule that always checks the last 30 days relative to the current date.

Select the data range

Highlight the cells in your target column (e.g., A2:A200) that contain the names you want to conditionally format.

Open Conditional Formatting settings

Navigate to the Home tab on the top ribbon, click on 'Conditional Formatting' in the Styles group, and select 'New Rule'.

Enter the COUNTIFS formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter: =COUNTIFS($A$2:$A$200,A2,$B$2:$B$200,">="&TODAY()-30)>x (replace 'x' with your desired threshold number).

Configure the highlight format

Click the 'Format' button, choose your preferred fill color or text styling to highlight the frequent names, and click 'OK' to apply the rule to your spreadsheet.

Use a Custom COUNTIFS Formula for Conditional Formatting

Understanding relative and absolute references: In the formula, the ranges like $A$2:$A$200 are absolute (locked with dollar signs) so they don't shift, while the criteria cell A2 is relative so it updates for each row evaluated.

Advanced Spreadsheet Tools

Use WPS Spreadsheet for Advanced Conditional Formatting

WPS Spreadsheet offers a robust set of data analysis and visualization tools. You can easily set up complex conditional formatting rules using COUNTIFS and other advanced formulas to manage your data.

1. Open your file in WPS Spreadsheet: Launch WPS Office and open the workbook containing your name and date columns.
2. Access Conditional Formatting: Highlight the names range, go to the Home tab, and click the 'Conditional Formatting' icon.
3. Apply your custom rule: Click 'New Rule' > 'Use a formula to determine which cells to format', paste your COUNTIFS formula, configure the highlight color, and save.

Completely free and lightweight alternative to Microsoft ExcelHigh format compatibility for Microsoft Excel files, ensuring conditional formatting rules and COUNTIFS formulas work seamlesslyIntuitive user interface for quick data highlighting and efficient spreadsheet management

microsoft office alternative - wps office

Frequently Asked Questions

Why is my COUNTIFS formula highlighting the wrong rows in Excel?

This usually happens if your selected data range doesn't align with the first cell referenced in your formula. If you selected A2:A200, the relative reference in your formula must specifically point to A2, not A1.

Can I change the dynamic 30-day range to a specific calendar month?

Yes. Instead of using ">="&TODAY()-30, you can modify the date criteria in the COUNTIFS formula to include ">="&DATE(2023,10,1) and "<="&DATE(2023,10,31) to restrict the counts exclusively to October 2023.

Will the conditional formatting update automatically when I add new data rows?

It will update as long as the newly added data falls within the absolute range specified in your rule (e.g., up to row 200). To make the formatting apply automatically to all new entries without adjusting the formula, format your dataset as an Excel Table first.

Document & File Issues