Excel formula for COUNTIFS and Date is + or - 1 day

Question

Please help with the following excel formula.

Need to compare column A to G which is a date column. Date may be off + or - 1 day then highlight the cell if column E and L match.

I made this formula for the conditional formatting, but it didn't get everything as the date is off sometimes by 1 day.

=COUNTIFS($G$1:$G$21,$A1,$L$1:$L$21,$E1)>0

Answer 1

Hello @Mary Ballard

Thank you for reaching out to Microsoft Q&A.

Based on the information you provided, I understand that you want to use conditional formatting to highlight cells when:

• The date in column A matches the date in column G, allowing for a ±1 day difference, and
• The values in column E and column L match.

Your current formula works for exact date matches but misses some cases because the dates can be off by one day. After reproducing this scenario in my test environment with sample data, the following approach produced the expected result.

Please make sure columns A and G are formatted as Date. Identify the formula delimiter used on your machine. Most English‑language Excel versions use a comma (,), but some regional settings use a semicolon (;)

Then, please follow the following steps:

Select the range you want to highlight (for example: E1:E10) > Go to Home > Conditional Formatting > New Rule > Choose “Use a formula to determine which cells to format” > Use this formula:

=COUNTIFS($L$1:$L$500,$E1,$G$1:$G$500,">="&INT($A1)-1,$G$1:$G$500,"<="&INT($A1)+1)>0

This should now correctly highlight all expected matches.

If your actual data structure is different from this sample (for example, different ranges or headers), feel free to share more details and I can adjust the formula accordingly.

---

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

To create a formula that counts occurrences where the date in column G is either one day before or one day after the date in column A, while also checking that the values in column E and L match, you can modify your existing formula to account for the date range. Here’s an updated version of your formula:

=COUNTIFS($G$1:$G$21, $A1-1, $L$1:$L$21, $E1) + COUNTIFS($G$1:$G$21, $A1, $L$1:$L$21, $E1) + COUNTIFS($G$1:$G$21, $A1+1, $L$1:$L$21, $E1) > 0

This formula checks three conditions:

1. If the date in column G is one day before the date in column A.
2. If the date in column G is the same as the date in column A.
3. If the date in column G is one day after the date in column A.

If any of these conditions are met and the corresponding values in columns E and L match, the formula will return TRUE, allowing you to highlight the cell accordingly in your conditional formatting rule.