Dates and conditional formatting

Closed
Report
-
Posts
47366
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
-
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]


1 reply

Posts
47366
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
11,364
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/contents/948-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
0