Calendar Date Conditional Formatting Counts

[AlexNorman100]

Hello,

I have been managing contracts and their end dates, I have done this by conditional formatting. For example, if a contract is due to end within 6 months it will highlight red, if due to end within 12 months amber and within 24 months green.

I will be continuously updating this sheet with new contracts and would love to be able to automatically count the number of contracts that are highlighted red (due within 6 months), amber (12 months) and 24 (months) so I can quickly summarize that there are x amount of contracts due to end within 6 months. I know how to use the count functions but coupling this with the conditional formatting has thrown me.

Any help would be greatly appreciated,
Alex

[Bglamb]

You could just add extra column that outputs the word 'red' 'amber' or 'green' based on the same calculation your conditional formatting uses, and then use a formula to count the number of occurrences of each word.

Let me know if you want to implement it this way and need help with the formulas.

[AlexNorman100]

That would work - please can you help with the set up of this output and formulas of this? Thanks!

[Bglamb]

Sure, are you ok to provide a sample of your data so I can make sure it's suitable?

[AlexNorman100]

Please find attached a very basic example.

The conditional formatting formula I used is:
For 6 month period.. =AND($D2-TODAY()>=0,$D2-TODAY()<=182.5)
For 12 month period.. =AND($D2-TODAY()>=182.5,$D2-TODAY()<=365)
etc.. etc..

[Bglamb]

Attached.

I've broken it out into columns so you can see the working, though you could consolidate that formula into a single cell if needed.

[AlexNorman100]

That's great Bglamb, many thanks for your help!!

[AlexNorman100]

Hi,

I went to input this formula in today but I am using lots of colors and sub-colors. How do I input into the formula Red, Accent 2, Lighter 40%?

Thanks!!

[Bglamb]

Hi Alex,

I used two formulas, both of which you can tweak quite easily.

The first one is just several nested IF statements:

Please Login or Register  to view this content.

You can change these numbers/words to change what word you would like to appear at which threshold. Reading from left to right, it reads "If D2 is less than 183, output "RED", else..." and then I've nested the next IF statement. You can add more layers or edit the existing ones by copying the existing format.

eg:

Please Login or Register  to view this content.

The same with the second formula, though on that one you will simply need to change the string that is being searched for from eg "RED" to whatever the new string is, eg: "DARKRED"