Forum Discussion
Shift distribution into excel to review overall Coverage
ikawaljeetsingh Don't have a ready solution for your specific example, since calculating with start and end times ("hh:mm") contained in text strings becomes very difficult, especially when shift end time goes beyond midnight. Though, I'm offering an example that may inspire you to choose a different approach by using numbers, rather than time values. Since your shifts always seem to last 11 hours, it becomes much easier.
The one sheet in this workbook does the calculation of the number of employees per time slot per day. It does NOT create the a fancy summary per week, but all the elements are there.
- ikawaljeetsinghSep 05, 2020Copper ContributorThank You Riny, I'm going to think and apply your suggested logic sometime over the weekend. However, I'll continue to look for other solution that others can provide/suggest. Although, I'm going to try the solution provided by Sergei (below).
- Riny_van_EekelenSep 05, 2020Platinum Contributor
ikawaljeetsingh Just wondering if your example really reflects what you want/need. Now you count the time slots that are carried over from Monday to Tuesday as time slots on the Monday. In my view, when a shift starts on Monday 17:00 and ends 04:00 the next morning it should reflect 4 hours on Tuesday from 00:00 till 04:00. In other words, the early morning slots on a Monday come from shifts that started on the Sunday before and that ran over midnight. But perhaps I misunderstood your requirements.
- ikawaljeetsinghSep 05, 2020Copper ContributorYes Riny, you got it right. When a Monday's 17:00 shift ends at 04:00, it should reflect the 4 hours on Tuesday 00:00 till 04:00.