Results 1 to 3 of 3

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

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

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

    Start Date End Date Next Saturday
    14-Oct 23-Oct 26-Oct

    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!

  2. #2
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

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

    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)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  3. #3
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!!



    Quote Originally Posted by Rick Rothstein View Post
    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)

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •