Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ploting dates against a calendar and not as a simple events | Charts and Charting in Excel | |||
Calendar 13 Periods 4 weeks each | Excel Discussion (Misc queries) | |||
Problem with data using IF and Nested IF statements possibly??? | Excel Discussion (Misc queries) | |||
Fiscal Calendar | Excel Discussion (Misc queries) | |||
calendar spreadsheet | Excel Discussion (Misc queries) |