G
Guest
I am trying to create a schedule that spreads production start dates
throughout the month based on the number of untis required that month. Each
unit is unique, so it may start on the same day as another, but may require
more or less time to complete. Trying to logically think this through, I've
created a spreadsheet with one unit number per row and have broken up steps
as follows. I know some of this could be strung together, but I wanted to see
the steps and make sure everything was working correctly.
1. Determine the 1st workday on the month (USING WEEDKAY)
2. Determine the number of days in the month (using EOMONTH)
3. Divide the number of days/mth by the number of units required = interval
(1 unit started every ___ days)
4. If 2 criteria are the same (which represents starts within the same
month), then beginning with the 1st workday, add the interval. If the
criteria are different (which represents a month change), then use the 1st
workday of the (next) month (=IF(AND(D2=D1,C2=C1),G1+K1,F2))
5. Check if the calculated start date is a workday, if not force
(=IF(WEEKDAY(L2)=1,L2+1,IF(WEEKDAY(L2)=7,L2-1,L2)))
This works fine, but I have 2 problems, and thus 2 questions:
1. Sometimes adding the interval will push the last start date into the next
month, which is unacceptable - How do I keep this from happening?
2. I need to present an alternate schedule with 2 untis started every ___
days. - How can I start 2 units on one day, then add the interval to the next
2 starts, and so on, changing with the next month.
If needed, I can send a sample of the data.
Your help is appreciated.
Janice
throughout the month based on the number of untis required that month. Each
unit is unique, so it may start on the same day as another, but may require
more or less time to complete. Trying to logically think this through, I've
created a spreadsheet with one unit number per row and have broken up steps
as follows. I know some of this could be strung together, but I wanted to see
the steps and make sure everything was working correctly.
1. Determine the 1st workday on the month (USING WEEDKAY)
2. Determine the number of days in the month (using EOMONTH)
3. Divide the number of days/mth by the number of units required = interval
(1 unit started every ___ days)
4. If 2 criteria are the same (which represents starts within the same
month), then beginning with the 1st workday, add the interval. If the
criteria are different (which represents a month change), then use the 1st
workday of the (next) month (=IF(AND(D2=D1,C2=C1),G1+K1,F2))
5. Check if the calculated start date is a workday, if not force
(=IF(WEEKDAY(L2)=1,L2+1,IF(WEEKDAY(L2)=7,L2-1,L2)))
This works fine, but I have 2 problems, and thus 2 questions:
1. Sometimes adding the interval will push the last start date into the next
month, which is unacceptable - How do I keep this from happening?
2. I need to present an alternate schedule with 2 untis started every ___
days. - How can I start 2 units on one day, then add the interval to the next
2 starts, and so on, changing with the next month.
If needed, I can send a sample of the data.
Your help is appreciated.
Janice