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

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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]
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
240
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

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
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

Board Regular
Joined
Mar 5, 2016
Messages
240
Can you provide some data showing the dates and how you want them to look?
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,542
@ halesowenmum:

See if the following CF rule works for you:

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

halesowenmum

Active Member
Joined
Oct 20, 2010
Messages
380
Office Version
365
Platform
Windows
@ 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!!!!
 

Watch MrExcel Video

Forum statistics

Threads
1,099,328
Messages
5,467,963
Members
406,561
Latest member
Grappledog

This Week's Hot Topics

Top