Forum Discussion

bbkbhattarai's avatar
bbkbhattarai
Copper Contributor
Nov 13, 2020

Formatting cells based on certain date function.

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 tryi...
excel
Formulas and Functions
bbkbhattarai's avatar
bbkbhattarai
Copper Contributor
Nov 13, 2020
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
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Nov 13, 2020

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

  • bbkbhattarai's avatar
    bbkbhattarai
    Copper Contributor
    Nov 13, 2020

    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

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Nov 13, 2020

      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)

      • bbkbhattarai's avatar
        bbkbhattarai
        Copper Contributor
        Jan 03, 2021

        SergeiBaklan 

         

        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

Resources