Remember Me?

#1
February 23rd 06, 06:37 PM posted to microsoft.public.excel.worksheet.functions
 jct Posts: n/a
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?

Janice

#2
February 23rd 06, 10:20 PM posted to microsoft.public.excel.worksheet.functions
 Gary L Brown Posts: n/a
Urgent date/scheduling calc needed

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

"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?

Janice

#4
February 24th 06, 01:36 AM posted to microsoft.public.excel.worksheet.functions
 jct Posts: n/a
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...

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

HTH,
--
Gary Brown

"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

"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?

Janice

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Brento Excel Discussion (Misc queries) 0 February 9th 06 09:10 PM Irn Bru Freak Excel Discussion (Misc queries) 2 February 3rd 06 07:04 PM skarbanan Excel Worksheet Functions 23 December 30th 05 10:56 PM Brento Excel Discussion (Misc queries) 1 July 29th 05 12:35 AM Urgent Excel Worksheet Functions 2 December 14th 04 10:32 PM

All times are GMT +1. The time now is 06:10 AM.