1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Getting calculated date to display the next working date if result is Saturday and Sunday

Discussion in 'Ask an Excel Question' started by Kevin, Dec 6, 2017.

  1. Kevin

    Kevin New Member

    Messages:
    21
    Hi,

    I have date 13/12/17(T) and I have events to happen at various intervals of days before and days afterwards. T+3 and T+4 in this case would be a Saturday(16/12/17) and Sunday(17/12/17). I am trying to get the results to display the next working day 17/12/17.

    Any suggestions appreciated?

    Thanks

    K
  2. pecoflyer

    pecoflyer Active Member

    Messages:
    119
    Perhaps try the WORKDAY function
    Nightlytic likes this.
  3. Kevin

    Kevin New Member

    Messages:
    21
    Hi, I have tried that but it adds a day regardless of the calculated date. So if T+1 = Thursday 14/12/17, the workday function =Workday(T+1,1) is displaying as 15/12/17 whereas I want it to stay as is. Similarly, if I try to do previous working day, it is deducting a day whether or not it is working day or not.
  4. NARAYANK991

    NARAYANK991 Excel Ninja

    Messages:
    15,924
    Hi ,

    To know whether a date is a weekday or a weekend , try :

    =NETWORKDAYS(date , date)

    If it is a working day , it will return 1 , else it will return 0.

    Hence , based on this , you can use an IF to add either a day or 0 to the WORKDAY function.

    Narayan
    Thomas Kuriakose likes this.
  5. Nightlytic

    Nightlytic Member

    Messages:
    42
    You might want to attach a file and show on it what output you want... The workday formula works as you ask, it's the day you give it, more or less specified number of work days (refer 3rd argument to a list of holiday dates to capture these as non-working days also)
  6. Kevin

    Kevin New Member

    Messages:
    21
    hi,
    Should have uploaded this earlier.

    Thanks for your responses so far. All useful for my education.

    Thanks
    K

    Attached Files:

  7. Chihiro

    Chihiro Excel Ninja

    Messages:
    3,917
    Your issue is you are adding T basis to date.

    No need. Formula should be...
    =WORKDAY($B$1,A4)

    But... why should 4 workdays after 12/13/2017 be 12/18/2017?
    Considering Sat & Sun as weekend. It should be 12/19/2017.

    Or do your workplace observe 1 day weekend?
    If you need to pick custom weekend, use...
    =WORKDAY.INTL($B$1,A4,11) or something similar.
    11 being Sunday only weekend.
    Thomas Kuriakose likes this.
  8. Kevin

    Kevin New Member

    Messages:
    21
    Chichiro, Thank you.

    Thank you to everyone who took the time to respond.
  9. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,344
    Try………

    In B4, copied down :

    =B$1+A4+MID(2100000,MOD(B$1+A4,7)+1,1)

    Regards
    Bosco

    Attached Files:

    Thomas Kuriakose and deciog like this.
  10. Kevin

    Kevin New Member

    Messages:
    21
    Thank you Bosco. Another option :)

Share This Page