Sep 05 2020 12:17 AM
Sep 05 2020 12:17 AM
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.
Sep 05 2020 03:34 AM
@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.
Sep 05 2020 08:49 AM
Here is Power Query solution. Result is
- 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
Sep 05 2020 09:09 AM
Sep 05 2020 09:10 AM
Sep 05 2020 09:13 AM
Sep 05 2020 09:21 AM - edited Sep 05 2020 09:22 AM
@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.
Sep 05 2020 09:25 AM
Sep 05 2020 09:39 AM
@ikawaljeetsingh So, in your example for the Monday, there should be nothing in the early morning time slots as the first shift on Monday starts 6:00 and there is no information on the Sunday before. But again, perhaps I misinterpret your example.
Sep 05 2020 09:53 AM
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
Nov 11 2020 01:21 AM
@Sergei Baklan - 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?
Nov 11 2020 01:00 PM
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.