Weeks in a Calendar
 

I have a spreadsheet where I want to break up the weeks of the month by each
month. The work week begins Sat @12:01AM and ends Friday at midnight. For
instance JAN 05 would look like:
Wk1 Wk2 Wk3 WK4 Wk5 Wk6
1-7 8-14 15-21 22-28 29-31

July 05 would be:
Wk1 Wk2 Wk3 WK4 Wk5 Wk6
1 2-8 9-15 16-22 23-29 30-31

and so on. TIA

Greg

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:D2, E2:G2, H2:J2, K2:M2, N2:P2, 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+1DAY(EOMONTH(A1,0)),DAY(EOMONTH(A1,0)),J3+ 1)
L3 -
M3 =IF(K3+6DAY(EOMONTH(A1,0)),DAY(EOMONTH(A1,0)),K3+ 6)
N3
=IF(M3=DAY(EOMONTH(A1,0)),"",IF(M3+1DAY(EOMONTH(A 1,0)),DAY(EOMONTH(A1,0)),M
3+1))
O3 =IF(P3="","","-")
P3
=IF(OR(N3="",N3=DAY(EOMONTH(A1,0))),"",IF(N3+6DAY (EOMONTH(A1,0)),DAY(EOMONT
H(A1,0)),N3+6))
Q3
=IF(OR(P3=DAY(EOMONTH(A1,0)),P3=""),"",IF(P3+1DAY (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+6DAY (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


"GregR" wrote in message
...
I have a spreadsheet where I want to break up the weeks of the month by
each
month. The work week begins Sat @12:01AM and ends Friday at midnight. For
instance JAN 05 would look like:
Wk1 Wk2 Wk3 WK4 Wk5 Wk6
1-7 8-14 15-21 22-28 29-31

July 05 would be:
Wk1 Wk2 Wk3 WK4 Wk5 Wk6
1 2-8 9-15 16-22 23-29 30-31

and so on. TIA

Greg

I didn't try the suggestion, but =eomonth() will only work if the analysis
toolpak addin is loaded.

Tools|Addins

(If it's not installed, the OP may need the Office CD to install it.)

Michael Malinsky wrote:

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:D2, E2:G2, H2:J2, K2:M2, N2:P2, 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+1DAY(EOMONTH(A1,0)),DAY(EOMONTH(A1,0)),J3+ 1)
L3 -
M3 =IF(K3+6DAY(EOMONTH(A1,0)),DAY(EOMONTH(A1,0)),K3+ 6)
N3
=IF(M3=DAY(EOMONTH(A1,0)),"",IF(M3+1DAY(EOMONTH(A 1,0)),DAY(EOMONTH(A1,0)),M
3+1))
O3 =IF(P3="","","-")
P3
=IF(OR(N3="",N3=DAY(EOMONTH(A1,0))),"",IF(N3+6DAY (EOMONTH(A1,0)),DAY(EOMONT
H(A1,0)),N3+6))
Q3
=IF(OR(P3=DAY(EOMONTH(A1,0)),P3=""),"",IF(P3+1DAY (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+6DAY (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

"GregR" wrote in message
...
I have a spreadsheet where I want to break up the weeks of the month by
each
month. The work week begins Sat @12:01AM and ends Friday at midnight. For
instance JAN 05 would look like:
Wk1 Wk2 Wk3 WK4 Wk5 Wk6
1-7 8-14 15-21 22-28 29-31

July 05 would be:
Wk1 Wk2 Wk3 WK4 Wk5 Wk6
1 2-8 9-15 16-22 23-29 30-31

and so on. TIA

Greg



--

Dave Peterson