Urgent date/scheduling calc needed

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

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

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

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