# Dates and conditional formatting

Closed
tennis55 - May 15, 2011 at 12:38 PM
Ambucias Posts 47358 Registration date Monday February 1, 2010 Status Moderator Last seen September 1, 2021 - May 15, 2011 at 04:20 PM
Hello,

I'm trying to create conditional formatting that will do the following:
A3= today's date
I10: The due date
Past due: Anything due before today's date= Red
Due within the next week: Anything due within 5 business days of A3= Yellow
Due in the future: Anything due beyond 5 business days of A3= Green
Everything must be in business days only, excluding holidays.

I've created the following conditional formatting rules:
=IF(\$A\$3>WORKDAY(I10,2,\$M\$5:\$M\$6),TRUE,FALSE) [will be red]
=IF(\$A\$3>=WORKDAY(I10,2,\$M\$5:\$M\$7),TRUE,FALSE) [will be yellow]
=IF(\$A\$3<=WORKDAY(I10,6,\$M\$5:\$M\$6),TRUE,FALSE) [will be green]

I also need a way to count the amount of reds, yellows, and greens, without using a macro and again using only workdays and non-holiday days. I have used this formula, but it's not working properly:
=IF(J10="","",IF(J10="Complete",4,IF(J10<\$A\$3,1,IF(J10=\$A\$3+(WORKDAY(I10,6,\$M\$5:\$M\$6)),3)))))
Red= 1
Yellow=2
Green= 3
"Complete"=4 [cell will say complete]

Ambucias Posts 47358 Registration date Monday February 1, 2010 Status Moderator Last seen September 1, 2021 11,242
May 15, 2011 at 04:20 PM
Bare in mind that behind every answer, to help given on the forum, there are human volunteers who give their time trying to solve users' problems.

Therefore, it is expressly requested from kioskea forums users to show their utmost respect. For this, the use of polite expressions is a minimum.
https://ccm.net/apps-sites/internet-archeology/ccm/10131-terms-of-use-for-ccm-respect-for-others/#politesse

* Please, re-write your message accordingly, respecting the politeness charter and repost your message.

Please, thank you, appreciate, greateful are words among those forms

We trust that you understand

Moderator