Actual build time

[robbarba]

Hello Excel gurus I do not have a sample data set but here are the circumstances:

1. It take 90 minutes for each item to be built. (known)
2. I have 53 units to be built (variable)
3. I have only a 3 hour window 7a - 10a to build, some sites will all all day 7a - 5p (variable)
4. I have 2 builders available on start day, and 1 additional builder on day 9 and 2 additional builders on day 14 (variable)
2 builders on day 1 - 90 min each item to build - 180min off the 240min build window, continue to build until window closes
if the cycle is incomplete, deduct the completed units, and add another day to build cycle repeat until unit count is is zero

How do I formalize this so I can rerun this cycle build it will take to complete all 53 units? 95 units? 210 units? utilizing the variables as outlined

You guys have save bacon numerous times and I get those aha's and learning from this group!

Thank you all to take up this challenge.

 

[Dan_W]

Interesting. Where have you got to so far?
Also, I might just need to read it again properly, but I don't understand point 4 - it feels like it missing some contextual information. Namely, what are these days you're referencing? Is there a deadline or something? This really just looks like a maths problem. Invariably, you're going to need to work out the hypothetical amount of time it will take to build all of the whatevers and then measure it against each day unit, with its fluctuating amount of hours in the day, bur you've probably already done that part.

 

[robbarba]

thanks for trying to understand the need, let see if I can make point 4 clearer for you

on 6/1 (day 1) I have 53 units to build - each unit takes 90 min to build, I have 270 min (build window) available per day to do as many as many as we can, on day 1 - I have 2 builders working in tandem, ergo 2 units every 90 min or 4 units every 180 min (barring any "redo" work) there is no work on the weekends what is the total elapse day to complete the builds ?
so on 6/6 I have an additional builder arriving, making the total builder count to 3 - I now have 49 units to build - so now I can complete 6 units in the same 270 min build window - what is the adjusted elapse time to complete the build. I can get the first build with 2 builders, 3 builders, and 4 builders I am unable to "trickle down" the cycle count, and days to completion

there must be a simpler way to just to see the "start day" and "end day" based on the number of builders

Book1
	A	B	C	D	E
1		Units	Available Time	Time to Build
2		53	240	90
3	Total Build Minutes	4770
4
5	Assigned Builders	1	2	3	4
6	Available Build Time	240	480	720	960
7
8	Units able to Complete
9	Wed 06/1/22	2	5	8	10
10	Remaining	51	48	45	43
11	Thu 06/2/22	2	5	8	10
12	Remaining	49	43	37	33
13	Mon 06/6/22	2	5	8	10
14	Remaining	47	38	29	23
15	Mon 06/6/22	2	5	8	10
16	Remaining	45	33	21	13
17	Mon 06/6/22	2	5	8	10
18	Remaining	43	28	13	3
Sheet1

Cell Formulas
Range	Formula
B3	B3	=B2*D2
B6	B6	=$B$5*$C$2
C6:E6	C6	=C5*$C$2
B9,B11,B13,B15,B17	B9	=(INT($B$6/$D$2))
C9,C11,C13,C15,C17	C9	=(INT($C$6/$D$2))
D9,D11,D13,D15,D17	D9	=(INT($D$6/$D$2))
E9,E11,E13,E15,E17	E9	=(INT($E$6/$D$2))
B10	B10	=B2-B9
C10	C10	=B2-C9
D10	D10	=B2-D9
E10	E10	=B2-E9
B12:E12,B14:E14,B16:E16,B18:E18	B12	=B10-B11
A11	A11	=A9+1

 

[Dan_W]

Sorry, I only just saw this now. Thank you very much for your explanation - this is very helpful. I see what you're saying now. I will give it some thought tomorrow, and will let you know if I come up with anything. I'm absolutely certain there is a solution, but I have a nagging feeling that it's probably one of these amazing data/forecasting tools/functions in Excel that I've been trying to stay away from as much as possible (there are things in Excel don't even go near because I'm running out of room in my brain...)

 

[robbarba]

lol... thanks! I am took the data that showed and extrapolated the results. It was the long way, but I know that this group has a shorter version utilizing the functions that we both are shy around...