Louis, about that supervisor shift overlapping
PostPosted:Sat Nov 08, 2008 5:25 pm
I overheard Shellie talking about that overlapping shift spreadsheet thing you were doing. When I heard you were doing it with true/false statements every 30 minutes, I was thinking that there's got to be a more mathematical way.
Well, I was bored waiting at the haircutting place, so I figured it out. Okay, given any two shifts, the amount of overlap is going to be:
(Earlier end time) minus (Later start time)
So, if you have a shift like these:
=IF(B1 < B2, B1, B2) - IF(A1 > A2, A1, A2)
In the case of a "tie", 1 or 2 will be the same, so it doesn't matter. The only other thing to consider is if the overlap doesn't exist at all. If that's the case, it will be negative. So, there's a MAX function in Excel. Make it either the formula or zero, whichever is higher.
As far as individual days, I'm not sure, but ultimately, you'll be adding all of those together. You will also need to account for 3rd shifts, but if the dates are complete times and dates, Excel will fix part of the problem for you.
Well, I was bored waiting at the haircutting place, so I figured it out. Okay, given any two shifts, the amount of overlap is going to be:
(Earlier end time) minus (Later start time)
So, if you have a shift like these:
Code: Select all
The overlap visually is obvious. Mathematically, it's always going to be the first ending time minus the last starting time. In Excel, where A/B is the start/end times, and 1/2 is the shifts, this would be:|---------------------|
|---------------------|=IF(B1 < B2, B1, B2) - IF(A1 > A2, A1, A2)
In the case of a "tie", 1 or 2 will be the same, so it doesn't matter. The only other thing to consider is if the overlap doesn't exist at all. If that's the case, it will be negative. So, there's a MAX function in Excel. Make it either the formula or zero, whichever is higher.
As far as individual days, I'm not sure, but ultimately, you'll be adding all of those together. You will also need to account for 3rd shifts, but if the dates are complete times and dates, Excel will fix part of the problem for you.