I have a solution, but it is seems somewhat complicated to explain via the
NG but I'll give it a shot.
First, maximize this window. Second, if you copy and paste, make sure that
when you paste, there are no hard returns buried in the formula or it will
not work.
Cell A1 contains the month you need. The format can be anything you want,
but it MUST be entered as mm/dd/yy, with dd always being 1, for the first of
the month(i.e., use 1/1/05 for the beginning of January and 7/1/05 for the
beginning of July).
Merge cells B2
2, E2:G2, H2:J2, K2:M2, N2
2, Q2:S2. Input the following:
Cell Value
B2 Wk1
E2 Wk2
H2 Wk3
K2 Wk4
N2 =IF(AND(N3="",P3=""),"","Wk5")
Q2 =IF(AND(Q3="",S3=""),"","Wk6")
B3 =IF(WEEKDAY(A1)=6,"",1)
C3 =IF(B3="","","-")
D3 =IF(WEEKDAY(A1)=6,1,IF(WEEKDAY(A1)=7,7,7-WEEKDAY(A1)))
E3 =D3+1
F3 -
G3 =E3+6
H3 =G3+1
I3 -
J3 =H3+6
K3 =IF(J3+1>DAY(EOMONTH(A1,0)),DAY(EOMONTH(A1,0)),J3+1)
L3 -
M3 =IF(K3+6>DAY(EOMONTH(A1,0)),DAY(EOMONTH(A1,0)),K3+6)
N3
=IF(M3=DAY(EOMONTH(A1,0)),"",IF(M3+1>DAY(EOMONTH(A1,0)),DAY(EOMONTH(A1,0)),M
3+1))
O3 =IF(P3="","","-")
P3
=IF(OR(N3="",N3=DAY(EOMONTH(A1,0))),"",IF(N3+6>DAY(EOMONTH(A1,0)),DAY(EOMONT
H(A1,0)),N3+6))
Q3
=IF(OR(P3=DAY(EOMONTH(A1,0)),P3=""),"",IF(P3+1>DAY(EOMONTH(A1,0)),DAY(EOMONT
H(A1,0)),P3+1))
R3 =IF(OR(Q3="",S3=""),"","-")
S3
=IF(OR(Q3=DAY(EOMONTH(A1,0)),Q3=""),"",IF(Q3+6>DAY(EOMONTH(A1,0)),DAY(EOMONT
H(A1,0)),Q3+6))
I got a bit fancy by making the Wk5 and Wk6 headers disappear if there are
no values under them and the dashes "-" disappear if they are not needed.
I tried this with all 12 months in 2005 and it seems to work.
HTH,
Mike