Formulas to Highlight Dates by Month and Year

Updated 9 November 2026 · Standard formula, shown below

To highlight dates from a particular month and year in Excel or Google Sheets, use a conditional-formatting formula that checks the date’s year and month. For example, to highlight every date in March 2025 in cells A2:A100, use =AND(ISNUMBER(A2),YEAR(A2)=2025,MONTH(A2)=3).

How the formula works

Conditional formatting evaluates a rule for each cell in the selected range. The formula returns TRUE for dates that match the chosen year and month, and the formatting is applied to those cells. In the example, YEAR(A2)=2025 checks the year, while MONTH(A2)=3 checks for March. The ISNUMBER check prevents blank cells and text from being treated as matching dates.

Spreadsheet dates are stored as serial values, so year and month functions can inspect them even when they are displayed in a format such as Mar 15 or 15/03/2025. The display format does not determine the actual date value. A cell containing text that merely looks like a date may not be recognized as a date, however.

Highlight a fixed month and year

Use a fixed formula when you want one specific month highlighted, such as a reporting period. In Excel or Google Sheets, select the date cells and add a conditional-formatting rule using a custom formula. If the selected range begins at A2, enter:

=AND(ISNUMBER(A2),YEAR(A2)=2025,MONTH(A2)=3) for March 2025.
=AND(ISNUMBER(A2),YEAR(A2)=2025,MONTH(A2)=11) for November 2025.
=AND(ISNUMBER(A2),YEAR(A2)=2026,MONTH(A2)=1) for January 2026.

Choose a fill color or other format and save the rule. Use month numbers from 1 to 12: January is 1, February is 2, and December is 12. The year is a four-digit number in the formula, even if the cells display only a two-digit year.

The reference in the formula must correspond to the top-left cell of the selected range. For a range beginning at D5, use D5 throughout the formula. Spreadsheet conditional formatting adjusts a relative reference for each cell it checks, so the rule tests D6 for the next row, D7 after that, and so on.

Use cells to choose the month and year

If you want to change the highlighted period without editing the rule, store the chosen month and year in cells. For example, enter a month number in F1 and a year in G1. Apply this rule to the date range starting at A2:

=AND(ISNUMBER(A2),YEAR(A2)=$G$1,MONTH(A2)=$F$1)

The dollar signs make F1 and G1 absolute references, keeping the criteria cells fixed as the conditional-formatting rule evaluates other rows. Change F1 or G1 to update the highlighted month and year. You can also use a date in a single control cell, such as F1 containing any date in the month, and compare against its year and month: =AND(ISNUMBER(A2),YEAR(A2)=YEAR($F$1),MONTH(A2)=MONTH($F$1)).

For easier entry, a cell can contain a real date displayed as a month and year, such as March 2025. Make sure it is a spreadsheet date, not text. The formula reads the underlying date value, regardless of its display format.

Set up the rule in Excel or Google Sheets

The menu wording differs slightly between spreadsheet versions, but the core setup is similar:

Select the date range, including the first cell used in the formula.
Open conditional formatting. In Excel, look under Home; in Google Sheets, use Format.
Choose the option for a formula or custom formula rule.
Enter the formula, replacing A2 with the first cell of your selected range.
Choose the formatting style and apply or save the rule.

In Excel, the rule type is commonly called “Use a formula to determine which cells to format.” In Google Sheets, choose “Custom formula is.” If you want to color an entire row based on a date in column A, select the rows to format and use a formula such as =AND(ISNUMBER($A2),YEAR($A2)=2025,MONTH($A2)=3). The dollar sign before A locks the date column while the row number changes. Ensure the selected range and formula’s starting row agree.

Alternative: compare date boundaries

You can also highlight dates by checking whether they fall between the first day of a month and the first day of the following month. For March 2025, use:

=AND(ISNUMBER(A2),A2>=DATE(2025,3,1),A2<DATE(2025,4,1))

This method is especially useful when cells may contain date-and-time values. A date-time such as March 31, 2025 at 4:00 p.m. is still earlier than April 1, so it passes the upper-bound test. Using a less-than boundary for the next month avoids the common error of excluding dates with times on the final day. The YEAR/MONTH version is simpler when the cells contain dates without times.

Both approaches identify the same calendar month when the underlying values are valid dates. The boundary formula uses DATE(year,month,day); spreadsheet date functions handle month rollover, but explicit first-of-month and next-month dates are easiest to verify.

Common problems and checks

Wrong starting reference: If the selected range starts at A2 but the formula refers to A1, every result may be offset by one row. Match the formula reference to the range’s top-left cell.
Text instead of dates: A value that looks like a date may have been imported as text. Try changing its number format or converting it to a real date; formatting alone does not convert text.
Regional date order: Dates such as 04/05/2025 can mean different days depending on locale. Check the actual date value or use an unambiguous date entry.
Unwanted fixed references: A reference such as $A$2 always checks the same cell. Use A2 for a cell-by-cell range rule, or $A2 when checking column A across entire rows.
Conflicting rules: Another conditional-formatting rule may apply a competing style. Review the rule order and the ranges each rule covers.
Wrong month number: MONTH returns 1 for January through 12 for December; it does not use month names or zero-based numbering.

Frequently Asked Questions

Can I highlight a month every year, regardless of year?
Yes. To highlight every March, regardless of year, use =AND(ISNUMBER(A2),MONTH(A2)=3). Leave out the YEAR test.

How do I highlight the current month automatically?
Use =AND(ISNUMBER(A2),YEAR(A2)=YEAR(TODAY()),MONTH(A2)=MONTH(TODAY())). TODAY updates with the current date when the spreadsheet recalculates.

Can I highlight an entire row based on its date?
Yes. Select the row range and use a formula such as =AND(ISNUMBER($A2),YEAR($A2)=2025,MONTH($A2)=3) when the date is in column A and the first selected row is row 2.

Why does the formula highlight the wrong rows?
Check that the formula references the first cell in the selected range and that the row or column is not locked unintentionally with dollar signs. Also verify that the dates are real date values.

Does the date’s displayed format affect the result?
No. A real spreadsheet date can be displayed in many formats; the formula checks its stored value. Text that only resembles a date is different and may need conversion.

Should I use YEAR and MONTH or date boundaries?
Use YEAR and MONTH for a straightforward month-and-year test. Use a first-of-month and next-month boundary when date-time values may be present or you need an explicit range test.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.