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]
 

shknbk2

Board Regular
Joined
Mar 5, 2016
Messages
235
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
235
Can you provide some data showing the dates and how you want them to look?
 

Tetra201

MrExcel MVP
Joined
Oct 14, 2016
Messages
3,471
@ 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!!!!
 

Forum statistics

Threads
1,081,989
Messages
5,362,581
Members
400,683
Latest member
LogChief

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top