NetWorkingDays function with long list of Holidays

[Mini_Elaine]

Hi All,

I am trying to calculate set up period for several items and would like to measure way back to 10 years ago. I have to take account of working days and bank holidays. Each of year we have roughly 8 bank holidays. So if I use networkingdays function, how can I add bank holidays as a list rather than adding one date by date?

My data looks like below:

	Start date	End date
A	30/01/2024	09/05/2024
B	17/10/2023	12/02/2024
C	02/03/2022	14/03/2024
D	01/07/2020	04/06/2023
E	20/09/2020	23/12/2020
F	24/05/2021	09/09/2021

And my bank holidays list looks like:

BH

01/01/2020

10/04/2020

13/04/2020

08/05/2020

25/05/2020

31/08/2020

25/12/2020

28/12/2020

01/01/2021

02/04/2021

05/04/2021

03/05/2021

31/05/2021

30/08/2021

27/12/2021

28/12/2021

03/01/2022

15/04/2022

18/04/2022

02/05/2022

02/06/2022

03/06/2022

29/08/2022

19/09/2022

26/12/2022

27/12/2022

02/01/2023

07/04/2023

10/04/2023

01/05/2023

08/05/2023

29/05/2023

29/08/2023

25/12/2023

26/12/2023

01/01/2024

29/03/2024

01/04/2024

06/05/2024

27/05/2024

26/08/2024

25/12/2024

26/12/2024

I have tried created a master item as: networkingdays(start date, end date, BH) or a variable from script like: 

let vBH=concat(BH,',');

[New table]:

NetWorkDays([start date], [end date], '$(vBH)') AS [duration]

Resident [XX];

None of them are working. Any thoughts?

Many thanks!

[brunobertels · Accepted solution]

Hi 

concact(BH,',') will give you something like this :

29/03/2024,01/04/2024,....,.....

Or in the function networkday the format for holliday awaiting is in this format 

'29/03/2024' , '01/04/2024' 

so try

Férié:
LOAD
   CHR(39)&CONCAT(BH,CHR(39)&','&CHR(39))&CHR(39) as BH
RESIDENT yourtable


;
LET vFeiertage= PEEK('BH',0,'Férié');

[Brian_C]

Hi 

You can create a variable with all the bank holiday dates in a string such as 

'02/01/2023','07/04/2023','10/04/2023','01/05/2023','08/05/2023','29/05/2023','28/08/2023','25/12/2023','26/12/2023',
and then use the variable as the third parameter in the the networkdays function.

Hope this helps.

B

[Mini_Elaine]

Thank you. It is very helpful. I noticed where problem is now. And this works very well.

The last part 

LET vFeiertage= PEEK('BH',0,'Férié');

didn't work but as long as I create variable in master item. It works perfectly!