Distribute value equally over weeks by start- and end-date

Question

I want to distribute a certain value (D4:D6) equally over the week-numbers (E3:J3) by its start- and end-date (B4:C6), as shown in the example.

A formula/vba script should do the following things:

Check which week-number the start- and end-date has
Divide the value by the amount of weeks between start- and end-date
Place the values in the matching column in the same row

The example in text format to copy:

                                2017        2018        
    Start       End       Value 50  51  52  1   2   3
    26.12.2017  04.01.2018  20  -   -   10  10  -   -
    12.12.2017  24.12.2017  50  25  25  -   -   -   -
    11.12.2017  10.01.2018  60  12  12  12  12  12  -

Also glad about hints / ideas how single steps could be achieved.

You'll need a formula in each of the destination cells. Look at the weeknumber function for a start on that step. — Ron Rosenfeld, Aug 23 '17 at 11:19
Checked that already. The weeknumber function has one big issue: it can not differentiate between years when calculating. If I want the amount of weeknumbers between the 51th week of 2017 (simply 51 for excel) and the first of 2018 (simply 1), excel will calculate 1-51 and give me -50 instead of the correct value: 3. — Falk, Aug 23 '17 at 11:26
You can compensate for that by checking the number of weeks in the preceding year. Something like `mod(endWeeknum-startWeeknum,weeks_in_startYear)`. And if the number of weeks being counted might be greater than `weeks_in_startYear`, a different approach would also work. — Ron Rosenfeld, Aug 23 '17 at 11:33
in you second row, should the value of fifty not be spread out over 3 weeks, weeks 50, 51 and 52? — Forward Ed, Aug 23 '17 at 12:01
This should do it for the beginning, since nearly every year (exception: 2020) has 52 weeks. `=mod(weeknum(end-cell;21)-weeknum(start-cell;21)+2;52)` gives out the correct value then. @Forward Ed: It is up to the start-day of the week. In Europe (at least in Germany) normally Monday is used as start point. In America it typically is Sunday if I am not mistaken. — Falk, Aug 23 '17 at 12:04
If you want to **compute** the last week number in a year, something like: `=MAX(WEEKNUM(DATE(year,12,{31,30,29,28}),21))` should do it. — Ron Rosenfeld, Aug 23 '17 at 12:44

Forward Ed · Accepted Answer · 2017-08-23T15:09:46.887

Proof of Concept:

place the following formula in E4 and copy down and right

=IF(WEEKNUM($C4,21)-WEEKNUM($B4,21)<0,IF(OR(E$3>=WEEKNUM($B4,21),E$3<=WEEKNUM($C4,21)),$D4/IF(WEEKNUM($C4,21)-WEEKNUM($B4,21)<0,MAX(WEEKNUM(DATE(YEAR($B4),12,{28,29,30,31}),21))-WEEKNUM($B4,21)+WEEKNUM($C4,21)+1,WEEKNUM($C4,21)-WEEKNUM($B4,21)+1),0),IF(AND(E$3>=WEEKNUM($B4,21),E$3<=WEEKNUM($C4,21)),$D4/IF(WEEKNUM($C4,21)-WEEKNUM($B4,21)<0,MAX(WEEKNUM(DATE(YEAR($B4),12,{28,29,30,31}),21))-WEEKNUM($B4,21)+WEEKNUM($C4,21)+1,WEEKNUM($C4,21)-WEEKNUM($B4,21)+1),0))

Now this is a built up formula from multiple cells that I back substitute the formulas to wind up with the monstrosity above. The break down is as follows.

STEP 1

Find the start week number. Place the following in B8.

=WEEKNUM($B4,21)

STEP 2

Find the end week number. Place the following in C8.

=WEEKNUM($C4,21)

STEP 3

Determine the maximum number of weeks in a year. Thanks to Ron Rosenfeld for this formula. Place the following in D8.

=MAX(WEEKNUM(DATE(YEAR($B4),12,{28,29,30,31}),21))

STEP 4

Determine if the week is in the same year or the following year. Place the following in E8.

=C8-B8

STEP 5

Determine the number of weeks. Place the following in F8.

=IF(E8<0,D8-B8+C8+1,C8-B8+1)

STEP 6

Average the value for each week. Place the following in G8.

=D4/F8

STEP 7

Determine if the average value belongs to a date header or the value of 0 (if you want an actual dash and not just formatting 0 as a dash then change 0 to -. Place the following formula in H8.

=IF($E8<0,IF(OR(E$3>=$B8,E$3<=$C8),$G8,0),IF(AND(E$3>=$B8,E$3<=$C8),$G8,0))

Copy the H8 formula to the right and down as required.

Caveat: Will work for a 1 year spread in work weeks. I have serious doubts that it would work over multi year start and end week.

Layout of steps

That is super close to a perfect solution. But you are right, it only works for a time periode of one year, since the formula will fill out cells containing the same weeknums from a different year as well. I maybe just need to tweak the reference to the year in the second row (or it is simply my not-example sheet - will do more testing soon and report). Anyway, that's an awesome formula. — Falk, Aug 23 '17 at 14:31
one way that MAY, and I stress may, simplify multi year for you is to place the date of the monday in the cell above/below the week number. That way you will always have a year attached to the column you a looking at. This is good in the case where you are not showing all 52 weeks. You wont have to go hunting for the year cell which may not be regularly positioned. — Forward Ed, Aug 23 '17 at 14:58
If you use the date in each column as mentioned above, I believe your formulas would also get much simpler to achieve the same objective. — Forward Ed, Aug 23 '17 at 15:10