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

E.Z. picture E.Z. · Jun 6, 2012 · Viewed 71.5k times · Source

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)

Answer

barry houdini picture barry houdini · Jun 6, 2012

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