Menu
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)
799
Add business days excluding weekends with formula To add days excluding weekends, you can do as below: Select a blank cell and type this formula =WORKDAY(A2,B2), and press Enter key to get result. Tip: In the formula, A2 is the start date, B2 is the days you want to add.
Then, to calculate the number of business days in the specified time period, type the formula =NETWORKDAYS(B3,B4), where B3 is the starting date and B4 is the ending date. In this case, the function tells us the year 2017 will include 260 business days.
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
77
In LibreOffice Calc:
C1=A1+INT(B1/5)*7+MOD(B1,5)+(IF(WEEKDAY(A1,2)+MOD(B1,5)>5,2,0)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
