Hello,
I'm new to the forums and a bit of a novice. Just a background of my situation, at my place of work we have a group of specialized agents who needs to have staggered breaks and lunches and everyday we have to submit all of our break schedules and a couple of people are tasked to figure out the best schedule with as little overlap as possible. It's a time consuming task and I'm hoping we can stream line it.
I have attached a sample file.
now assuming that each break is 15mins and lunches are 30, I am trying to create a spreadsheet where one can enter the start of each agent's breaks and it would show/highlight any conflict or overlaps across all schedules.
End times for the breaks i was able to use =IF(B2="","",B2+TIME(0,15,0)). the "blank" is just there for those who don't stay so late.
with what limited knowledge i have and after hours of reading through forums I was able to learn to use =SUMPRODUCT((B2<$H$2:$H$11)*(H2>$B$2:$B$11))>1 assuming the H column is where the end times for break 1.
All i've been successful with is comparing overlaps for Break 1, and have failed to extend it to include the range for the Lunches much less all break segments for the whole day.
I've also learned how to use conditional formatting to put filler colors on overlapping times (which i think is pretty neat) but again that's only for 1 column.
I was hoping someone could educate me or point me in the right direction.