View Single Post
  #2   Report Post  
Old February 23rd 06, 10:20 PM posted to microsoft.public.excel.worksheet.functions
Gary L Brown
 
Posts: n/a
Default 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

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