Formula Errors

How to Highlight Current Month and Year in Excel Using Conditional Formatting

Olivia Miller Sep 28, 2026 869 views

Question details

The user needs a conditional formatting rule to highlight cells in a specific column when the date matches the current month and year.

How to Highlight the Current Month and Year in Excel with Conditional Formatting

Product: Excel
Device & OS: not provided
Scenario: Tracking and highlighting current data based on dates falling within the present month and year.
Observed behavior: An earlier formula failed to work correctly, highlighting random and unrelated months (such as November and August) instead of the current month.

Before you start

Ensure that the cells in your target column contain valid Excel dates, rather than text strings, so the formula can accurately evaluate the month and year.

Solution 1Recommended

Use the EOMONTH Formula in Conditional Formatting

Applying the EOMONTH function guarantees an accurate comparison of the month and year against today's date, ignoring the specific day value.

The EOMONTH (End of Month) function returns the last day of the month for a specified date. By comparing the end of the month of the current date (TODAY) to the end of the month of your target cell, Excel perfectly aligns the dates to check if they share the same month and year.

Select the target range

Highlight the cells in your date column (e.g., Column E, starting from E2) where you want the conditional formatting to apply.

Open Conditional Formatting

Navigate to the 'Home' tab on the Excel ribbon, click on 'Conditional Formatting', and select 'New Rule' from the drop-down menu.

Enter the formula

Choose 'Use a formula to determine which cells to format'. In the formula box, enter exactly: =EOMONTH(TODAY(),0)=EOMONTH($E2,0)

Apply formatting

Click the 'Format' button, go to the 'Fill' tab, select your preferred highlight color, and click 'OK' twice to apply the rule.

Use the EOMONTH Formula in Conditional Formatting

Correct Absolute Referencing: Notice the dollar sign in $E2. This locks the column reference while allowing the row reference to change, ensuring the formatting applies accurately down the entire column.

Powerful Spreadsheet Tool

Highlight Dates Easily in WPS Spreadsheet

WPS Spreadsheet supports advanced conditional formatting and all standard Excel formulas, including EOMONTH, allowing you to highlight current months seamlessly.

1. Select your dates: Open your document in WPS Spreadsheet and select the column containing your date values.
2. Access Conditional Formatting: Go to the 'Home' tab, click 'Conditional Formatting', and choose 'New Rule'.
3. Input the formula: Select the option to use a formula and input =EOMONTH(TODAY(),0)=EOMONTH($E2,0) into the field.
4. Set color and apply: Click 'Format' to choose your highlight color, then click 'OK' to instantly format the current month's dates.

Fully compatible with Microsoft Excel formulas and conditional formatting rules.Free and lightweight alternative for quick data analysis and tracking.Intuitive interface for easily managing complex conditional formatting rules.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my conditional formatting highlighting the wrong months?

This usually happens if the formula references the wrong starting cell, uses an absolute reference (like $E$2 instead of $E2) when applied to a range, or if the cells contain plain text instead of valid date formats.

Can I highlight dates for the current year only, regardless of the month?

Yes, you can use a simpler formula in your conditional formatting rule to match only the year. Use =YEAR(TODAY())=YEAR($E2) to highlight any date falling in the current year.

How do I highlight the previous month using conditional formatting?

You can modify the EOMONTH formula to look back exactly one month by changing the zero in the TODAY function to -1. The formula would be: =EOMONTH(TODAY(),-1)=EOMONTH($E2,0).

Excel & Data Problems