How to return the date of a specific day of the month?

Hailee

We have a calendar that has tasks that occur every Sunday of the month. We would like to be able to set the first day of the month and have each start day return the 1st Sunday of the month, the 2nd Sunday of the month, etc. And we would also like to be able to edit this formula for each day of the week. Not sure where to start, so any help is appreciated.

Paul Newcome

How are you wanting to structure this? Would all of the dates be listed down a single column, or are you wanting each date in a different column with the month and day of week on each row?

Hailee

The dates would be in a single column (Start Date), and we would have one row at the top that would be the date the formula is based off of. This is our template, and the Tasks/Roles never change.

Paul Newcome

You will need a helper column (called "Number" in this example) that has the numbers 1 - 5 the way it shows in the snippet at the bottom of this comment. You can repeat the numbers as many times as needed, and I do suggest going down through 5. I am also assuming that you will have somewhere to enter a number that represents the day of the week (Sunday = 1, Monday = 2, etc.). That can be seen in the snippet as well.

Then the formula that would go in the Start Date column on the second row and dragfilled down would be:

=IF(MONTH(DATE(YEAR([Start Date]$1), MONTH([Start Date]$1), 1) + (IF(WEEKDAY(DATE(YEAR([Start Date]$1), MONTH([Start Date]$1), 1)) <= [Weekday Number]$1, [Weekday Number]$1, [Weekday Number]$1 + 7) - WEEKDAY(DATE(YEAR([Start Date]$1), MONTH([Start Date]$1), 1))) + (7 * (Number@row - 1))) = MONTH([Start Date]$1), DATE(YEAR([Start Date]$1), MONTH([Start Date]$1), 1) + (IF(WEEKDAY(DATE(YEAR([Start Date]$1), MONTH([Start Date]$1), 1)) <= [Weekday Number]$1, [Weekday Number]$1, [Weekday Number]$1 + 7) - WEEKDAY(DATE(YEAR([Start Date]$1), MONTH([Start Date]$1), 1))) + (7 * (Number@row - 1)))