Using Excel to Calculate the Sundays for Each month.

[Alexander_Ewing]

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.
Very much-appreciated in advance.
Alex

 

[dr_shivan]

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

 

[Alexander_Ewing]

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

 

[mikebecker]

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.

Book3
	A	B	C	D
6	Sunday,January16,2005
7	Sunday,January23,2005
8	Sunday,January30,2005
9	Sunday,February06,2005
10	Sunday,February13,2005
11	Sunday,February20,2005
12	Sunday,February27,2005
13	Sunday,March06,2005
14	Sunday,March13,2005
15	Sunday,March20,2005
16	Sunday,March27,2005
17	Sunday,April03,2005
Sheet1

Mike

 

[Alexander_Ewing]

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

 

[SydneyGeek]

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

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

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

 

[Legacy 12630]

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.

 

[Alexander_Ewing]

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