How to Check If a Date Is Within 7 Days of Another Date in Excel (7 Methods)

Dataset Overview

We’ll use the following dataset to demonstrate the methods. The dataset contains three columns: Product, Order Date and Delivery Date.

Method 1 – Use of IF Function

• Select a new cell (let’s say E5) where you want to display the result.
• Enter the following formula in cell E5:

• Press ENTER to get the result.

• Drag the Fill Handle icon to autofill the formula for the remaining cells (E6:E11).

Method 2 – Using the DAYS Function

• Select a new cell (E5).
• Enter the formula:

This calculates the duration in days between the end date (D5) and the start date (C5).

• Press ENTER to get the result.

• Double-click the Fill Handle icon to autofill the formula for the other cells (E6:E11).

Method 3 – Combining Functions

• Choose a new cell (E5).
• Enter this formula:

• Press ENTER to see the result.

• Autofill the formula for the other cells (E6:E11).

Method 4 – Applying IF & AND Functions

• Select a new cell (let’s say E5) where you want to display the result.
• Enter the following formula in cell E5:

• Press ENTER to get the result.

• Drag the Fill Handle icon to autofill the formula for the remaining cells (E6:E11).

Method 5 – Employing Conditional Formatting

• Select the data range (D5:D11) for which you want to apply conditional formatting.
• Go to the Home tab and click on Conditional Formatting.
• Choose New Rule…

• In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
• Enter the formula:

This formula checks if the difference between the dates in cells D5 and C5 is less than 7.

• Click on the Format button.

• In the Format Cells dialog box, choose a light color (e.g., Green) for the fill. Adjust font color if needed.
• Press OK to apply the formatting.

• Confirm by clicking OK on the New Formatting Rule dialog box. You’ll see the sample formatting in the Preview box.

• The colored dates within 7 days of another date will be visually highlighted.

Method 6 – Using the Data Validation Tool to Know If a Date Is Within 7 Days of Another Date

• Select the cell range where you want to insert data.
• Go to the Data tab and click on Data Validation.
• Choose Data Validation…

In the Data Validation dialog box:

• Select Custom under Allow.
• Enter the formula:

• This formula checks if adding 7 days to the start date (C5) results in a value greater than the end date (D5).
• Press OK to apply the validation.

You will see the following result.

Method 7 – Use of “A Date Occurring” Feature

• Select the data range (D5:D11) for which you want to apply conditional formatting.
• Go to the Home tab and click on Conditional Formatting.
• Choose Highlight Cells Rules and select A Date Occurring…

• In the A Date Occurring dialog box:
  • Choose In the last 7 days from the drop-down menu under Format cells that contain a date occurring:
  • Select your preferred color (e.g., Green Fill with Dark Green Text).
• Press OK to apply the changes.

Now, the colored dates within 7 days of another date will be visually highlighted.

Things to Remember

• If you simply want to check whether a date is within 7 days of another date, you can use method 2 (DAYS function).
• If you need a logical result based on the current date, consider using method 3 (a combination of functions).

Practice Section

You can practice the explained methods by yourself.

Download Practice Workbook

You can download the practice workbook from here:

<< Go Back to Dates | Compare | Learn Excel