@ > Home > Contents > Assigning weekly values to months Excel 4+

Task: Values related to week numbers (ISO8601 date representation, week starting on Monday) are to be assigned to months. Problem: A week ncan be part of two months.

```        A      B   C      D          E      F        G
1 YearWeek Val's   Month      Splitvalues   Weekdays
2   200652   14    01.12.2006         14                 7
3   200701   14    01.01.2007         38
4   200702    7    01.02.2007         28    (splitted to
5   200703    7    01.03.2007         31    Mon-Sun
6   200704    7    01.04.2007         30    of the months)
7   200705    7    01.05.2007         31
8   200706    7    01.06.2007         30
9   200707    7    01.07.2007         31
10   200708    7    01.08.2007         31
11   200709    7    01.09.2007         30
12   200710    7    01.10.2007         31
13   200711    7    01.11.2007         30
14   200712    7    01.12.2007         31
15   200713    7    01.01.2008          6
16   200714    7    01.02.2008          0
17   200715    7    01.03.2008          0
18   200716    7                      392
... until ...
54   200752    7
55   200753    7
56           392                                            ```

Conventions:

A2:A55 has the six-digit calendarial week (neither necessarily sorted nor in subsequent order). It gets the name "KW". Format it 0000"-"00,if you like.
B2:B55 contains the values, named "Werte"
Starting at D2 you have the (subsequent running!) firsts of months; therefore copy D3: =D2+32-DAY(D2+31) down
G2 (named as "Wochentage") defines the number of week days starting on monday. You may choose from 1 (Mon-Mon) to 7 (Mon-Sun).

Following formulae have to be named via Insert Name Define:

FMo =("4.1."&INT(KW%))+MOD(KW,100)*7-MOD("2.1."&INT(KW%);7)
TageLfdMonat =IF(DAY(FMo)<8;IF(TAG(FMo)-8+Wochentage<0;;IF(FMo)-8+Wochentage);Wochentage)
TageVorMonat =Wochentage-TageLfdMonat
AuftLfdM =TageLfdMonat/Wochentage*Werte
AuftVorM =TageLfdMonat/Wochentage*Werte
Aufteilwert =SUMPRODUCT((FMo>=D2)*(FMo<D3)*AuftLfdM+(FMo>=D2+32-DAY(D2+31))*(FMo<D3+32-DAY(D3+31))*AuftVorM)
(Aufteilwert has to be defined on active cell E2!)

E2: =Aufteilwert (copy down)

Find the following monday (FMo) of a given week number, by Franz Pölt (in German).

The model does not work where a begin of the week is not Monday (ISO8601 date representation, mandatory in the European Union). USA has Sunday, Arabia Saturday.

Remarks:

If months don't have to be following each other, change "Aufteilwert" to:
=SUMPRODUCT((FMo>=D2)*(FMo<D2+32-DAY(D2+31))*AuftLfdM+(FMo>=D2+32-DAY(D2+31))*(FMo<D2+63-DAY(D2+62))*AuftVorM)

Why? The formula in row 2 refers to no other than row 2 (not considering arrays in this aspect). Also, it is safer then.

Don't complain about the values of column 2. They just have to result in the exact number of days per month so that the formula proves right.

Odds:

Normally, it does not make a difference whether or not: But here not naming some parts of the formula (f.e. FMo) makes it malfunction.