Formatting cells based on certain date function.

[bbkbhattarai]

Hi all,

I am pretty new here and beginner in Excel too. I recently started using conditional formatting and am still learning it. I have a doubt and any help will be highly appreciated. I am trying to build a rota for the month, and wanted to build colour coded days to indicated which day who is doing what. Also wanted to include the days where one is off, since we use rolling day off per week for everyone, I wanted excel to use the weekdays function and mark rolling days off (eg: Monday this week to Tuesday next week as off---the trickiest art in this is the 5th week where one will be off on Friday as well as Monday). 

I will highly appreciate if anyone here can help me with this. Thanking you in advance.

Regards

Bebek

[#2]

@bbkbhattarai 

With your permission,

if I can recommend. It can help us all if you upload an Excel file (without sensitive data), no picture. Even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

You could get a precise solution much faster with a file (w/out sensitive data).

This would also be a blessing for all of us, as we can understand the problem much better, a win-win situation for everyone.

*Knowledge of Excel version and the operating system is a must have if you want to proposing a reasonable solution (Example: office version e.g. 2016 or 2019 or 365 web or 365 pro, etc) and your operating system (e.g. Win10 (2004), Win 10 (1903), Mac, etc.).

Thank you for your understanding and patience

Nikolino

I know I don't know anything (Socrates)

[#3]

@bbkbhattarai 

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.

[#4]

Thank you for your reply. By this week I was giving a reference to one individual who was on rolling off on Monday this week, so she/he will be off Tuesday next then Wednesday followed by Thursday. The week after she/he will be off for Friday and Monday too, this generally is the 5th week on the rolling day off system.

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

[#5]

@NikolinoDEPlease see the attached excel sheet. 

All cells with Black colour is day off and rest of the colour coded cells are for specific tasks. I wish to get the colour coding for the rolling off days, if possible. As I explained earlier the day off will keep changing from week to week. And on every 5th week day off will start on Friday for that week followed by Monday of the next week.

Will it be possible to get that sorted with the dates/days from the first two cells?

Thank you for your time and help.

[#6]

@bbkbhattarai 

You may apply conditional formatting rule with formula

=MOD(WEEKDAY(B3,2)+MOD(B3,7)+MOD(B3,35),10)=5

In attached file it is applied to this range

[#7]

@Sergei BaklanThank 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

[#8]

@bbkbhattarai 

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)

[#9]

@Sergei Baklan 

Apologies for the delay in reply. I was bit caught up with other work and festive season. 

Thank you for the formula, and it does work for one person and exactly as I wanted it to. Could I please request how I can change the formula so it starts with different days marked as off day. 

For example, in this formula the day off are on the first week is on Friday followed by next week on Monday. How can I change the formula, so the day off starts on Wednesday this week and Thursday the next and so on resulting in Friday & Monday off the the weeks after.

Also I was looking to apply the same conditional formatting to different rows (different clinicians with different off days within the week). Can I get this done?

Many thanks for your time and patience.

Kind regards

Bebek