View Single Post
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
jct
 
Posts: n/a
Default Urgent date/scheduling calc needed

Thank you. I do use the analysis toolpak.

What if I'm using the following format? I need to fill-in, producing 2 units
per production date (workdays only, and keeping within the specified month).
I can calculate the interval, but can't get my head around how to change the
production date...?

Unit #___ProdtntMth___Category__Days/Mth__Units/Mth__Interval__ProdtnDate

ac02036__5/1/06_____AC_____31_____5_____12_____5/1/06
ac02035__5/1/06_____AC_____31_____5_____12_____5/1/06
ac02034__5/1/06_____AC_____31_____5_____12_____5/13/06
ac02033__5/1/06_____AC_____31_____5_____12_____5/13/06
ac02032__5/1/06_____AC_____31_____5_____12_____5/25/06
ac02031__6/1/06_____AC_____30_____3_____20_____
ac02028__6/1/06_____AC_____30_____3_____20_____
ac02027__6/1/06_____AC_____30_____3_____20_____
ap02059__3/1/06_____AC_____31_____4_____14_____
ap02061__3/1/06_____AC_____31_____4_____14_____
ap02062__3/1/06_____AC_____31_____4_____14_____
ap02063__3/1/06_____AC_____31_____4_____14_____
ap02088__4/1/06_____AC_____30_____6_____10_____
ap02087__4/1/06_____AC_____30_____6_____10_____
ap02086__4/1/06_____AC_____30_____6_____10_____
ap02085__4/1/06_____AC_____30_____6_____10_____
ap02064__4/1/06_____AC_____30_____6_____10_____
ap01033__4/1/06_____AC_____30_____6_____10_____
ap02080__5/1/06_____AC_____31_____5_____12_____
ap02079__5/1/06_____AC_____31_____5_____12_____
ap02078__5/1/06_____AC_____31_____5_____12_____
ap02065__5/1/06_____AC_____31_____5_____12_____
ap02066__5/1/06_____AC_____31_____5_____12_____

"Gary L Brown" wrote:

Forgot to mention that the Analysis Toolpak addin needs to be available. If
you're not sure...

ToolsAddins...
If 'Analysis Toolpak' is not checked, check it.
Select OK.

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"Gary L Brown" wrote:

Assume:

Cell A1 = # units produced evenly scheduled throughout the month
Cell B1 = # of units
Cell C1 = # of schedule dates
Cell D1 = # of workdays in between schedules
Cell E1 = 1st Day of Month
Cell F1 = Schedule 1
Cell G1 = Schedule 2
Cell H1 = Schedule 3
Cell I1 = Schedule 4
Cell J1 = Schedule 5
Cell K1 = Schedule 6
Cell L1 = Schedule 7
Cell M1 = Schedule 8
Cell N1 = Schedule 9
Cell O1 = Schedule 10

Cell A2 = YOUR INPUT - EXAMPLE: 2
Cell B2 = YOUR INPUT - EXAMPLE: 5
Cell C2 = =ROUNDUP(B2/A2,0)
Cell D2 = =ROUND(NETWORKDAYS(EOMONTH(E2,-1)+1,EOMONTH(E2,0))/C2,0)
Cell E2 = YOUR INPUT - EXAMPLE: 01-Mar-2006
Cell F2 = =IF(COUNTA($F1:F1)$C2,"",WORKDAY(E2-1,$D2))
copy Cell F2 over to G2:O2

Using the example information above...
Cell A2 = 2
Cell B2 = 5
Cell C2 = 3
Cell D2 = 8
Cell E2 = 01-Mar-2006
Cell F2 = 10-Mar-2006
Cell G2 = 21-Mar-2006
Cell H2 = 30-Mar-2006
Cell I2 =
Cell J2 =
Cell K2 =
Cell L2 =
Cell M2 =
Cell N2 =
Cell O2 =

HTH,
--
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"jct" wrote:

How can I calculate the following:

# units / mth, 2 units produced evenly scheduled throughout the month, only
on weekdays

6 units - Jan (resulting in 3 schedule dates)
4 units - Feb (resulting in 2 schedule dates)
5 units - Mar (resulting in 3 schedule dates)

How would the calculation be modified to schedule 1 unit evenly throughout
the month?

Thank you in advance,
Janice