SOLVED

Excel Formula based on dates

Copper Contributor

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 idea is that products with the same product characteristics (country, variety) cannot be purchased more than once in a period of less than 2 months. See screenshot with example "apples".
According to the logic, this would work like this: If identical product characteristics appear more than once in a period of less than two months, a "message" will appear. Example: red field.
If anyone would like to help me, I would be very happy! Kind regards

 

Bildschirmfoto 2022-04-20 um 10.08.21.png

5 Replies
best response confirmed by Bolbie1 (Copper Contributor)
Solution

@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.

 

S1314.png

@Bolbie1 

The formula in German is

 

=ZÄHLENWENNS($A$2:$A$1000;$A2;$B$2:$B$1000;$B2;$C$2:$C$1000;">="&EDATUM($C2;-2);$C$2:$C$1000;"<="&EDATUM($C2;2))>1

@Hans Vogelaar thank you so so much for the help and the formula in german! I've got one problem though, the rows are not changing color. Do you know where the problem is? 

 

Bildschirmfoto 2022-04-20 um 11.43.41.png

@Bolbie1 

I have attached the workbook I used to test the rule. Perhaps that will help.

If not: could you attach a sample workbook demonstrating the problem, or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?

Perfect, that file works on my laptop! Thank you so much Hans! Have a great day!
1 best response

Accepted Solutions
best response confirmed by Bolbie1 (Copper Contributor)
Solution

@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.

 

S1314.png

View solution in original post