Forum Discussion
Shift distribution into excel to review overall Coverage
I was recently working on a spreadsheet to review the overall coverage where I entered the numbers manually by looking at shifts for each employee for the entire week. I was thinking of applying a function which can distribute the hours post 00:00 hours into the next day similarly, Sunday hours into Monday.
Hope, attached spreadsheet/image will help you answer my question to be able to assist.
I tried applying COUNT IF, however, i couldn't figure out distributing it to the next day.
12 Replies
Here is Power Query solution. Result is
In brief,
- source data is named as Range;
- query it, unpivot shifts, split on start and end
- filter on ThisDay and NextDay. If overnight shift, ThisDay ends at 24:00, NextDay starts at 00:00 and for it change Day name on next weekday
- generate 30-minutes lists for each Start and End, expand it and Pivot on weekday counting employees
SergeiBaklan - Hello, I had to perform this couple of times in the last few weeks but I don't think I could replicate with the steps you defined. What could be an alternate to run this through power query?
Thanks
In theory Power Query could be build in some different way, but that's no warranty you will reproduce it since it's not clear why this one didn't work in your case.
It doesn't work if you open the file above; or if you add another data to that file; or if you copy/paste quires into another file? And there are some errors appear or result is not as you expect or something else.
- Thank You very Much @SergeiBaklan. Seems, your resolution has done the job. I am going to learn PQ, test and apply it on actual sheet so I can understand the solution. By the looks, it seems easy (with logics).
- Thank You @Ramiz, I tested the solution provided by you. It seems to be distributing the hours after 00:00 into the same day than the next day.
Then it would make more sense to start your day from 00:00 and end it at 23:59
Let's say one worker had a shift from 22:00 to 04:00 then from 22:00-23:59 goes to this day and the remainder goes to the next day.
the formula would be much easier.
Best of luck
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.
- Thank 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).
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.
