Use of Workday

[sjg123]

Hi,

I'm looking for a bit of help, if possible, with trying to produce a production schedule.

We manufacture 3 units per day (number 3 contained within Cell A1). Say Job A contains 4 units (cell B1 = 4), Job B 5 units (C1 = 5) and Job C 13 (D1 = 13).

If Job A starts on the date 16/11 (Cell F1), I can use WORKDAY(F1,(B1/A1)) to calculate that job finishes on 17/11. Say that formula goes into Cell G1.

This however leaves 2 units capacity for job B on the 17/11. The problem I have is I cant find a smart way to use the new date given in G1 to schedule the next job accurately, using the 2 units spare capacity. The easy was would be to say the start date of job B is simply G1+1 however that would then lose the 2 units spare on 17/11.

I don't even know if I'm trying to go about this the right way!

Any help appreciated.

 

[jasonb75]

It would depend on how your final sheet is going to be set up. With the example provided, I would use

=WORKDAY($F1,SUM($B1:B1)/$A1)

in G1, then drag it right to H1 and I1.
Changing the layout slightly, this might be easier to follow.

Book1
	A	B	C	D	E	F	G
1	Job	Units	Start	Finish		Capacity	3
2	A	4	16/11/2020	17/11/2020
3	B	5	17/11/2020	19/11/2020
4	C	13	19/11/2020	25/11/2020
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=WORKDAY($C$2,SUM(B$2:B2)/$G$1)
C3:C4	C3	=WORKDAY($C$2,SUM(B$2:B2,1)/$G$1)

If you want to show the date and time (1st 2nd or 3rd item of the day) then it will need something a little more creative.

 

[Dave Patton]

T202011a.xlsm
	A	B	C	D	E
1	3	Daily capacity
2
3	Job	Requirement	Start	Completed	Capacity Avail
4	A	4	Mon 16-Nov-20	Tue 17-Nov-20	0.667
5	B	5	Tue 17-Nov-20	Wed 18-Nov-20	0.000
6	C	13	Thu 19-Nov-20	Wed 25-Nov-20	0.667
9c

Cell Formulas
Range	Formula
D4	D4	=WORKDAY(C4,ROUNDUP(B4/$A$1,0)-1)
E4	E4	=D4-C4+1-B4/$A$1
C5	C5	=D4
D5:D6	D5	=WORKDAY(C5,(B5-E4*$A$1)/$A$1)
E5	E5	=D5-C4+1-SUM(B4:B5)/$A$1
C6	C6	=D5+1
E6	E6	=NETWORKDAYS(C6,D6)-B6/A1

 

[jasonb75]

I just noticed an error in my method but too late to edit previous post

Book1
	A	B	C	D	E	F	G
1	Job	Units	Start	Finish		Capacity	3
2	A	4	16/11/2020	17/11/2020
3	B	5	17/11/2020	18/11/2020
4	C	13	19/11/2020	25/11/2020
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=WORKDAY($C$2,SUM(B$2:B2,-1)/$G$1)
C3:C4	C3	=WORKDAY($C$2,SUM(B$2:B2)/$G$1)

 

[Dave Patton]

Edit to suggestion

T202011a.xlsm
	A	B	C	D	E
1	3	Daily capacity
2	Job	Units	Start	Completed	Available
3	A	4	16-Nov-20	17-Nov-20	0.667
4	B	5	17-Nov-20	18-Nov-20	0.000
5	C	13	19-Nov-20	25-Nov-20	0.667
9c

Cell Formulas
Range	Formula
D3:D5	D3	=WORKDAY(C3,(B3-N(E2)*$A$1)/$A$1)
E3	E3	=D3-C3+1-B3/$A$1
C4:C5	C4	=IF(E3=0,WORKDAY(D3,1),D3)
E4	E4	=D4-C3+1-SUM(B3:B4)/$A$1
E5	E5	=NETWORKDAYS(C5,D5)-B5/A1