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 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

excel
Formulas and Functions

8 Replies

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

    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.

    • 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
  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Nov 13, 2020

    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)

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

      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.

Resources