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