# Using Excel to Calculate the Sundays for Each month.

#### Alexander_Ewing

##### New Member
I am creating a sheet that will calculate the Sunday of every month after you enter the first one for April. My problem is that I have 5 Rows per month and require 2 Rows between the months. I can use formulas that calculate 7 days from the first date I give but cant manage the check at the bottom that will jump to the cell 3 rows down if a new month starts or enter the value for that month. April=(A6>10) May=(A13>17) June=(A20>24) etc I am hoping that if there are only 4 Weeks for that month that the bottom row will show the month.

I have tried several means of doing this including the IF and DATE statements but to no avail, hope that somebody can help.
Alex

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
Hi there

You could try weekday(19-01-05) will return a 4 for wednesday
a sunday is 1
so with this maybe you can work something out?

I'm not exactly sure of what you want exactly..

Hope the above helps

Thanks for the suggestions haven’t used that function but will give it a go when have more time.

I am hoping to design the sheet so that after you enter the first Sunday in April it will fill the other spaces bellow it with either the date in that month or the month.

Ex. I enter 4 April 2004 in cell A6; A7 should show 11 April 2004; A8 should show 18 April 2004; A9 Should show 25 April 2004; Cell A10 will show April because it is the last Sunday of the month and in Cell A13 should be 2 May 2004 and so on until I go to the end of March.

Thanks Again

Alexander_Ewing said:
Thanks for the suggestions haven’t used that function but will give it a go when have more time.

I am hoping to design the sheet so that after you enter the first Sunday in April it will fill the other spaces bellow it with either the date in that month or the month.

Ex. I enter 4 April 2004 in cell A6; A7 should show 11 April 2004; A8 should show 18 April 2004; A9 Should show 25 April 2004; Cell A10 will show April because it is the last Sunday of the month and in Cell A13 should be 2 May 2004 and so on until I go to the end of March.

Thanks Again

Alexander,

You could simply enter the first date and in the next cell add 7 to the cell above just like this example.

Mike

Thanks Mike,
I tried that but I require 2 rows space between each month and I used IF statements at the start and End of each month to check, The IF statements wouldn’t work for each month start from year to year.

Alex

Can't think of a formula-based approach but here's a code solution.
Assumptions:
1. Dates are in column A
2. First date in sequence is in A2
Code:
``````Sub Insert2()
Range("A65536").End(xlUp).Select
Do Until ActiveCell.Row = 2
If Month(ActiveCell.Value) > Month(ActiveCell.Offset(-1, 0).Value) Then
'month boundary
ActiveCell.Range("A1:A2").EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
Else
'within month
ActiveCell.Offset(-1, 0).Select
End If
Loop
End Sub``````
EDIT This version writes the month name and year below each group...
Code:
``````Sub Insert2_WithMonth()
Range("A65536").End(xlUp).Select
ActiveCell.Offset(1, 0).Value = "'" & Format(ActiveCell.Value, "mmm yyyy")
Do Until ActiveCell.Row = 2
If Month(ActiveCell.Value) > Month(ActiveCell.Offset(-1, 0).Value) Then
'month boundary
ActiveCell.Range("A1:A2").EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
ActiveCell.Offset(1, 0).Value = "'" & Format(ActiveCell.Value, "mmm yyyy")
Else
'within month
ActiveCell.Offset(-1, 0).Select
End If
Loop
End Sub``````

Insert code into a standard module, select the worksheet, and run the macro (Alt + F8, double-click the macro name)

Denis

Put the following in A7 and fill down :-

=IF((MOD(ROW(),7)=4)+(MOD(ROW(),7)=5),"",IF((MOD(ROW(),7)=6)*ISNONTEXT(A4),A4+7,IF((MOD(ROW(),7)=6)*ISTEXT(A4),A3+7,IF((MOD(ROW(),7)=3)*(MONTH(A6+7)<>MONTH(A6)),TEXT(A6,"mmmm"),A6+7))))

What data do you have in A3:A6?
Might need to revise the formula depending upon what is in A3:A6.

Thank you to everybody for all your help, especially to Ponsonby that does what I was looking to do Thanks.
Alex

Replies
10
Views
550
Replies
0
Views
404
Replies
1
Views
229
Replies
16
Views
2K
Replies
1
Views
252

1,221,409
Messages
6,159,709
Members
451,586
Latest member
khaledshahin

### 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.

### Which adblocker are you using?

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

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