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

halesowenmum

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

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0
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.
 
Upvote 0
Can you provide some data showing the dates and how you want them to look?
 
Upvote 0
@ 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!!!!
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top