Sharepoint date range formula

Question

Hi, I am creating a holiday request site and need to find the best way to update users holiday entitlements when the date range includes a bank holiday. Currently when users create a new request with a [START TIME] of say 29th Aug and [End TIME] of 4 September the total number of holidays will be incorrect as it will also include the Aug bank holiday. The column creates a total number of days to be deducted from the users holiday entitlements. I want a formula to check the date range and exclude any bank holidays from the total. Any help will be much appreciated. Damo

Answer 1

Unfortunately SharePoint's calculated column functionality isn't particularly advanced. However have a look at this Pentalogic blog post Working Days, Weekdays and Holidays in SharePoint Calculated Columns which might give you some tips.

If you find you have to go down the route of custom code, have a look at the SO question How can I set the default value in a SharePoint list field, based on the value in another field? This shows how you can use JavaScript (specifically jQuery) to update a SharePoint field with a more advanced calculation.