• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Working Hours

John Jairo V

John Jairo V

Well-Known Member
Hi to all!

This time I need a formula (or VBA solution, but I prefer formula solution) that must work with amount of hours worked with only one worker, two workers and three workers. Please see the attach file, check the attachment for clarity. Blessings!
 

Attachments

  • WorkingHours.xlsx
    15.6 KB · Views: 21
Wow, what is becoming of this worldo_O ? You are normally one of the guys one needs to turn to for formula wonders, dear @John Jairo V.
Care to explain how you arrive at your manual results? I honestly don't see/understand the logic. Nor do I pretend to be able to help you, but I do believe some extra information is required for others, like the Bosco's, the XOR's, the Lori's... of our time, to solve your query.
 
I'm guessing that @John Jairo V is looking to get available worker # per each hour window (broken up by percentage of time with # of workers 1 to 3).

1T to 3T representing # of workers.

So, for 8:00 to 9:00, 25% of time there was only 1 worker, 25% with 2 worker and 50% with 3 worker.

Each row must equal 1. I suppose.

And you have error on Row 26. Should be 1 for 2T.

However, I'm not sure I get working hours calculation at the bottom.

5.75 hours with 1 worker, 5 hours with 2 worker, 3.25 hours with 3 worker...

Should total not equal...
5.75 + 5 * 2 + 3.25 * 3 = 25.5 hours?

Personally, I'd start with different layout for initial data.
 
Still wanted to say "Sorry, I get into "Computer says no" mode with this. And by computer I mean my brain."
But your extra information has clarified Johns' query.
So for line 21 (time frame 12 - 13) there were 3 workers available for 100% and thus you have 1 in T3 only.

Hmm, "Computer says no", I'm afraid ;-).
 
Personally, I'd record in flat table, each person's shift/workhour, ie. Start, End, Name. Plus any other attributes.

Then from there, I'd use time interval table (24 hours in 15 min increment). Calculate overlap and # of workers per 15 min interval.

Then produce result as final out put.
 
Hi, to both!

GraH - Guido said:
Wow, what is becoming of this world
Click to expand...

jajajaja... (laughing in spanish, my native language). Sometimes I didn't get formulas... this time was one of them.

Sometimes I don't get some things ... this is one of them.

I proceed to explain. The idea is to calculate the overlap of workers by schedule. That is, I want to know how many hours (no percentage as Chihiro said) a worker works alone, how many hours two workers work and how many hours 3 workers work.

Since the intervals are 1 hour, then the sum of the rows (1T, 2T and 3T) must add 1. And the total sum must match the schedule worked by all the workers (in the example I raised, the total sum must give 14 hours (because work began at 7:00 -> P2 and finished work at 9:00 pm -> P4. Then: 9:00 pm - 2:00 pm = 7:00

Indeed, there was an error with row 26, since there are 2 workers working, then one hour should go in column 2T in that row.

With Chihiro's idea of reducing the intervals every 15 minutes, I could use the example ... although I must reduce it every minute, because the schedule can be given at 8:04 for example (entrance) and leave at 18: 57 (departure time). I wanted for the example to place multiples of 15 so that it was clearly verifiable.

Now: The comments that are in row 17, 20 and 25 correspond to the times in which the overlap is made (seen manually, of course), explaining why it gives the sum.

I upload the file with auxiliary columns and resolved, taking into account intervals per minute. I will look, as it is resolved, to do it without the auxiliary columns. Chihiro, thank you very much for your suggestion, which was key to obtaining the result. Blessings!
 

Attachments

  • OverlapHours.xlsx
    117.6 KB · Views: 12
I might have misunderstood the problem but what I have attempted is to place the worker start/end times in event order which allowed me to accumulate the worker count as well as calculate the interval between events.
 

Attachments

  • WorkingHoursOverlap.xlsx
    20 KB · Views: 10
John Jairo V said:
Hi, to both!

jajajaja... (laughing in spanish, my native language). Sometimes I didn't get formulas... this time was one of them.

Sometimes I don't get some things ... this is one of them.
Click to expand...
:-D, More then sometimes, I don't dig stuff myself. Like totally being in the dark this time. Seems you are a "normal" dude after all then ;-)
Cool you could adapt to Chihiro's idea. Got to study it as well as that thing Peter's brain produced. At my own risk I might add...
 
@GraH - Guido
I now think I have at least solved the correct problem unless the hourly report is a key part of the output!

I unpivoted the source data and sorted it by hand because fancy array methods were going to get me nowhere if I was solving the wrong problem and they are not so great for communicating ideas as yet. The mindset is to address the problem from the viewpoint of factory security. With each clock-in the headcount is increased by one, clocking-out decreases it. Each event generates an output from the interval timer. All that remains is to tot up the results.

At the moment accumulations (such as the headcount) do not work as an array formula because they are treated as circular references. Ways round include allowing circular references, using matrix multiplication or writing a UDF but, for the user, such solutions lack appeal (just realised that if MMULT works, it is likely that I can get SUMIFS to work!).
 
Hi to all!

@Peter Bartholomew
What you propose is interesting, although it is not what I needed to do (the table on the left fulfills its mission, visually showing the overlaps).
I have certain problems expressing ideas in English, since it is not my native language (I am Colombian, and I speak Spanish). Maybe that was why my idea was not initially understood. What I needed to solve was the right part (where it says expected result), which initially resisted me, but Chihiro gave me a working idea with smaller intervals - (I took it every minute for the potential entry and exit times per minute). Your solutions are very interesting (you see things from a not very common approach for the average Excel user).

@vletm
Clearly VBA is a good outlet for some users. As you have been able to realize, I am a lover of formulas and I force myself to carry out problems like this in order to learn more and more. VBA for me is a final option because I don't like the issue of undoing when it runs. It is the same formula copied 24 * 6 = 144 times, simply changing the reference from where it is seen, but in essence it is the same formula - and if we talk about length, shorter than the code you propose :)

@GraH - Guido
I have always been a "normal" person, passionate about solving everything with formulas. When something is not given to me, or I get stuck because of something, I have no problem asking, because sometimes the solution is in front of our eyes, and because we are digressing in several things we do not see it. As you can see ... a small idea opened the way to finding the solution.

Thx again to all for participating. And some other ideas could be welcome. Blessings!
 
John Jairo V said:
... I am a lover of formulas and I force myself to carry out problems like this in order to learn more and more.
I have always been a "normal" person, passionate about solving everything with formulas. ...
Click to expand...
I have that deficiency when it comes down to using Power Query :)
For the fact you share your wit, we can only be humble and grateful. So a sincere thank you.

... Now I need to take on the challenge and solve this with PQ, I guess. (Tried and failed earlier... Thus learning, yes!).
 
@John Jairo V
I am aware that my solutions may appear frankly weird to others. Just to demonstrate how 'sad' I can be, I spend part of today playing with your problem setup to see whether I could come up with a different strategy. There are no FILTERs or SORTs this time, just SUMIFS, MINIFS etc. I have attached a CSE version of the workbook but my main interest was to get the dynamic array version set up in a way that maximised the flexibility of the solution beyond anything that could be achieved with traditional single-cell formulas, never mind the heavily-constraining CSE approach.

What I did to test the workbook was to transpose the initial data and reapply the names to the new setup. I then inserted a clock-off / clock-on pair of columns to represent lunch breaks. The result at the bottom of the sheet continued working correctly and, turning to the 'hidden' calculation sheet showed that the intermediate spilt arrays had all changed shape but, other than a bit of tidying up as far as layout was concerned, no editing was needed.

Thanks for the challenge :) even if my results were not what you were looking for.
 

Attachments

  • WorkingHoursOverlapCSE.xlsx
    23.9 KB · Views: 4
Chihiro said:
Best approach here is to use 2 table as starting point.
1. Just the header portion (Start, End, P1~6)
2. Then use List.Times function to generate 2nd table.
Click to expand...
Step 1 I did exactly like that. Then I started wondering off... I hardly ever need to calculate with time and so my tactics are off as well. Will investigate List.Times functions. Thanks for the clue, @Chihiro. Yet I'll try to make a 100% UI solution (for fun).

Peter Bartholomew said:
@GraH - Guido
I have made some progress with Power Query but the overwhelming feeling is one of groping around in the dark!
Click to expand...
Progress = great! Don't you have light switches in the UK? ;-p
Using indexes and list functions, nice...
 
65057
This scatter chart was drawn from the table output by PQ. The data series is headcount versus clock-in/out time and the bars are error bars whose length is set equal to the interval between events. All pretty standard stuff I know, but it is rare for me to have the opportunity to try such things.
 
@John Jairo V , you have in your formula (attachment msg#11) a couple of instances of:
ROW($A$1:INDEX($A:$A,60))
with a hardcoded 60, so why not:
ROW($A$1:$A$60)
?
 
Hi, p45cal!

I like ROW($A$1:INDEX($A:$A,60)) , for avoiding changes when insert/delete rows. Sometimes I use ROW(INDEX($A:$A,1):INDEX($A:$A,60)), rigorously.

For me is better than ROW(INDIRECT("1:60")) <-- Volatile Function.

Blessings!
 
I had bit of time. So did a 1 table solution in PQ.

1. Load P1~6, Start & Time as flat table.
65114

2. Add custom column
Code: 
=List.Times([Start],Duration.TotalMinutes([End]-[Start]),#duration(0,0,1,0))

3. Expand [Custom] and group by [Custom] Count Rows.

4. Add another custom column [Bucket]
Code: 
=#time(Time.Hour([Custom]),0,0)

5. Group by on both [Bucket] and [Count]. Count Rows.

6. Pivot based on Count, and choose Count.1 as values column.

7. Reorder columns as need. I didn't do it, but you can convert values in each column to hours, if you want.
65115
 

Attachments

  • WorkingHours_PQ_Sample.xlsx
    27.8 KB · Views: 13
You must log in or register to reply here.
Back
Top