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)
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