Results 1 to 5 of 5

Thread: Return a date based on it being Monday, Thursday or Friday
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Sep 2014
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Return a date based on it being Monday, Thursday or Friday

    Hi,

    I have a spreadsheet that gets dates manually inputted into it.
    What I need is a formula to return a date 7 days on from the manual input but has to be a Monday, Thursday or Friday.
    I.E. Manual input is 1/10/19 or 2/10/19 I would want the returned date to be 10/10/19. Manual input 3/10/19 would return the date 10/10/19, manual input 4/10/19 returned date would be 11/10/19.

    Hope this makes sense.
    I have tried using a helper column with the function WEEKDAY in it but I am having some brain freeze.

    Thanks in advance for any help.

  2. #2
    Board Regular
    Join Date
    Mar 2016
    Posts
    234
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Return a date based on it being Monday, Thursday or Friday

    Here's my formula. There may be better ones.
    ABCD
    1Tue10/1/201910/10/2019Thu
    2Wed10/2/201910/10/2019Thu
    3Thu10/3/201910/10/2019Thu
    4Fri10/4/201910/11/2019Fri
    5Sat10/5/201910/14/2019Mon
    6Sun10/6/201910/14/2019Mon
    7Mon10/7/201910/14/2019Mon
    8Tue10/8/201910/17/2019Thu
    9Wed10/9/201910/17/2019Thu
    10Thu10/10/201910/17/2019Thu
    11Fri10/11/201910/18/2019Fri
    12Sat10/12/201910/21/2019Mon
    13Sun10/13/201910/21/2019Mon
    14Mon10/14/201910/21/2019Mon
    15Tue10/15/201910/24/2019Thu
    16Wed10/16/201910/24/2019Thu
    17Thu10/17/201910/24/2019Thu

    Sheet1



    Worksheet Formulas
    CellFormula
    C1=IF(OR(WEEKDAY(B1+7,14)<3,WEEKDAY(B1+7,14)=5),B1+7,IF(WEEKDAY(B1+7,14)>5,B1+7+3-WEEKDAY(B1+7,12),B1+7+3-WEEKDAY(B1+7,16)))

    Hobby: VBA and some .Net
    Career: the world of patent and trademark protection

    See
    Where do I paste the code that I find on the internet to implement any code I provide

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  3. #3
    Board Regular
    Join Date
    Apr 2016
    Location
    Vietnam
    Posts
    139
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a date based on it being Monday, Thursday or Friday

    Or try this:

    =WORKDAY.INTL(A1-1,1,"0110011")+7

  4. #4
    Board Regular
    Join Date
    Apr 2016
    Location
    Vietnam
    Posts
    139
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a date based on it being Monday, Thursday or Friday

    A little shorter:

    =WORKDAY.INTL(A1+6,1,"0110011")



    Last edited by Phuoc; Oct 1st, 2019 at 08:42 PM.

  5. #5
    Board Regular
    Join Date
    Sep 2014
    Posts
    59
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a date based on it being Monday, Thursday or Friday

    Thanks for your replies. Both answers worked thank you very much

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
  •