Search code examples
exceldateopenoffice-calcdayofweekweekday

How to add number of days to a date, consider only business days (i.e. ignore weekends)?

I'm trying to create a formula to calculate the n-th Business Date (only Monday to Friday are business days). For simplicity's sake, holidays are not important; only weekends should be ignored.

For example:

   +------------------------------------------------------------------
   |   A                B                     C
   +------------------------------------------------------------------
1  |  Starting Date    Business-Day Number   Business Date
2  |  06-Jun-2012      0                     06-Jun-2012
3  |  06-Jun-2012      1                     07-Jun-2012
4  |  06-Jun-2012      2                     08-Jun-2012
5  |  06-Jun-2012      3                     11-Jun-2012    <-- June 9th (Sat) and 10th (Sun) are skipped
6  |  06-Jun-2012      4                     12-Jun-2012
...

The formula would be used to fill Column C above. The only solution I could come up with involves vlookup on a table of working days, which I found a bit cumbersome.

Any ideas how I could go for it in a single formula?

(it can be on Excel or OpenOffice-Calc)

Solution

  • In Excel WORKDAY function does this, e.g. this formula in C2

    =WORKDAY(A2,B2)

    you can also add a holiday range, for example with holidays listed in H2:H10 make that

    =WORKDAY(A2,B2,H$2:H$10)

    WORKDAY is a built-in function in Excel 2007 and later versions - in earlier versions you need to enable Analysis ToolPak addin