Forum Discussion

NConde's avatar
NConde
Copper Contributor
Apr 17, 2021
Solved

Conditional Formatting between certain dates

I have a date collumn and a number that was completed on that date.  I want a forumla that will alert me if i try and put the same number on another row WITHIN 6 months of that first date.   Exa...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Apr 17, 2021

    NConde 

    This is what I get. I drew lines for the first occurrences.

    Perhaps I misinterpreted your question. If so, please explain in more detail what you want.

     

     

HansVogelaar's avatar
HansVogelaar
MVP
Apr 25, 2021

NConde 

Are the dates sorted differently in the 3rd section?

NConde's avatar
NConde
Copper Contributor
Apr 25, 2021

Nope exactly the same, infact it uses the same date column as the other ones

First section
=SUMPRODUCT(($F$9:$O9=F10)*($C$9:$C9>=EDATE($C10,-5)))
Second section
=SUMPRODUCT(($Q$9:$V9=Q10)*($C$9:$C9>=EDATE($C10,-5)))
third section - section thats not working properly
=SUMPRODUCT(($X$9:$AA9=X10)*($C$9:$C9>=EDATE($C10,-5)))

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Apr 25, 2021

    NConde 

    Could you attach a small sample workbook that demonstrates the problem, but without sensitive data?

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Apr 25, 2021

        NConde 

        Thanks!

        The difference is the range you've applied the rule to.

        As for the columns to the left, the middle rule should start in row 10, i.e. it should apply to $X$10:$AA$268. Unfortunately, when you edit the Applies to box, the formula will change, so you'll have to make sure that it is

        =SUMPRODUCT(($X$9:$AA9=X10)*($C$9:$C9>=EDATE($C10,-5)))

        after the edit.

        See the attached version.