Use of Workday

sjg123

New Member
Joined
Nov 16, 2020
Messages
1
Office Version
  1. 2013
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.
 

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
Joined
Dec 30, 2008
Messages
12,910
Office Version
  1. 365
Platform
  1. Windows
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:

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,757
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,910
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Feb 15, 2002
Messages
4,757
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top