number of days in a week that correspond to a month

M

MDuff

Well-known Member
Joined
Dec 29, 2002
Messages
529
Office Version
  1. 365
Platform
  1. Windows
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

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Upvote 0
thanks I will check them out....
Any Ideas on the original question???
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
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)
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Summing several columns based on criteria
Replies
7
Views
206
dreid1011
dreid1011
J
  • Question Question
How to create monthly report for activities based on created date, closed date, and due date, for past year?
Replies
1
Views
458
josevaldesv1
J
W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
J
  • Question Question
Using CountIf to find total number based on 2 different cells?
Replies
1
Views
142
Asbestos_Jen
Asbestos_Jen
Sweedler
  • Solved
Time between two dates
Replies
8
Views
521
Joe4
Joe4

Forum statistics

Threads
1,224,616
Messages
6,179,911
Members
452,949
Latest member
beartooth91

Share this page

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
Back
Top