## Thursday, July 25, 2013

### How to get list of Sundays within a range of two dates

Question:

( by
Paul Winn )

I have a simple church schedule workbook with sheets for every month.  The 3 column headings are Date, Sound, Video.  Under the last 2 columns there will be just names of people assigned for either sound or video.  Since church is on every Sunday in a month, I would like to calculate the specific dates of the the 4 or 5 Sundays (and place them in 4 or 5 cells/rows beneath the "date" heading) for the corresponding month an year.  Maybe it is easier to just look at a calendar and copy the values but if there is a better way I'd love to know.
Thanks

Solution:

For example, if you have Start Date in Cell B1 And End Date in Cell B2

Have a look at the following animated screenshot of my Spreadsheet:

Insert the following formula in Cell A4:

=query(arrayformula(if(mod(weekday(if(B1+row(A:A)-1<=B2;B1+row(A:A)-1;""));7)=1;B1+row(A:A)-1;""));"select * where Col1 is not null";0)

the above formula will give you the list of dates that are all sundays between the dates inserted by you in Cells B1 and B2.

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html

Thanks,