Need help building a model in Excel

  • Thread starter Thread starter jerry.ranch
  • Start date Start date
J

jerry.ranch

I'm modeling a two-step industrial process for work

Step #1 occurs anytime (date or day of week), step #1 requires 21 days
to completion

Step #2 follows step #1, but occurs ONLY on Mondays, so, for
example,for a particular instance, if (start date of step #1 + 21
days) is a Wed, step #2 doesn't commence until the following Monday.

I'm counting pieces (widgets) that will pass from step#1 to step#2,
and this model helps to provide estimates for the resources needed to
complete step #2

I've having intellectual problems trying to slove this


So input is count of widgets entering the process at starte date (step
#1), and output is count of widgets that enter step #2 on Mondays only
after at least a 21 day processing in step #1

How would I model this with simple logic and mathematical equations in
excel. I can convert date to day of week (text or number) and maybe
do some math with that (if mondays are always 1, for example)

Thanks
Jerry
 
Tell us about Sunday.
No production?
But do Sundays count in the 21 days to process?
best wishes
 
Row 1 has labels: Date, Input, Output date
Column A has dates; incremented by 1 (I started with Oct 1 2006)
Column B has production numbers (widgets entering system)
Column C has output date with this formula
=A2+21+CHOOSE(WEEKDAY(A2),1,0,6,5,4,3,2)
We add 21 days as required to process and then add a variable to make the
output a Monday

E1 has label "Output Date", F1 has label "Output"
E2 has start date (Oct 23 2006)
E3 has formula =E2+7 and this is copied down the column maybe for 10 rows
F2 has formula =SUMIF(C:C,E2,B:B) and this is copied down the column

I will send your a file (not to newsgroup)

best wishes
 
Back
Top