Weekdays - Conditional Formatting

[Kinna]

Hello,

I am using the following formula on a conditional formatting to color cell A1 based on cell N5 date =$N5=TODAY()-2

The problem is that I need this formula to consider only weekdays, how can apply formula weekdays to my formula?

Tks!

[NBVC]

=$N5=TODAY()-SMALL(IF((WEEKDAY(TODAY()-(ROW(INDIRECT("1:"&2*10))),2)<6),ROW(INDIRECT("1:"&2*10))),2)

replace the 2's with other number if you want to subtract other than 2 days...

[Kinna]

Hi!

I've tried your suggestion, but it ends up subtracting 4 days instead of 2. I have tried a few things but wasn't able to make it work properly.

[NBVC]

With that formula I get a result of May 15th which is 2 working days prior to today. Is that wrong?

[Kinna]

Lol, I'm sorry my bad! Your formula is more than perfect! Thank you very much!!!

Just one more quick question, I am trying to do a scale on my consditional formatting like this:
-2 weekdays from today = yellow
-3 weekdays from today = orange
-4 weekdays from today = red

So, how am I supposed to make the last two formulas subtract 3 and 4 days?
I have tried the below just like your hint in the first post but it did not work...

ORANGE
=$N5=TODAY()-SMALL(IF((WEEKDAY(TODAY()-(ROW(INDIRECT("1:"&3*10))),3)<6),ROW(INDIRECT("1:"&3*10))),3)

RED
=$N5=TODAY()-SMALL(IF((WEEKDAY(TODAY()-(ROW(INDIRECT("1:"&4*10))),4)<6),ROW(INDIRECT("1:"&4*10))),4)

[NBVC]

Sorry... it's not all the 2's you change... there is one that remains for the Weekday() function...

so it would be:

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

[Kinna]

Awesome! Thank you very much!!!

I hope you have a wonderful week!