Forum Discussion
Solved
Formula
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)))
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)))
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)))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)))
