Conditional formatting with special conditions

Hi
for me the easiest way is to use a Pivot Table, grouping dates as required ( see attached)
Of course there are other ways with some complicated formulas...

With year in D2:
Use this CF in cell B4:
Min:

Please Login or Register  to view this content.

Max: change 15 to 14
In attachment is sample of year condition

For other set of criteria,
Year + quarter
Year + month
Year + week

adapted with CF formula in relevant columns (G to N)

Thank you very much bebo021999! Yearly worked fine with the formula provided, but quarter, month and week not really. Based on the idea that I got to get these values for each week, I should be getting more TRUE values for quarter, month and week, which is not the case. You guys helped me so much I try to figure out whats the problem now.

EDIT: Also found help on another website (can't post it due to restriction...) BUT they basically used the following formula for monthly max/min values: =MAX(IF((YEAR($B$3:$B$16)= YEAR($B3))* (MONTH($B$3:$B$16)=MONTH($B3)), $C$3:$C$16, ""))=$C3

This approach seems easier, but I cannot figure out how to do it for quarterly and weekly Min/Max values.

Please post a link to the forum where you got help (see the rules on cross-posting). You can post the link with a few spaces in it.

No further help until this has been added.

Thank You for posting the link to the tutorial. The formula used there and given in post #5, uses the MIN and MAX functions which can be replaced by AGGREGATE as bebo explained.
I have modified bebo's formulas so that they will do what I believe that you want:
For Year: =$B4=AGGREGATE(15,6,$B$4:$B$50/(YEAR($A$4:$A$50)=YEAR($A4)),1)
For Quarter: =$B4=AGGREGATE(15,6,$B$4:$B$50/(YEAR($A$4:$A$50)=YEAR($A4))/($D$4:$D$50=$D4),1)
Note that the formula for quarter references a helper column (D) which is populated using: =ROUNDUP(MONTH(A4)/3,0)
For Month: =$B4=AGGREGATE(15,6,$B$4:$B$50/(YEAR($A$4:$A$50)=YEAR($A4))/(MONTH($A$4:$A$50)=MONTH($A4)),1)
For Week: =$B4=AGGREGATE(15,6,$B$4:$B$50/(YEAR($A$4:$A$50)=YEAR($A4))/($C$4:$C$50=$C4),1)
Note that the formula for week references a helper column (C) which is populated using: =WEEKNUM(A4)
Let us know if you have any questions.

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.