Formula

[A2Z CF]

Greetings everyone,

I’m trying to put a formula in Conditional Formatting to highlight when it’s the last Monday of the month or next working day if its holiday. I have a list of holidays in tab sheet "Batch" D78 to D87. To do that, I try to tweak my existing formulas but it doesn’t work. One of the example formula is below which is resident in Conditional Formatting to highlight the 2nd and 4th Thursday of the month when the condition is true. Any help to achieve my above goal will be greatly appreciated and thank you in advance.

=AND(WEEKDAY(TODAY())=5, OR(AND(DAY(TODAY())>7, DAY(TODAY())<15), AND(DAY(TODAY())>21, DAY(TODAY())<29)))

[#2]

@A2Z CF 

Use

=TODAY()=WORKDAY(EOMONTH(TODAY(),0)-WEEKDAY(EOMONTH(TODAY(),0),3)-1,1,Batch!$D$78:$D$87)

[#3]

That’s it! Thank you so much for your time and the great solution! This formula works so great now even after holiday, it does not fail to serve its purpose. Awesome! I wish if my following two formulas could have the same holiday criteria in them as well. I appreciate it very much, May the power be with you!
For every Friday:
=AND(WEEKDAY(TODAY())=6)

For every 2nd & 4th Thursday of the month
=AND(WEEKDAY(TODAY())=5, OR(AND(DAY(TODAY())>7, DAY(TODAY())<15), AND(DAY(TODAY())>21, DAY(TODAY())<29)))

[#4]

@A2Z CF 

The first one: do you want to highlight if it's Friday except if it's a holiday?

If so:

=AND(WEEKDAY(TODAY())=6, ISERROR(MATCH(TODAY(), Batch!$D$78:$D$87, 0)))

If you want to highlight the next working day instead, it's more complicated. I'll look at it later, no time now.

[#5 · Accepted solution]

@A2Z CF 

If you want to highlight the next workday after Friday if Friday is a holiday:

=TODAY()=WORKDAY(TODAY()+1-WEEKDAY(TODAY(),15)-1,1,Batch!$D$78:$D$87)

For the 2nd or 4th Thursday of the month:

=AND(TODAY()=WORKDAY(TODAY()+1-WEEKDAY(TODAY(), 14)-1, 1, Batch!$D$78:$D$87), OR(AND(DAY(TODAY()+1-WEEKDAY(TODAY(), 14))>7, DAY(TODAY()+1-WEEKDAY(TODAY(), 14))<15), AND(DAY(TODAY()+1-WEEKDAY(TODAY(), 14))>21, DAY(TODAY()+1-WEEKDAY(TODAY(), 14))<29)))

[#6]

Okay, thank you so much Hans. Whenever you get chance, I'll appreciate your help a lot. By the way, that formula works great except highlighting next working day if there is a holiday. I thank you so much and appreciate your precious time.

[#7]

I just saw this message and I'll let you know once I put it in the sheet. I thank you so much for your time and effort.
Thank you

[#8]

Thank you so much Hans. These are very great and helpful formulas specially when the holiday is just around the corner. This solved my long issue of missing assignments on different days and the holidays were making worst, but now not anymore! You have a great weekend and stay safe. Once again thank you so much for your precious time and effort in this regard, I appreciated very much. Thank you