Formula Errors

How to Use Excel Conditional Formatting for Dates Between Four and Five Years

Algirdas Jasaitis Oct 12, 2026 869 views

Question details

The user needs to highlight dates in a spreadsheet that fall specifically between four and five years from the current date.

How to Highlight Dates Between Four and Five Years in Excel

Product: Excel
Device & OS: not provided
Scenario: Applying conditional formatting rules based on dynamic date ranges spanning exactly 4 to 5 years into the past or future.
Observed behavior: To accurately identify and format cells containing dates within a specific 4 to 5-year timeframe relative to today's date.

Before you start

Ensure your target column is formatted as standard dates in Excel, and identify the top cell reference (e.g., H5) of the range you intend to format.

Solution 1Recommended

Use EDATE with AND for Precise Date Boundaries

Using EDATE is recommended because it calculates exact month boundaries, preventing the logical gaps that occur when measuring integer years.

The DATEDIF function returns completed years, which can leave no useful range between integer values for precise continuous timeframes. By combining the AND function with EDATE (multiplying the years by 12 months), you can perfectly isolate dates exactly between 48 and 60 months away.

Select your date range

Highlight the range of cells containing the dates you want to format. Note the first cell reference in this range (for example, H5).

Create a new formatting rule

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

Enter the custom formula

Choose 'Use a formula to determine which cells to format'. For future dates, enter: =AND($H5>EDATE(TODAY(),4*12),$H5<EDATE(TODAY(),5*12)). For past dates, enter: =AND($H5<EDATE(TODAY(),-4*12),$H5>EDATE(TODAY(),-5*12)).

Apply formatting

Click the 'Format' button, navigate to the 'Fill' or 'Font' tab to select your desired highlight style, and click 'OK' twice to apply the rule.

Use EDATE with AND for Precise Date Boundaries

Reference Alignment: Ensure the row number in your formula (like $H5) exactly matches the first row of your selected range to prevent formatting offsets.

Solution 2

Use DATEDIF for Completed Years

If you only need to check if exactly four or five full years have passed, DATEDIF provides an alternative approach.

Efficient Spreadsheet Formatting

Easily Apply Conditional Formatting with WPS Spreadsheet

WPS Office features a robust and highly compatible spreadsheet tool that makes it incredibly simple to apply complex date formulas and conditional formatting to your data.

1. Open your document: Launch WPS Spreadsheet and open the document containing your date data.
2. Access formatting tools: Select your data range, click on the 'Home' tab, select 'Conditional Formatting', and choose 'New Rule'.
3. Apply the date formula: Select 'Use a formula', input your specific EDATE timeframe formula, configure your background color, and click 'OK'.

100% compatible with Microsoft Excel formulas, including EDATE and DATEDIFIntuitive conditional formatting rule manager for seamless data analysisFree and lightweight alternative for managing complex spreadsheets

microsoft office alternative - wps office

Frequently Asked Questions

Why should I use EDATE instead of DATEDIF for highlighting these date ranges?

DATEDIF only calculates completed, integer years. If you are looking for a continuous, unbroken timeframe between exactly four and five years, DATEDIF can leave logical gaps. EDATE accurately measures exact boundaries in months, making it much more reliable for this scenario.

How do I apply this conditional formatting rule to an entire row instead of just the date cell?

Select the entire range of data you want to highlight, not just the date column. Then, ensure the column reference in your formula is absolute by locking it with a dollar sign (e.g., $H5). This forces the spreadsheet to evaluate the date column while applying the color to the entire row.

What if my conditional formatting highlights the wrong rows?

This offset issue usually occurs when the row number in your formula does not match the first row of your selected range. Verify that if you highlight your data starting from row 2 downwards, your formula specifically references row 2 (e.g., $H2).

Excel & Data Problems