@ > Home > Contents > Calendar weeks CW of a month  Excel 97+


Following formulae calculate aspects of A2 which is a month given in a date:

the counted number of CW touched by the month (4, 5 or 6):
B2: =CHOOSE((DATE(YEAR(A2),MONTH(A2)+1,1)-A2+DAY(A2)-16+MOD(A2-DAY(A2)-1,7))/7,4,5,6)
abbreviated by Christian Hapke (2007/01/23) as:
B2: =CHOOSE((DAY(DATE(YEAR(A1),MONTH(A1)+1,))-15+MOD(A1-DAY(A1)-1,7))/7,4,5,6)

the first CW's N of the above:
C2: =INT((A2-DAY(A2)-DATE(YEAR(A2),1,-4-MOD(DATE(YEAR(A2),1,2),7)))/7)

and the last CW's N respectively:
D2: =C2-1+B2

Example: A2: 2010/02/14 - B2: 4 - C2: 5 - D2: 8
That means: Feb 2010 has 4 CW touching it, beginning with CW 5 and ending with CW 8. This is the only month with no overlapping CW until Feb 2038. The same stands for the N of touching CW which otherwise always result in 5 or 6. A 6 repeats after 3 months minimum.

The start of a CW is Monday. The formulae treat year-overlapping CW as follows:

3 or less days of it in the old year appear as week 52 or 53 despite the regulation this being week 1.
3 or less days of it in the new year appear as week 0 despite the regulation this being week 52 or 53.
See the file here

For a link to the correct treatment see the German page.