Shift distribution into excel to review overall Coverage

%3CLINGO-SUB%20id%3D%22lingo-sub-1637862%22%20slang%3D%22en-US%22%3EShift%20distribution%20into%20excel%20to%20review%20overall%20Coverage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637862%22%20slang%3D%22en-US%22%3E%3CP%3EI%20was%20recently%20working%20on%20a%20spreadsheet%20to%20review%20the%20overall%20coverage%20where%20I%20entered%20the%20numbers%20manually%20by%20looking%20at%20shifts%20for%20each%20employee%20for%20the%20entire%20week.%20I%20was%20thinking%20of%20applying%20a%20function%20which%20can%20distribute%20the%20hours%20post%2000%3A00%20hours%20into%20the%20next%20day%20similarly%2C%20Sunday%20hours%20into%20Monday.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%2C%20attached%20spreadsheet%2Fimage%20will%20help%20you%20answer%20my%20question%20to%20be%20able%20to%20assist.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20applying%20COUNT%20IF%2C%20however%2C%20i%20couldn't%20figure%20out%20distributing%20it%20to%20the%20next%20day.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1637862%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637963%22%20slang%3D%22en-US%22%3ERe%3A%20Shift%20distribution%20into%20excel%20to%20review%20overall%20Coverage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637963%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F782959%22%20target%3D%22_blank%22%3E%40ikawaljeetsingh%3C%2FA%3E%26nbsp%3BDon't%20have%20a%20ready%20solution%20for%20your%20specific%20example%2C%20since%20calculating%20with%20start%20and%20end%20times%20(%22hh%3Amm%22)%20contained%20in%20text%20strings%20becomes%20very%20difficult%2C%20especially%20when%20shift%20end%20time%20goes%20beyond%20midnight.%20Though%2C%20I'm%20offering%20an%20example%20that%20may%20inspire%20you%20to%20choose%20a%20different%20approach%20by%20using%20numbers%2C%20rather%20than%20time%20values.%20Since%20your%20shifts%20always%20seem%20to%20last%2011%20hours%2C%20it%20becomes%20much%20easier.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20one%20sheet%20in%20this%20workbook%20does%20the%20calculation%20of%20the%20number%20of%20employees%20per%20time%20slot%20per%20day.%20It%20does%20NOT%20create%20the%20a%20fancy%20summary%20per%20week%2C%20but%20all%20the%20elements%20are%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1637964%22%20slang%3D%22en-US%22%3ERe%3A%20Shift%20distribution%20into%20excel%20to%20review%20overall%20Coverage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1637964%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F782959%22%20target%3D%22_blank%22%3E%40ikawaljeetsingh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20it%20attached%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1638303%22%20slang%3D%22en-US%22%3ERe%3A%20Shift%20distribution%20into%20excel%20to%20review%20overall%20Coverage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1638303%22%20slang%3D%22en-US%22%3EThank%20You%20very%20Much%20%40SergeiBaklan.%20Seems%2C%20your%20resolution%20has%20done%20the%20job.%20I%20am%20going%20to%20learn%20PQ%2C%20test%20and%20apply%20it%20on%20actual%20sheet%20so%20I%20can%20understand%20the%20solution.%20By%20the%20looks%2C%20it%20seems%20easy%20(with%20logics).%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1638306%22%20slang%3D%22en-US%22%3ERe%3A%20Shift%20distribution%20into%20excel%20to%20review%20overall%20Coverage%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1638306%22%20slang%3D%22en-US%22%3EThank%20You%20%40Ramiz%2C%20I%20tested%20the%20solution%20provided%20by%20you.%20It%20seems%20to%20be%20distributing%20the%20hours%20after%2000%3A00%20into%20the%20same%20day%20than%20the%20next%20day.%3C%2FLINGO-BODY%3E
Highlighted
Occasional 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.

10 Replies
Highlighted

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

 

Highlighted

@ikawaljeetsingh 

 

Please find it attached

Highlighted

@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

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

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

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

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

Highlighted

@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