Forum Discussion

Bolbie1's avatar
Bolbie1
Copper Contributor
Apr 20, 2022
Solved

Excel Formula based on dates

Hi Guys,    first of all im not a native English so there might be some weird phrasing going on.  I would like to create a special Excel sheet. I am trying to avoid duplications in purchasing. The...
BI & Data Analysis
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 20, 2022

    Bolbie1 

    Select A2:C4 (or however far down the data extend).

    The active cell in the selection should be in row 2.

    On the Home tab of the ribbon, select Conditional Formatting > New Rule...

    Select 'Use a formula to determine which cells to format'.

    Enter the formula

    =COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2,$C$2:$C$1000,">="&EDATE($C2,-2),$C$2:$C$1000,"<="&EDATE($C2,2))>1

    Adjust the ranges if needed; it doesn't matter if they extend further down than the data.

    Click Format...

    Activate the Fill tab.

    Select red.

    Click OK, then click OK again.

     

HansVogelaar's avatar
HansVogelaar
MVP
Apr 20, 2022

Bolbie1 

Select A2:C4 (or however far down the data extend).

The active cell in the selection should be in row 2.

On the Home tab of the ribbon, select Conditional Formatting > New Rule...

Select 'Use a formula to determine which cells to format'.

Enter the formula

=COUNTIFS($A$2:$A$1000,$A2,$B$2:$B$1000,$B2,$C$2:$C$1000,">="&EDATE($C2,-2),$C$2:$C$1000,"<="&EDATE($C2,2))>1

Adjust the ranges if needed; it doesn't matter if they extend further down than the data.

Click Format...

Activate the Fill tab.

Select red.

Click OK, then click OK again.

 

Resources