Shift distribution into excel to review overall Coverage

Copper Contributor

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

@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.

 

@ikawaljeetsingh 

 

Please find it attached

@ikawaljeetsingh 

Here is Power Query solution. Result is

image.png

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

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.
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. 

Yes 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.

@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.

@ikawaljeetsingh 

 

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

 

@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?

 

Thanks

@ikawaljeetsingh 

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.