To highlight a date exactly one year from today in Excel, select the date cells and create a formula-based conditional formatting rule using =AND(ISNUMBER($A2),INT($A2)=EDATE(TODAY(),12)), assuming the dates begin in A2. To flag dates in the next 12 months instead, use a range formula; the right choice depends on whether you mean one exact anniversary or any date within a year.
Choose what “1 year from date” means
Excel can compare dates against today, but “one year from a date” can describe several different rules. Decide what you need to flag before setting up formatting. A due date exactly one year from now is different from a date that falls anywhere in the coming year, and both differ from a date that has already passed its first anniversary.
- Exactly one year from today: Match one calendar date, such as November 2, 2027, when today is November 2, 2026.
- Within the next 12 months: Highlight dates from today through the same calendar date next year.
- At least one year old: Highlight dates on or before the date one year ago.
- One year after another date in the row: Compare two cells, such as a start date and a review date, rather than comparing with today.
The examples below assume Microsoft Excel for Microsoft 365 on Windows and that the dates are in column A, starting in A2. They use calendar-year anniversaries, not a fixed count of 365 days. This matters because a year can contain 366 days, and months have different lengths.
Set up a rule for the exact date one year away
For dates in A2:A100, select that range, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. With the first selected cell at A2, enter the formula below, choose Format to set a fill or font style, and confirm the rule. The dollar sign fixes the column while the row number remains relative, so Excel checks each row in the selected range.
With dates in A2:A100 and the rule applied to A2:A100, use this formula to match the calendar date exactly one year from today, ignoring any time stored in the cell: =AND(ISNUMBER($A2),INT($A2)=EDATE(TODAY(),12)). If today is November 2, 2026, the comparison date is November 2, 2027. Only a cell containing that date is highlighted; a date of November 3, 2027, does not match.
ISNUMBER prevents blanks and text from being treated as dates. Excel stores valid dates as serial numbers, so INT($A2) removes any time portion—for example, 6:00 PM—before comparing the calendar day. If your cells contain dates only, the formula still works. Apply a recognizable format, such as a light fill, and leave the date’s number format unchanged unless you also want to alter how it is displayed.
Highlight dates anywhere in the next 12 months
If you mean “coming due within one year,” an exact-date rule is too narrow. It would highlight just one day. Instead, set a lower and upper limit so every date from today through the date 12 months ahead qualifies.
With dates in A2:A100 and the rule applied to that range, use this formula to include today and the date exactly 12 months from today: =AND(ISNUMBER($A2),INT($A2)>=TODAY(),INT($A2)<=EDATE(TODAY(),12)). For example, if today is November 2, 2026, dates from November 2, 2026, through November 2, 2027, are included. Dates before today or after the upper limit are not.
The word “within” sometimes means strictly after today, excluding items due today. For that version, change the lower comparison from greater than or equal to TODAY() to greater than TODAY(). If you want a rolling period that includes the current time of day, a different comparison may be needed; these examples deliberately compare calendar dates by removing times with INT.
Flag dates that are at least one year old
For expirations, completed service periods, or records that need review after a year, you may want to flag dates that have reached or passed their one-year anniversary. This is a threshold rule, not an exact match, so it continues to highlight older dates too.
With the dates in A2:A100 and the rule applied to A2:A100, use this formula to highlight dates on or before the date 12 months ago: =AND(ISNUMBER($A2),INT($A2)<=EDATE(TODAY(),-12)). If today is November 2, 2026, November 2, 2025, and earlier dates qualify. To highlight dates that are older than one year but not the anniversary date itself, change <= to <.
For a warning before a year has passed, use the next-12-months rule or define a shorter lead time. For example, a 30-day warning is a different business rule from a one-year anniversary. Be explicit about whether the anniversary itself counts, especially when the formatting signals a deadline or an action someone must take.
Compare a date with another date in the same row
Sometimes “one year from date” means one year after a start date in the same record, rather than one year from today. If column A contains a start date and column B contains a target or review date, compare the two cells. This makes the rule move with each row and avoids relying on the current day.
With start dates in A2:A100, review dates in B2:B100, and the conditional formatting rule applied to B2:B100, use this formula to highlight a review date that matches the calendar anniversary of its start date: =AND(ISNUMBER($A2),ISNUMBER($B2),INT($B2)=EDATE($A2,12)). For example, if A2 is March 15, 2025, the formula matches a date of March 15, 2026, in B2. It does not match a date one day earlier or later.
To highlight an entire row based on a date in column A, select the table’s data rows, such as A2:D100, and use a formula that refers to the date column with a fixed column and relative row. With dates in A2:A100 and the formatting applied to A2:D100, the exact-one-year-from-today rule is: =AND(ISNUMBER($A2),INT($A2)=EDATE(TODAY(),12)). The dollar sign in $A2 keeps every cell in a row tied to column A, while the row number changes for each record.
Why EDATE is useful for anniversaries
EDATE returns a date a chosen number of months before or after a starting date. Using 12 months represents a calendar year, while using 1 month represents a calendar month. That is usually more suitable for anniversaries than adding 365, which can miss by a day around leap years.
Month-end dates are another reason to use a calendar calculation. If the starting date is the 31st and the destination month has fewer than 31 days, Excel returns the last day of that destination month. For example, adding 12 months to February 29, 2024, returns February 28, 2025, because February 2025 has no 29th day. Decide whether that month-end behavior matches the policy you are tracking; a special business definition may require a different formula.
TODAY() supplies the current date and updates when Excel recalculates the workbook. As a result, a conditional formatting rule based on today can change over time without editing the rule. If the workbook is set to manual calculation, or recalculation has not occurred, the display may not update immediately. Excel’s calculation mode is controlled from Formulas > Calculation Options; Automatic is the usual setting for a workbook that relies on current dates.
Common problems and how to fix them
- The rule never highlights anything: Check that the cells contain real Excel dates, not text that merely looks like a date. Changing the number format will not convert text into a date. Re-enter or convert the values, then test the rule again.
- The wrong rows are highlighted: Check the first row in the formula against the first row in the rule’s Applies to range. If the range begins at row 2, the formula should refer to row 2. A mismatch can shift every comparison.
- Only one day matches: That is expected for an exact anniversary rule. Use the lower-and-upper-limit formula if you intend to highlight all dates in the coming year.
- A date with a time fails to match: The time makes the stored value later than midnight. The examples use
INTto compare just the date portion. - Blanks or text behave unexpectedly: Keep the
ISNUMBERcheck in the formula. It limits matches to cells Excel recognizes as numeric date values. - The color does not appear: Open Home > Conditional Formatting > Manage Rules, check the rule’s formula and Applies to range, and look for another rule that may override the format. Also confirm that a visible fill or font style was selected.
Regional date display can also be confusing. A cell may show dates in a day/month order or month/day order depending on formatting and regional settings, but the conditional formatting formula evaluates the underlying date value. When checking a questionable cell, inspect the formula bar and test a known date rather than relying only on how the cell is displayed.
Conditional formatting compared with a date column
Conditional formatting changes how qualifying cells look; it does not change the stored date or create a permanent status value. That makes it useful for visual reminders in a live schedule. If you need to filter, count, export, or report the status, consider adding a separate helper column with a formula that returns a label such as Due soon or Overdue. That label can be filtered and used in reports independently of the color.
Color should not be the only way important information is conveyed. Add a clear column heading, status text, or other context so readers can understand the result in print, when colors are difficult to distinguish, or when the workbook is viewed with different display settings. Conditional formatting is a signal, not a substitute for documenting what the date means.
Frequently Asked Questions
Can Excel highlight a date exactly one year from today?
Yes. Use the exact-date rule with EDATE(TODAY(),12). It highlights a matching calendar date, not every date in the next year.
How do I highlight dates within the next 12 months?
Use a formula with both a lower limit of TODAY() and an upper limit of EDATE(TODAY(),12). This flags the whole date window rather than one anniversary day.
Does EDATE account for leap years?
It calculates by calendar months and returns the last day of a shorter destination month when the original day does not exist there. That is why a February 29 anniversary can fall on February 28 in a non-leap year.
Will the rule update automatically tomorrow?TODAY() changes when Excel recalculates. In a workbook using manual calculation, the displayed result may wait until recalculation; check Formulas > Calculation Options.
Can I highlight the whole row instead of just the date?
Yes. Apply the rule to the full table range and use a formula such as =AND(ISNUMBER($A2),INT($A2)=EDATE(TODAY(),12)), assuming the relevant date is in column A and the first data row is 2.
Is the setup different on Mac or Excel for the web?
The formula logic is the same in current Excel for Mac and Excel for the web. Ribbon placement or dialog labels may vary slightly. In Windows Microsoft 365, use Home > Conditional Formatting > New Rule and select the formula-based rule type.
Related
- Conditional Formatting Excel Whole Row: A Practical Guide
- Excel Conditional Formatting: Highlight Entire Rows
- Excel Conditional Formatting for Values Less Than a Cell
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Insert Row Shortcut: Windows and Mac KeysIn Excel for Microsoft 365 on Windows, select a cell in the row where you want a new…
- How to Edit a Filter in Google Sheets Without Losing DataTo edit a filter in Google Sheets, click the filter icon in the column header, change the selected…
- Unable to Add Page Numbers in Word? Fixes That WorkIf you’re unable to add page numbers in Word, click Insert > Page Number on Windows, choose a…
- Create a Vertical Line in an Outlook Email SignatureTo create a vertical line in an Outlook signature, use a two-column table and show only the border…
- Excel Not Equal: The <> Operator ExplainedIn Excel, “not equal to” is written as <>. Put it between two values or cell references to…
- How to Insert a Citation in Word OnlineTo insert a citation in Word Online, open the document and check the References tab for Insert Citation.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.