• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Help with Conditional Formatting

Vams

New Member
I am trying to build a daily work schedule for my team where I have the agents name, their scheduled activities and their start and end times (Coloum A to D).
I need help in conditional formatting to

1) Highlight the Shift time for all the agents with one color as seen for Tom and Gary
2) Every agent will have some additional activities like Meal, Training,Coaching etc.. with their start and end times. I want those hours against those agents to be highlighted with a different color

Example:
  1. Tom works Shift 10am to 5 pm . So cell H3:O3 is highlighted in yellow, Similarly for agent Gary E4: N4
  2. Now Tom also has another activity "Meal" between 12pm and 1 pm , so I want J3:K3 to be Green. He has "Training" from 11am to 12 pm,so I want I3:J3 to be blue.
  3. Similarly if you see for Gary his regular shift is from 7 am to 4pm which is highlighted in Yellow , but he as activities like "Coaching", "Meal","Break" etc at different times during his shifts. I want those times to be highlighted with a different color
  4. Note I have 200 individual agents and 12 different kind of activities . So in Coloum A - Tom might be repeated a couple of times with his activities in coloum B ( See A3 - for his regular shift and A8&A9 for his other activities) . Similarly for Gary
  5. 59882
 

Attachments

  • 1557074490295.png
    1557074490295.png
    20.6 KB · Views: 6
  • Agent Daily Schedule.xlsx
    10.5 KB · Views: 1
Last edited:
Try,

1] In E3, copied across and down :

=IF((E$2>=$C3)*(E$2<=$D3),E$2,"")

2] Select E3:Q13 >> Conditional Formatting >> New rule >> Use a formula >> Formula : =E3<>"" >> Format >> choose : Fill in Orange and Font in White color >> OK

Regards
Bosco
 

Attachments

  • Agent Daily Schedule(1).xlsx
    12.8 KB · Views: 4
Try,

1] In E3, copied across and down :

=IF((E$2>=$C3)*(E$2<=$D3),E$2,"")

2] Select E3:Q13 >> Conditional Formatting >> New rule >> Use a formula >> Formula : =E3<>"" >> Format >> choose : Fill in Orange and Font in White color >> OK

Regards
Bosco

Hi Basco,

Thanks for the quick response, I think i did not articulate the question properly

See Below,
  1. Tom works Shift 10am to 5 pm . So cell H3:O3 is highlighted in yellow, Similarly for agent Gary E4: N4
  2. Now Tom also has another activity "Meal" between 12pm and 1 pm , so I want J3:K3 to be Green. He has "Training" from 11am to 12 pm,so I want I3:J3 to be blue.
  3. Similarly if you see for Gary his regular shift is from 7 am to 4pm which is highlighted in Yellow , but he as activities like "Coaching", "Meal","Break" etc at different times during his shifts. I want those times to be highlighted with a different color
  4. Note I have 200 individual agents and 12 different kind of activities . So in Coloum A - Tom might be repeated a couple of times with his activities in coloum B ( See A3 - for his regular shift and A8&A9 for his other activities) . Similarly for Gary




59881
 
Vams
Have You read next?
 
Back
Top