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: Oldest
Most helpful Newest Oldest
  1. 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
  2. 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
  3. 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
  4. 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

  5. Anonymous
    2010-06-05T22:37:56+00:00

    As far as I understood the question - the Holidays list should not / can not be limmited to within the range of those 30, or what days.

    I compared Ron Rosenfelds last formula with Chip Pearsons suggestion [on his site]:

       http://www.cpearson.com/Excel/BetterWorkday.aspx

    I checked:

    Start date: 02/02/2009

    Added days: 50

    Holidays dates - in range: C18:C379 [coverring holidays for the years 2006-2012]

    The range was build from fictitious Dates - some relevant for that period of time are along that long range.

    Both solutions return the same answer [date]: 28th., Mar. 2009

    Rons Codderre formula returns: 24th., Mar. 2009

    Micky (Microsoft® MVP - Excel)

    Was this answer helpful?

    0 comments