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

Question

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 1

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

Solution 2

In LibreOffice Calc:

C1=A1+INT(B1/5)*7+MOD(B1,5)+(IF(WEEKDAY(A1,2)+MOD(B1,5)>5,2,0))

Comments

[mazaneicha]

It seems to work fine with whole numbers, but what if my duration contains half- and other fractions of a day?