Forum Discussion

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

NConde

Copper Contributor

Apr 17, 2021

HansVogelaar That didnt work, They didnt highlight if they were within 6 months.

HansVogelaar

MVP

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.

NConde
Copper Contributor
Apr 25, 2021
The formula is on my spreadsheet in 3 different sections, its working perfectly for 2 of the 3 locations, The 3rd location on the tracker the formula is almost upside down, Instead of highlighting the 2nd date being within 6 months, its highlighting the first date.
- HansVogelaar
  MVP
  Apr 25, 2021
  NConde
  
  Are the dates sorted differently in the 3rd section?
  - 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)))