Need help building a model in Excel

[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

 

[Bernard Liengme]

Tell us about Sunday.
No production?
But do Sundays count in the 21 days to process?
best wishes

 

[Bernard Liengme]

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