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.