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,

2 comments:

  1. I want specific day such as Monday in liu of weekday. How can I do this? Please help!

    ReplyDelete
  2. What do I change is I wanted, not only a specific day, but also to constrain the number of rows that I would want it do display? For example, if i wanted all the Tuesdays between 1/1/2017 and 1/31/2017 to only populate 4 or 5 rows?

    I hope to hear from you soon. Thank you for the help!

    ~Dan (dlubinsky@pittsburghfoodbank.org)

    ReplyDelete