How to identify the date of the next consecutive Saturday when working with a range of dates.

[jnaeve]

Hello,
I'm building a workbook to help me find the date of the next available Saturday within a range of dates.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start Date[/TD]
[TD]End Date[/TD]
[TD]Next Saturday[/TD]
[/TR]
[TR]
[TD]14-Oct[/TD]
[TD]23-Oct[/TD]
[TD]26-Oct[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What sort of formula can I use to determine the date of the following Saturday after the "End Date"?
This is on a Mac.
Thanks!

 

[Rick Rothstein]

If your End Date will never be a Saturday or, if it is and you want that date as the "next" Saturday, you can use this formula...

=B1-WEEKDAY(B1)+7

If your End Date could be a Saturday and, for that condition, you wanted the following Saturday, then you could use this formula...

=IF(WEEKDAY(B1)=7, B1+7,B1-WEEKDAY(B1)+7)

 

[jnaeve]

Thank you Rick, the second formula actually hit the nail on the head. The first one resulted in the previous Saturday in some instances, but all my scenarios worked well for me with the second one.

The actual formula that I'm using is:
=IF(WEEKDAY(WORKDAY([Start Date],[Duration],[Holidays]))=7,(WORKDAY([Start Date],[Duration],[Holidays]))+7,(WORKDAY([Start Date],[Duration],[Holidays]))-WEEKDAY(WORKDAY([Start Date],[Duration],[Holidays]))+7) == In essence, we have a task that starts on [Start Date] that has a [Duration] and using the [Holidays] range, we remove dates that we can't work and with your formula, we're able to find the date of the following Saturday, after completing the task, that is the most likely target date for our next action.

Thanks again Rick! Very much appreciated!!

If your End Date will never be a Saturday or, if it is and you want that date as the "next" Saturday, you can use this formula...

=B1-WEEKDAY(B1)+7

If your End Date could be a Saturday and, for that condition, you wanted the following Saturday, then you could use this formula...

=IF(WEEKDAY(B1)=7, B1+7,B1-WEEKDAY(B1)+7)