Mark welcome to the forums! You're on the right track, and yes the Excel sheet of holidays will be helpful. Read this thread by Catherine Rivier : Re: Include Date value of Null when prior business day date is selected
That should get you going.
Welcome to the forum, Mark!
I believe you get more exact answers by attaching a packaged workbook with the question, because (1) it ensures that data and formulas are are as you define them and (2) makes it easy for helpers to play their way to a solution.
The second best solution would be to share a Excel file with the date periods and bank holidays.
Ps. I find your formula interesting, because it is a variation of a formula I admire, namely Mary Solbrig's in The specified item was not found.. The variation is that line 1 has 'sunday' and line 3 has 5 as parameters in yours. I mention this so (1) it can benefit random readers and (2) as a note for my own benefit.
DATEDIFF('week',[StartDate],[EndDate])*5 - MIN(DatePart('weekday',[StartDate]),6) + MIN(DatePart('weekday',[EndDate]),6)
Thank you for your responses so far, they gave me a couple of ideas.
How about this
I've created a parameter with my list of UK holidays (called Holidays) I then created the calculated field below
if [Parameters].[Holidays] <= today() AND [Parameters].[Holidays] >= [TARGET DUE DATE] THEN '1'
Am I right in thinking that this should look to see if one of my holiday dates (in the parameter list) falls between todays date and the target due date, if it does then it will assign a value of 1.
I have then edited my original formula (the one in my first post) so that the working days between my two dates were calculated and then the result of the formula above is subtracted, hopefully leaving me with working days less any holidays that occur in that period. Does this make sense?
- int ([Holidays])
Here is a more advanced but hopefully relative simple solution that can help you.
The first step was to create data. In my case, I created* an Excel file with two sheets, one with day numbers for period of interest, and one with Easter date to use as base for Easter related holidays.
The second step was to connect to the file with custom SQL via the legacy connector.
SELECT dateadd('d', ['Day No$'].[Day No] - 1, <Parameters.Start Date>) AS [Calendar Date] , ['Easter Date$'].[Easter Year] , ['Easter Date$'].[Easter Date] FROM ['Day No$'], ['Easter Date$'] WHERE YEAR(dateadd('d', ['Day No$'].[Day No] - 1, <Parameters.Start Date>)) = ['Easter Date$'].[Easter Year] AND dateadd('d', ['Day No$'].[Day No] - 1, <Parameters.Start Date>) <= <Parameters.Target Due Date>
The third step was to create a formula for workday. It is a row level calculation and therefore easy to use in a view.
// Weekend IF DATEPART('weekday', [Calendar Date]) = 7 THEN 0 ELSEIF DATEPART('weekday', [Calendar Date]) = 1 THEN 0 // Easter related holidays ELSEIF DATEDIFF('day', [Calendar Date], [Easter Date]) = 2 THEN 0 ELSEIF DATEDIFF('day', [Calendar Date], [Easter Date]) = -1 THEN 0 // Fixed holidays ELSEIF MONTH([Calendar Date]) = 1 AND DAY([Calendar Date]) = 1 THEN 0 ELSEIF MONTH([Calendar Date]) = 12 AND DAY([Calendar Date]) = 25 THEN 0 ELSEIF MONTH([Calendar Date]) = 12 AND DAY([Calendar Date]) = 26 THEN 0 // workday ELSE 1 END
Please notice that I have not included UK bank holidays. I hope you will do that part :-) and share it in this thread.
I added a screenshot to help curious readers to decide if they want to know more without opening the attached workbook.
* I didn't create it, but used (with minor changes) Johan Calendar #2.xlsm in The Calendar Workbook Collection