|
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 |
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 |
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 |
| All times are GMT +1. The time now is 11:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com