How to spread out data based on dates and weeks.

Hi,

I am trying to spread out some production figures on weeks.

My input data is start_date, end_date, and Qty. This I want to spread out on a weekly basis ( I have a row that contains week 1 - 52 (2006) - 1 - 52 (2007)) and keep sundays noneproductive.

Eg:
06-mar-06, 19-mar-06, 1200 units would give me 600 in week 10 & 11 ( days in week 10 and 6 days in week 11 @ 100 units/day.
This is simple enough.

However, it becomes more difficuelt once I have a range such as
08-mar-06, 19-mar-06, 1200 units which should give me 480 units in week 10 & 720 units in week 11 (4 days in week 10 and 6 in week 11 @ 120 units/day)

The idea is to place a production order in one row and be able to calculate each weeks production by summing up the rows.

Any ideas as how to solve this problem?

I have attached a file that shows the data how I understand it from your description and based on that I have created a custom function that will "spread" the production quantity over the number of weeks as you laid out. The only "Snag" is that the week numbers have to run from 1 which is the 1st week of 2006 to 104 which is the last week of 2007. You will see that I laid out the data this way on the spreadsheet. I tested a few examples as well as the ones that you listed and got your expected results.

Let me know if this fits the bill.


Spread Production Qty.zip

Originally Posted by c991257

Hi,

I am trying to spread out some production figures on weeks.

My input data is start_date, end_date, and Qty. This I want to spread out on a weekly basis ( I have a row that contains week 1 - 52 (2006) - 1 - 52 (2007)) and keep sundays noneproductive.

Eg:
06-mar-06, 19-mar-06, 1200 units would give me 600 in week 10 & 11 ( days in week 10 and 6 days in week 11 @ 100 units/day.
This is simple enough.

However, it becomes more difficuelt once I have a range such as
08-mar-06, 19-mar-06, 1200 units which should give me 480 units in week 10 & 720 units in week 11 (4 days in week 10 and 6 in week 11 @ 120 units/day)

The idea is to place a production order in one row and be able to calculate each weeks production by summing up the rows.

Any ideas as how to solve this problem?

The weeknumbering is a problem. I use the WEEKNUM function to get the weeknumber because that can be converted into a date, month or year. That makes it easier to read the spreadsheet.
Anyhow, the function seems to get the work done, but I found an error.

If I enter 24-mar-06 (week 12) and 05-dec-06 (week 49) it starts in week 15 and ends in week 71.


I actually just came up with a function that solves the problem

=IF(week-start_date<-7,0,
IF(week>end_date,0,
IF(AND(week-start_date>=0,end_date-week>=7),7*qty_day,
IF(end_date-week<7,(end_date-week+1)*qty_day,(7-(week-start_date)*-1)*qty_day)
)))

where days is the number of days between start_date and end_date. It isn't pretty but is solves the problem.

A VBA function would be much smarter, but I'm not good enough at programming to write one.

But thanks - the spread_qty() was helpful.