Search code examples
excelexcel-formulaweek-number

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

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.

enter image description here

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.

Solution

  • Proof of Concept:

    enter image description here

    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

    enter image description here