Follow these steps to create a custom formula that will calculate a business-days date difference (i.e it removes weekends and public holidays).

1. download the code attached to this post. This is for SQL Server, and would have to be amended for other DBMSs. The code does the following
a. Create a table listing all public holidays by location
b. Insert Australian public holidays for 2011/12. If you need PH for another country or year, you will need to insert them yourself.
c. Create a function to do the calculation
2. Make sure you have created yellowFin's dateLookup table. It can be found here
3. Run the code
4. Append the following code to [yf install]appserverwebappsROOTWEB-INFcustom-functions.xml (just before at the end of the file).

[code]

Days Between(Business Days)

1
Start Date
datetime


2
End Date
datetime


3
Location
text


dbo.fn_DatediffBusinessDays($1, $2, $3)

dbo.fn_DatediffBusinessDays($1, $2, $3)
SQLSERVER
numeric

[/code]

5. Cycle the yellowfin service.
6. Now you can create a calculated field using the predefined formula, as shown below.









Related Posts:

<a href = "http://www.yellowfinbi.com/YFForum-How-to-create-your-own-custom-functions-for-use-in-calculated-fields-?thread=105253
">How to create your own custom functions, for use in calculated fields