Highlight all Mondays (or Tuesdays...) that start a week

[halesowenmum]

Hi.

I am using CF to highlight all Mondays in a week using this formula [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=WEEKDAY(P5)=2

When I populated the dates from left to right across my columns, I filled right with this formula: [FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=WORKDAY(S5,1,Holidays)

However, since I've got my Holidays range to ensure that bank holidays aren't included since they are non-working days, any month with a bank holiday Monday shows across that month as having 6 or 7 weeks in it as it's not picking up that in these cases, there isn't a working Monday to highlight.

How can I change one or both of these formulae so that if, due to a bank holiday on the Monday of the week means that the first working day is a Tuesday, it will highlight the Tuesday instead of the Monday??

Thank you.
[/FONT][/FONT]

 

[shknbk2]

I came up with this for the CF formula for P5.
=AND(WEEKDAY(P5,3)<2,WORKDAY(P5-WEEKDAY(P5,2),1,Holidays)=P5)

If the weekday is a Monday or a Tuesday, and if the workday of the Sunday prior matches the weekday, then TRUE.

 

[halesowenmum]

Hi and thanks so much for this.

The formula in P5 is simply "=B3" ie it is formatted to Date, Custom and simply references a cell which has a date in it somewhere else, which is always a Monday btw, that the user has entered - so P5 is simply referencing that date and performs no calculations. The subsequent cells on Row 5 going along to the right are populated with the second formula to show the dates minus weekends, and bank holidays, which is what the user needs to see - the dates.

IF I fill your formula suggestion right across the columns from Q5 onwards, I get the word "TRUE" displayed - but unfortunately that's not what I need. I need to see the date displayed but it not be a weekend or from my Holidays list of dates. Then the CF can highlight the first working day of each week which will 99% of the time be a Monday or a Tuesday. ​

I'm not sure this is right or I'm not applying it correctly but I think it could probably be tweaked to work in the way needed?
​

If I paste this formula into the CF formula field then the highlighting of the Mondays that I know are Mondays, disappear.

 

[shknbk2]

Can you provide some data showing the dates and how you want them to look?

 

[Tetra201]

@ halesowenmum:

See if the following CF rule works for you:

=P5=WORKDAY.INTL(P5-5,1,"0011111",Holidays)

 

[halesowenmum]

@ halesowenmum:

See if the following CF rule works for you:

=P5=WORKDAY.INTL(P5-5,1,"0011111",Holidays)

THAT has cracked it - beautifully highlighted Tuesdays where there's a preceding Bank Holiday Monday - woohoo!!

Thank you!!!!

 

[Tetra201]

Glad to have helped.