#1   Report Post  
GregR
 
Posts: n/a
Default 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


  #2   Report Post  
Michael Malinsky
 
Posts: n/a
Default

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




  #3   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Ploting dates against a calendar and not as a simple events Barb Reinhardt Charts and Charting in Excel 2 January 22nd 05 03:41 AM
Calendar 13 Periods 4 weeks each CHawk@Mcc Excel Discussion (Misc queries) 2 December 10th 04 05:27 PM
Problem with data using IF and Nested IF statements possibly??? Ajay Excel Discussion (Misc queries) 2 December 9th 04 10:23 AM
Fiscal Calendar Eileen Excel Discussion (Misc queries) 1 November 27th 04 10:29 AM
calendar spreadsheet G Graham Excel Discussion (Misc queries) 1 November 26th 04 06:38 PM


All times are GMT +1. The time now is 03:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"