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 she...
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)))
- 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
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.
- 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.
