number of days in a week that correspond to a month

MDuff

Well-known Member
Joined
Dec 29, 2002
Messages
529
number of days in a week that correspond to a month

Hi,

I am in need of a formula that can tell me how many days are in each week that correspond to a certain Month for example:


Week 30-Jan 6-Feb 13-Feb 20-Feb 27-Feb
Days In Feb 5 7 7 7 2


Also Nay know a good tool to post excel sheets on the Board


thanks a lot
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047

MDuff

Well-known Member
Joined
Dec 29, 2002
Messages
529
thanks I will check them out....
Any Ideas on the original question???
 

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047
For me, I can't tell what column or row you information lies in.

Maybe if you get the suggested downloads working for you, you can post a better shot.


Although this is not a shot of your sheet, the downoads will produce something like below that we can see:

Excel Workbook
ABCD
15361919.8
Sheet2
 

MDuff

Well-known Member
Joined
Dec 29, 2002
Messages
529

ADVERTISEMENT

Does this help I need a fromual to give me the Hard coded values in Row 15
Excel Workbook
ABCDEF
14Week30-Jan6-Feb13-Feb20-Feb27-Feb
15Days In Feb57772
Sheet1
Excel 2007
Cell Formulas
RangeFormula
C14=B14+7
D14=C14+7
E14=D14+7
F14=E14+7
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,146
Office Version
  1. 2007
Platform
  1. Windows
Try something like this:
Only date entry is in cell A2.
Excel Workbook
ABCDEFGH
1StartEndWk1Wk2Wk3Wk4Wk5Wk6
201/01/1131/01/11177772
301/02/1128/02/11577720
401/03/1131/03/11577750
501/04/1130/04/11277770
601/05/1131/05/11777730
701/06/1130/06/11477750
801/07/1131/07/11277780
901/08/1131/08/11677740
1001/09/1130/09/11377760
1101/10/1131/10/11177772
1201/11/1130/11/11577740
1301/12/1131/12/11377770
Sheet1
Excel 2003
Cell Formulas
RangeFormula
B2=DATE(YEAR(A2),MONTH(A2)+1,1)-1
B3=DATE(YEAR(A3),MONTH(A3)+1,1)-1
C2=8-WEEKDAY(A2)
C3=8-WEEKDAY(A3)
D2=IF(SUM($C2:C2)+7<$B2-$A2,7,$B2-$A2-SUM($C2:C2)+1)
D3=IF(SUM($C3:C3)+7<$B3-$A3,7,$B3-$A3-SUM($C3:C3)+1)
E2=IF(SUM($C2:D2)+7<$B2-$A2,7,$B2-$A2-SUM($C2:D2)+1)
E3=IF(SUM($C3:D3)+7<$B3-$A3,7,$B3-$A3-SUM($C3:D3)+1)
F2=IF(SUM($C2:E2)+7<$B2-$A2,7,$B2-$A2-SUM($C2:E2)+1)
F3=IF(SUM($C3:E3)+7<$B3-$A3,7,$B3-$A3-SUM($C3:E3)+1)
G2=IF(SUM($C2:F2)+7<$B2-$A2,7,$B2-$A2-SUM($C2:F2)+1)
G3=IF(SUM($C3:F3)+7<$B3-$A3,7,$B3-$A3-SUM($C3:F3)+1)
H2=IF(SUM($C2:G2)+7<$B2-$A2,7,$B2-$A2-SUM($C2:G2)+1)
H3=IF(SUM($C3:G3)+7<$B3-$A3,7,$B3-$A3-SUM($C3:G3)+1)
A3=DATE(YEAR(A2),MONTH(A2)+1,1)
 

MDuff

Well-known Member
Joined
Dec 29, 2002
Messages
529

ADVERTISEMENT

thanks so much

But not sure If i have this wrong or I am not understanding it but week 5 in July has 8 days in it but if I do this manually with Sunday weeks I get:
Excel Workbook
BCDEFG
17July
1826-Jun3-Jul10-Jul17-Jul24-Jul31-Jul
19277771
Sheet1
Excel 2007


Please let me know if I am reading this wrong or misunderstand it
Excel Workbook
BCDEFG
17July
1826-Jun3-Jul10-Jul17-Jul24-Jul31-Jul
19277771
Sheet1
Excel 2007


Please let me know if i am reading this wrong or miundersard it
Excel Workbook
ABCDEFGH
1StartEndWk1Wk2Wk3Wk4Wk5Wk6
21/1/20111/31/2011177772
32/1/20112/28/2011577720
43/1/20113/31/2011577750
54/1/20114/30/2011277770
65/1/20115/31/2011777730
76/1/20116/30/2011477750
87/1/20117/31/2011277780
98/1/20118/31/2011677740
109/1/20119/30/2011377760
1110/1/201110/31/2011177772
1211/1/201111/30/2011577740
1312/1/201112/31/2011377770
Sheet1
Excel 2007
Cell Formulas
RangeFormula
B2=DATE(YEAR(A2),MONTH(A2)+1,1)-1
B3=DATE(YEAR(A3),MONTH(A3)+1,1)-1
B4=DATE(YEAR(A4),MONTH(A4)+1,1)-1
B5=DATE(YEAR(A5),MONTH(A5)+1,1)-1
B6=DATE(YEAR(A6),MONTH(A6)+1,1)-1
B7=DATE(YEAR(A7),MONTH(A7)+1,1)-1
B8=DATE(YEAR(A8),MONTH(A8)+1,1)-1
B9=DATE(YEAR(A9),MONTH(A9)+1,1)-1
B10=DATE(YEAR(A10),MONTH(A10)+1,1)-1
B11=DATE(YEAR(A11),MONTH(A11)+1,1)-1
B12=DATE(YEAR(A12),MONTH(A12)+1,1)-1
B13=DATE(YEAR(A13),MONTH(A13)+1,1)-1
C2=8-WEEKDAY(A2)
C3=8-WEEKDAY(A3)
C4=8-WEEKDAY(A4)
C5=8-WEEKDAY(A5)
C6=8-WEEKDAY(A6)
C7=8-WEEKDAY(A7)
C8=8-WEEKDAY(A8)
C9=8-WEEKDAY(A9)
C10=8-WEEKDAY(A10)
C11=8-WEEKDAY(A11)
C12=8-WEEKDAY(A12)
C13=8-WEEKDAY(A13)
D2=IF(SUM($C2:C2)+7<$B2-$A2,7,$B2-$A2-SUM($C2:C2)+1)
D3=IF(SUM($C3:C3)+7<$B3-$A3,7,$B3-$A3-SUM($C3:C3)+1)
D4=IF(SUM($C4:C4)+7<$B4-$A4,7,$B4-$A4-SUM($C4:C4)+1)
D5=IF(SUM($C5:C5)+7<$B5-$A5,7,$B5-$A5-SUM($C5:C5)+1)
D6=IF(SUM($C6:C6)+7<$B6-$A6,7,$B6-$A6-SUM($C6:C6)+1)
D7=IF(SUM($C7:C7)+7<$B7-$A7,7,$B7-$A7-SUM($C7:C7)+1)
D8=IF(SUM($C8:C8)+7<$B8-$A8,7,$B8-$A8-SUM($C8:C8)+1)
D9=IF(SUM($C9:C9)+7<$B9-$A9,7,$B9-$A9-SUM($C9:C9)+1)
D10=IF(SUM($C10:C10)+7<$B10-$A10,7,$B10-$A10-SUM($C10:C10)+1)
D11=IF(SUM($C11:C11)+7<$B11-$A11,7,$B11-$A11-SUM($C11:C11)+1)
D12=IF(SUM($C12:C12)+7<$B12-$A12,7,$B12-$A12-SUM($C12:C12)+1)
D13=IF(SUM($C13:C13)+7<$B13-$A13,7,$B13-$A13-SUM($C13:C13)+1)
E2=IF(SUM($C2:D2)+7<$B2-$A2,7,$B2-$A2-SUM($C2:D2)+1)
E3=IF(SUM($C3:D3)+7<$B3-$A3,7,$B3-$A3-SUM($C3:D3)+1)
E4=IF(SUM($C4:D4)+7<$B4-$A4,7,$B4-$A4-SUM($C4:D4)+1)
E5=IF(SUM($C5:D5)+7<$B5-$A5,7,$B5-$A5-SUM($C5:D5)+1)
E6=IF(SUM($C6:D6)+7<$B6-$A6,7,$B6-$A6-SUM($C6:D6)+1)
E7=IF(SUM($C7:D7)+7<$B7-$A7,7,$B7-$A7-SUM($C7:D7)+1)
E8=IF(SUM($C8:D8)+7<$B8-$A8,7,$B8-$A8-SUM($C8:D8)+1)
E9=IF(SUM($C9:D9)+7<$B9-$A9,7,$B9-$A9-SUM($C9:D9)+1)
E10=IF(SUM($C10:D10)+7<$B10-$A10,7,$B10-$A10-SUM($C10:D10)+1)
E11=IF(SUM($C11:D11)+7<$B11-$A11,7,$B11-$A11-SUM($C11:D11)+1)
E12=IF(SUM($C12:D12)+7<$B12-$A12,7,$B12-$A12-SUM($C12:D12)+1)
E13=IF(SUM($C13:D13)+7<$B13-$A13,7,$B13-$A13-SUM($C13:D13)+1)
F2=IF(SUM($C2:E2)+7<$B2-$A2,7,$B2-$A2-SUM($C2:E2)+1)
F3=IF(SUM($C3:E3)+7<$B3-$A3,7,$B3-$A3-SUM($C3:E3)+1)
F4=IF(SUM($C4:E4)+7<$B4-$A4,7,$B4-$A4-SUM($C4:E4)+1)
F5=IF(SUM($C5:E5)+7<$B5-$A5,7,$B5-$A5-SUM($C5:E5)+1)
F6=IF(SUM($C6:E6)+7<$B6-$A6,7,$B6-$A6-SUM($C6:E6)+1)
F7=IF(SUM($C7:E7)+7<$B7-$A7,7,$B7-$A7-SUM($C7:E7)+1)
F8=IF(SUM($C8:E8)+7<$B8-$A8,7,$B8-$A8-SUM($C8:E8)+1)
F9=IF(SUM($C9:E9)+7<$B9-$A9,7,$B9-$A9-SUM($C9:E9)+1)
F10=IF(SUM($C10:E10)+7<$B10-$A10,7,$B10-$A10-SUM($C10:E10)+1)
F11=IF(SUM($C11:E11)+7<$B11-$A11,7,$B11-$A11-SUM($C11:E11)+1)
F12=IF(SUM($C12:E12)+7<$B12-$A12,7,$B12-$A12-SUM($C12:E12)+1)
F13=IF(SUM($C13:E13)+7<$B13-$A13,7,$B13-$A13-SUM($C13:E13)+1)
G2=IF(SUM($C2:F2)+7<$B2-$A2,7,$B2-$A2-SUM($C2:F2)+1)
G3=IF(SUM($C3:F3)+7<$B3-$A3,7,$B3-$A3-SUM($C3:F3)+1)
G4=IF(SUM($C4:F4)+7<$B4-$A4,7,$B4-$A4-SUM($C4:F4)+1)
G5=IF(SUM($C5:F5)+7<$B5-$A5,7,$B5-$A5-SUM($C5:F5)+1)
G6=IF(SUM($C6:F6)+7<$B6-$A6,7,$B6-$A6-SUM($C6:F6)+1)
G7=IF(SUM($C7:F7)+7<$B7-$A7,7,$B7-$A7-SUM($C7:F7)+1)
G8=IF(SUM($C8:F8)+7<$B8-$A8,7,$B8-$A8-SUM($C8:F8)+1)
G9=IF(SUM($C9:F9)+7<$B9-$A9,7,$B9-$A9-SUM($C9:F9)+1)
G10=IF(SUM($C10:F10)+7<$B10-$A10,7,$B10-$A10-SUM($C10:F10)+1)
G11=IF(SUM($C11:F11)+7<$B11-$A11,7,$B11-$A11-SUM($C11:F11)+1)
G12=IF(SUM($C12:F12)+7<$B12-$A12,7,$B12-$A12-SUM($C12:F12)+1)
G13=IF(SUM($C13:F13)+7<$B13-$A13,7,$B13-$A13-SUM($C13:F13)+1)
H2=IF(SUM($C2:G2)+7<$B2-$A2,7,$B2-$A2-SUM($C2:G2)+1)
H3=IF(SUM($C3:G3)+7<$B3-$A3,7,$B3-$A3-SUM($C3:G3)+1)
H4=IF(SUM($C4:G4)+7<$B4-$A4,7,$B4-$A4-SUM($C4:G4)+1)
H5=IF(SUM($C5:G5)+7<$B5-$A5,7,$B5-$A5-SUM($C5:G5)+1)
H6=IF(SUM($C6:G6)+7<$B6-$A6,7,$B6-$A6-SUM($C6:G6)+1)
H7=IF(SUM($C7:G7)+7<$B7-$A7,7,$B7-$A7-SUM($C7:G7)+1)
H8=IF(SUM($C8:G8)+7<$B8-$A8,7,$B8-$A8-SUM($C8:G8)+1)
H9=IF(SUM($C9:G9)+7<$B9-$A9,7,$B9-$A9-SUM($C9:G9)+1)
H10=IF(SUM($C10:G10)+7<$B10-$A10,7,$B10-$A10-SUM($C10:G10)+1)
H11=IF(SUM($C11:G11)+7<$B11-$A11,7,$B11-$A11-SUM($C11:G11)+1)
H12=IF(SUM($C12:G12)+7<$B12-$A12,7,$B12-$A12-SUM($C12:G12)+1)
H13=IF(SUM($C13:G13)+7<$B13-$A13,7,$B13-$A13-SUM($C13:G13)+1)
A3=DATE(YEAR(A2),MONTH(A2)+1,1)
A4=DATE(YEAR(A3),MONTH(A3)+1,1)
A5=DATE(YEAR(A4),MONTH(A4)+1,1)
A6=DATE(YEAR(A5),MONTH(A5)+1,1)
A7=DATE(YEAR(A6),MONTH(A6)+1,1)
A8=DATE(YEAR(A7),MONTH(A7)+1,1)
A9=DATE(YEAR(A8),MONTH(A8)+1,1)
A10=DATE(YEAR(A9),MONTH(A9)+1,1)
A11=DATE(YEAR(A10),MONTH(A10)+1,1)
A12=DATE(YEAR(A11),MONTH(A11)+1,1)
A13=DATE(YEAR(A12),MONTH(A12)+1,1)
 

drsarao

Well-known Member
Joined
Sep 9, 2009
Messages
1,146
Office Version
  1. 2007
Platform
  1. Windows
Sorry. My mistake.
The formula should have<= instead of only<.
Code:
=IF(SUM($C2:C2)+7<[RANGE=cls:xl2bb-100][XR][XH=cs:9]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][XH]H[/XH][/XR][XR][XH]1[/XH][XD=h:l]Start[/XD][XD=h:l]End[/XD][XD=h:l]Wk1[/XD][XD=h:l]Wk2[/XD][XD=h:l]Wk3[/XD][XD=h:l]Wk4[/XD][XD=h:l]Wk5[/XD][XD=h:l]Wk6[/XD][/XR][XR][XH]2[/XH][XD=h:r]01/01/11[/XD][XD=h:r]31/01/11[/XD][XD=h:r]1[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]2[/XD][/XR][XR][XH]3[/XH][XD=h:r]01/02/11[/XD][XD=h:r]28/02/11[/XD][XD=h:r]5[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]2[/XD][XD=h:r]0[/XD][/XR][XR][XH]4[/XH][XD=h:r]01/03/11[/XD][XD=h:r]31/03/11[/XD][XD=h:r]5[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]5[/XD][XD=h:r]0[/XD][/XR][XR][XH]5[/XH][XD=h:r]01/04/11[/XD][XD=h:r]30/04/11[/XD][XD=h:r]2[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]0[/XD][/XR][XR][XH]6[/XH][XD=h:r]01/05/11[/XD][XD=h:r]31/05/11[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]3[/XD][XD=h:r]0[/XD][/XR][XR][XH]7[/XH][XD=h:r]01/06/11[/XD][XD=h:r]30/06/11[/XD][XD=h:r]4[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]5[/XD][XD=h:r]0[/XD][/XR][XR][XH]8[/XH][XD=h:r]01/07/11[/XD][XD=h:r]31/07/11[/XD][XD=h:r]2[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]1[/XD][/XR][XR][XH]9[/XH][XD=h:r]01/08/11[/XD][XD=h:r]31/08/11[/XD][XD=h:r]6[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]4[/XD][XD=h:r]0[/XD][/XR][XR][XH]10[/XH][XD=h:r]01/09/11[/XD][XD=h:r]30/09/11[/XD][XD=h:r]3[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]6[/XD][XD=h:r]0[/XD][/XR][XR][XH]11[/XH][XD=h:r]01/10/11[/XD][XD=h:r]31/10/11[/XD][XD=h:r]1[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]2[/XD][/XR][XR][XH]12[/XH][XD=h:r]01/11/11[/XD][XD=h:r]30/11/11[/XD][XD=h:r]5[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]4[/XD][XD=h:r]0[/XD][/XR][XR][XH]13[/XH][XD=h:r]01/12/11[/XD][XD=h:r]31/12/11[/XD][XD=h:r]3[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]7[/XD][XD=h:r]0[/XD][/XR][XR][XH=cs:9][RANGE][XR][XD][COLOR=Red]=[/COLOR][/B]$B2-$A2,7,$B2-$A2-SUM($C2:C2)+1)
Corrected sheet:
Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]Excel 2003
Cell Formulas
RangeFormula
B2=DATE(YEAR(A2),MONTH(A2)+1,1)-1
B3=DATE(YEAR(A3),MONTH(A3)+1,1)-1
C2=8-WEEKDAY(A2)
C3=8-WEEKDAY(A3)
D2=IF(SUM($C2:C2)+7<=$B2-$A2,7,$B2-$A2-SUM($C2:C2)+1)
D3=IF(SUM($C3:C3)+7<=$B3-$A3,7,$B3-$A3-SUM($C3:C3)+1)
E2=IF(SUM($C2:D2)+7<=$B2-$A2,7,$B2-$A2-SUM($C2:D2)+1)
E3=IF(SUM($C3:D3)+7<=$B3-$A3,7,$B3-$A3-SUM($C3:D3)+1)
F2=IF(SUM($C2:E2)+7<=$B2-$A2,7,$B2-$A2-SUM($C2:E2)+1)
F3=IF(SUM($C3:E3)+7<=$B3-$A3,7,$B3-$A3-SUM($C3:E3)+1)
G2=IF(SUM($C2:F2)+7<=$B2-$A2,7,$B2-$A2-SUM($C2:F2)+1)
G3=IF(SUM($C3:F3)+7<=$B3-$A3,7,$B3-$A3-SUM($C3:F3)+1)
H2=IF(SUM($C2:G2)+7<=$B2-$A2,7,$B2-$A2-SUM($C2:G2)+1)
H3=IF(SUM($C3:G3)+7<=$B3-$A3,7,$B3-$A3-SUM($C3:G3)+1)
A3=DATE(YEAR(A2),MONTH(A2)+1,1)
 

Forum statistics

Threads
1,141,849
Messages
5,708,973
Members
421,602
Latest member
jkpce1880

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top