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.

WORKDAYS - Future date then moved forward to the next working days

Discussion in 'Ask an Excel Question' started by Stephanie Rance, Feb 13, 2018.

  1. Stephanie Rance

    Stephanie Rance New Member

    Messages:
    2
    I am trying to create a formula that takes todays date, adds a specific term (in months) then if the date in the future is a weekend it then moves it forward to the next working day.
    E.g. Today 13/02/2018 add 1 months is 13/04/2019 which is a Saturday so I would want the formula to then shift the date to 15/04/2019.

    Any help is greatly appreciated!
  2. AliGW

    AliGW Member

    Messages:
    84
    That's two months ...

    Neither 13/03 nor 13/04 is a Saturday this year!

    One or two months? Take your pick:

    =IF(WEEKDAY(EDATE(A1,1),2)=6,EDATE(A1,1)+2,IF(WEEKDAY(EDATE(A1,1),2)=7,EDATE(A1,1)+1,EDATE(A1,1)))

    =IF(WEEKDAY(EDATE(A1,2),2)=6,EDATE(A1,2)+2,IF(WEEKDAY(EDATE(A1,2),2)=7,EDATE(A1,2)+1,EDATE(A1,2)))

    If you want a year and two months, let us know - your request is contradictory!!!

    For 14 months:

    =IF(WEEKDAY(EDATE(A1,14),2)=6,EDATE(A1,14)+2,IF(WEEKDAY(EDATE(A1,14),2)=7,EDATE(A1,14)+1,EDATE(A1,14)))
  3. Stephanie Rance

    Stephanie Rance New Member

    Messages:
    2
    Ah sorry that was meant to say 14, not 1! Thank you this works brilliantly.
  4. bosco_yip

    bosco_yip Excel Ninja

    Messages:
    1,468
    Or.......for Date + 14 months :

    =EDATE(A1,14)+TEXT(2-MOD(EDATE(A1,14),7),"0;\0;0;")

    Regards
    Bosco
    Last edited: Feb 13, 2018
  5. AliGW

    AliGW Member

    Messages:
    84
    You're welcome! :)

Share This Page