Using Excel to Calculate the Sundays for Each month.

Alexander_Ewing

New Member
Joined
Jan 18, 2005
Messages
9
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.
:confused:
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

dr_shivan

Active Member
Joined
Jan 3, 2005
Messages
298
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

New Member
Joined
Jan 18, 2005
Messages
9
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

Board Regular
Joined
Mar 28, 2004
Messages
227
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
 

Alexander_Ewing

New Member
Joined
Jan 18, 2005
Messages
9

ADVERTISEMENT

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

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
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
 
L

Legacy 12630

Guest
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

New Member
Joined
Jan 18, 2005
Messages
9
Thank you to everybody for all your help, especially to Ponsonby that does what I was looking to do Thanks.
Alex
 

Forum statistics

Threads
1,147,675
Messages
5,742,546
Members
423,737
Latest member
tom_xls

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