Forum Discussion
Formatting cells based on certain date function.
It depends on how your data is structured and what do you consider as "this" week, what is 5th week (from month start or every 5th after long days, off), etc. Question is bit abstract to give concrete answer, if only with some guesses.
What I want to do a create a calender with dates on the first row followed by each rows for the day's activity or work. There will be different rows for different individual and each of them will be off on different days of week.
I wish to automatically mark the rolling off days with certain colour (say black) so I don't have to check calendar and mark for each of the colleagues, provided I can use some sort of formatting to achieve that for each month.
I hope this makes it bit clearer.
Many thanks
Bebek
You may apply conditional formatting rule with formula
=MOD(WEEKDAY(B3,2)+MOD(B3,7)+MOD(B3,35),10)=5In attached file it is applied to this range
SergeiBaklanThank you for your swift response and the excel sheet with example. It is exactly what I wanted to do with my rota as well, but when I tried it on a new sheet I could not replicate the same using the formula you provided {=MOD(WEEKDAY(B3,2)+MOD(B3,7)+MOD(B3,35),10)=5}. I must have made some mistake, kindly check the attached sheet and guide me through.
If possible can you explain how I can change the formula to accommodate different day off, for example Mr A might be off this week on Monday whereas Ms B might be off on Thursday same week.
Many thanks
Bebek
In formula you shall use first cell of the range for the reference
If the range starts from first row, use B1. If from third row like C3:C1000, use B3, etc. And I'd fix the column, i.e. $B1, not B1 (and NOT $B$1)