+ Reply to Thread
Results 1 to 17 of 17

Adding Days to a Date including/counting Start date also, but Excluding Weekends+Holidays

  1. #1
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Question Adding Days to a Date including/counting Start date also, but Excluding Weekends+Holidays

    Hi,

    Given a date, how can I add a number of days to it including/counting given date as well, but exclude weekends (Fri,Sat) and Holidays? e.g. given Aug 26, 2020 (Wednesday) and adding 4 days will result in Aug 31, 2020 (Monday).

    Plus: IF Holiday(s) fall on weekend then shift number of holidays those fell on weekends.

    e.g. given Aug 26, 2020 (Wednesday) and adding 4 days will result in Aug 31, 2020 (Monday). IF Aug 29, 2020 (Saturday) is holiday, then the result will be Oct 01, 2020 (Tuesday) rather than Aug 31, 2020 (Monday).

    Regards,
    Last edited by qadeerume; 08-25-2020 at 12:34 AM. Reason: Rephrased the title for better understanding

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Adding Days to a Date but Excluding Weekends+Holidays

    Try the WORKDAY.INTL function

  3. #3
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Adding Days to a Date but Excluding Weekends+Holidays

    @Pepe Le Mokko, thanks, but how to handle second part of the question?

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Adding Days to a Date but Excluding Weekends+Holidays

    Post a sample sheet ( see yellow banner) with some examples, desired results, etc.. ( An IF function using WORKDAY.. should do the trick)

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Adding Days to a Date but Excluding Weekends+Holidays

    With F1:F2 is holiday range
    A1 is start date
    "0000110" where 1 is weekend (start from Monday at 1st digit, Friday & Sat is weekend)
    Try:

    =WORKDAY.INTL(A1,4,"0000110",F1:F2)
    Quang PT

  6. #6
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Adding Days to a Date but Excluding Weekends+Holidays

    Sample workbook is attached. Thanks!
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Adding Days to a Date but Excluding Weekends+Holidays

    @bebo021999, Please check the attached file, in Post # 6,

    it should also include the start date,

  8. #8
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Adding Days to a Date but Excluding Weekends+Holidays

    So, weekend is Fri, Sat and Sun?
    And, including start date, mean the day before start date also taken into account.

    =WORKDAY.INTL(A2,C2-1,"0000111",$F$2:$F$6)

  9. #9
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Adding Days to a Date but Excluding Weekends+Holidays

    No, weekend is only two days, Fri & Sat,
    including start date means like today is 24-Aug, if I want to add 2 days to 24-Aug, the output should be = 25-Aug-20

  10. #10
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Adding Days to a Date including/counting Start date also, but Excluding Weekends+Holid

    So:

    =WORKDAY.INTL(A2,C2,"0000110",$F$2:$F$6)+1

    Does it works?

  11. #11
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Adding Days to a Date including/counting Start date also, but Excluding Weekends+Holid

    No! it does not

  12. #12
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Adding Days to a Date including/counting Start date also, but Excluding Weekends+Holid

    any luck for this solution?

  13. #13
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Adding Days to a Date including/counting Start date also, but Excluding Weekends+Holid

    I don't understand why the expected finish in cell D2 is Aug. 26th. If the start date, Aug 20th, is counted then Aug 21st and 22nd are not counted, it would seem that the other 3 days would be Aug. 23rd, 24th and 25th. So it would seem that the finish date should be Aug. 25th.
    If so the formula could read: =WORKDAY.INTL(A2,C2-1,7,$F$2:$F$3) which makes both B2 and B4 correct.
    If I understand correctly then if a holiday falls on either a Friday or Saturday then it is observed on either Thursday or Sunday.
    Therefore put 8/27/2020 or 8/30/2020 in cell F2.
    Let us know if you have any questions.
    Last edited by JeteMc; 08-31-2020 at 06:16 PM. Reason: corrected formula
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  14. #14
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Adding Days to a Date including/counting Start date also, but Excluding Weekends+Holid

    @JeteMc,

    regardless where holiday falls either weekends or during working days, the end result should add those number of holidays to actual working days..
    with your formula, as you stated B2 and B4 are correct because the holiday is falling during work days (formula is excluding weekends+holidays).

    But B3 isn't, because holiday is actually fall in weekend for B3 itself, so how to overcome this scenario? (add number of holidays those fell in weekends)?

    Attachment 694952


    i hope its clear to understand now.

    thanks for helping.

  15. #15
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Adding Days to a Date including/counting Start date also, but Excluding Weekends+Holid

    Please try the following:
    1. Paste the following formula into cell G2 and then drag the fill handle down to cell G3:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    2. Modify the formula in B2 and down to read:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Note that selecting the attachment in post #14 results in the following message: "Invalid Attachment specified. If you followed a valid link, please notify the administrator" so I do not know what it shows.
    Let us know if you have any questions.

  16. #16
    Registered User
    Join Date
    12-26-2016
    Location
    SA, KSA
    MS-Off Ver
    2010, 2013, 2016, 2019
    Posts
    44

    Re: Adding Days to a Date including/counting Start date also, but Excluding Weekends+Holid

    @JeteMc thanks alot dear for your usual support. Screen shot was attached in Post # 14. dont know why its giving an error.

    would you please enlighten more on the first formula logic, i'm not able to figure it out.

  17. #17
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,430

    Re: Adding Days to a Date including/counting Start date also, but Excluding Weekends+Holid

    My attempt to explain: =IF(WEEKDAY(F2,1)<=5,F2,WORKDAY.INTL(F2,-1,7))
    IF the weekday of the date in cell F2 is Sunday through Thursday (<=5) then keep the date displayed in F2, else use the first workday that is one day earlier (F2,-1) than the date in cell F2.
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 10
    Last Post: 02-29-2024, 08:55 AM
  2. Replies: 0
    Last Post: 10-31-2014, 10:32 AM
  3. Replies: 4
    Last Post: 05-19-2014, 04:28 PM
  4. Add X Days To Dates, Excluding Weekends and Holidays
    By sianjialin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-07-2014, 11:46 PM
  5. Calculate number of days excluding holidays and weekends
    By david1987 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-05-2012, 08:17 AM
  6. Calculate number of days, excluding weekends and holidays
    By cloyd in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-26-2012, 04:37 PM
  7. [SOLVED] Formula for adding days to a date excluding weekends and holidays?
    By Jake via OfficeKB.com in forum Excel General
    Replies: 2
    Last Post: 01-25-2006, 04:03 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1