Can I calculate 30 calendar days from a given date, excluding holidays that I specify?

Anonymous
2010-06-04T20:34:51+00:00

I'm looking for something similar to the WORKDAY function, except I want it to return dates that include weekends (but not holidays that I specify).

Thanks for your advice!

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

44 answers

Sort by: Newest
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Anonymous
    2010-06-05T22:18:18+00:00

    I'm still testing this, but it just seems to be too short to be a solution...but, so far it seems to work:

    With

    A1: a start date....eg 05-June-2010

    B1: Num of non-holiday days to find in the future...eg 30

    E1:E3 contains a list of holiday dates

    30-JUN-2010

    06-JUL-2010

    07-JUL-2010

    Regular formula (No C+S+E)

    C1: =A1+B1+COUNTIF(E1:E3,"<="&(A1+B1+COUNT(E1:E3)))

    Am I missing something?

    Ron Coderre

    Microsoft MVP - Excel (2006 - 2010)

    P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-06-05T22:03:01+00:00

    With

    A1: a start date....eg 05-June-2010

    B1: Num of non-holiday days to find in the future...eg 30

    E1:E3 contains a list of holiday dates

    30-JUN-2010

    06-JUL-2010

    07-JUL-2010

    Not pretty, but this array formula, committed with CTRL+SHIFT+ENTER (instead of just ENTER) seems to work:

    C1: =SMALL(IF(ISNA(MATCH(ROW(INDIRECT((A1+1)&":"&(A1+B1+COUNT(E1:E3)))),E1:E3,0)),ROW(INDIRECT((A1+1)&":"&(A1+B1+COUNT(E1:E3)))),10^10),B1)

    Format cell C1 as a date.

    In the above example, the formula returns: 08-JUL-2010 

    Does that help?

    Ron Coderre

    Microsoft MVP - Excel (2006 - 2010)

    P.S. If any post answers your question, please mark it as the Answer (so it won't keep showing as an open item.)

    Was this answer helpful?

    0 comments
  4. Anonymous
    2010-06-05T21:58:08+00:00

    I misread your question. Sorry about that. To exclude only holiday dates, use the following array formula:

    =B1-A1-SUM((G1:G10>=A1)*(G1:G10<=B1))

    where A1 is the start date, B1 is the end date, and G1:G10 contains the holidays. Since this is an array formula, you must press CTRL SHIFT ENTER rather than just ENTER. If you do this properly, Excel will dislay the formula enclosed in curly braces { }. The formula will not work correctly if you do not use CTRL SHIFT ENTER.

    See www.cpearson.com/Excel/ArrayFormulas.aspx for more information about array formulas.

    Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com

    Was this answer helpful?

    0 comments
  5. Anonymous
    2010-06-05T21:35:54+00:00

    Entering 30 as the days to add:

    =A1+30+SUMPRODUCT(--ISNUMBER(MATCH(ROW(INDIRECT(A1&":"&A1+30)),D1:D3)))

    Here the holidays are in D1:D3.  Using the dates suggested by Bernd the above formula returns I get 7/11/2010 instead of 7/8/2010?

    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

    Was this answer helpful?

    0 comments