# Use of Workday

#### sjg123

##### New Member
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.

Any help appreciated.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### jasonb75

##### Well-known Member
It would depend on how your final sheet is going to be set up. With the example provided, I would use
Excel Formula:
``=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
ABCDEFG
1JobUnitsStartFinishCapacity3
2A416/11/202017/11/2020
3B517/11/202019/11/2020
4C1319/11/202025/11/2020
Sheet1
Cell Formulas
RangeFormula
D2:D4D2=WORKDAY(\$C\$2,SUM(B\$2:B2)/\$G\$1)
C3:C4C3=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.

Last edited:

#### jasonb75

##### Well-known Member
I just noticed an error in my method but too late to edit previous post
Book1
ABCDEFG
1JobUnitsStartFinishCapacity3
2A416/11/202017/11/2020
3B517/11/202018/11/2020
4C1319/11/202025/11/2020
Sheet1
Cell Formulas
RangeFormula
D2:D4D2=WORKDAY(\$C\$2,SUM(B\$2:B2,-1)/\$G\$1)
C3:C4C3=WORKDAY(\$C\$2,SUM(B\$2:B2)/\$G\$1)

#### Dave Patton

##### Well-known Member
Edit to suggestion
T202011a.xlsm
ABCDE
13Daily capacity
2JobUnitsStartCompletedAvailable
3A416-Nov-2017-Nov-200.667
4B517-Nov-2018-Nov-200.000
5C1319-Nov-2025-Nov-200.667
9c
Cell Formulas
RangeFormula
D3:D5D3=WORKDAY(C3,(B3-N(E2)*\$A\$1)/\$A\$1)
E3E3=D3-C3+1-B3/\$A\$1
C4:C5C4=IF(E3=0,WORKDAY(D3,1),D3)
E4E4=D4-C3+1-SUM(B3:B4)/\$A\$1
E5E5=NETWORKDAYS(C5,D5)-B5/A1

Replies
2
Views
483
Replies
2
Views
125
Replies
6
Views
75
Replies
12
Views
360
Replies
5
Views
351

1,132,642
Messages
5,654,547
Members
418,139
Latest member
nimesh72

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back