SOLVED

Conditional Formatting between certain dates

Occasional Contributor

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.

Example:

January 4th - 4201

February 10th - 4201

I want it to highlight that because its WITHIN 6 months of each other.

Column information.

Date Column is C

Number Columns are F - O

10 Replies

Re: Conditional Formatting between certain dates

Assuming that your data begin on row 1:

Select F2:Kn where n is the last used row.

F2 should be the active cell within the selection.

Create a conditional formatting rule of the formula type with the following formula:

=SUMPRODUCT((\$F\$1:\$K1=F2)*(\$C\$1:\$C1>=EDATE(\$C2,-6)))

Re: Conditional Formatting between certain dates

@Hans Vogelaar That didnt work, They didnt highlight if they were within 6 months.

best response confirmed by NConde (Occasional Contributor)
Solution

Re: Conditional Formatting between certain dates

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.

Re: Conditional Formatting between certain dates

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.

Re: Conditional Formatting between certain dates

Are the dates sorted differently in the 3rd section?

Re: Conditional Formatting between certain dates

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

Re: Conditional Formatting between certain dates

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

Re: Conditional Formatting between certain dates

@Hans Vogelaar

Here is a sample. I appreciate your assistance

Re: Conditional Formatting between certain dates

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.

Re: Conditional Formatting between certain dates

Thank you! this is now working! I appreciate all of your assistance!