NetWorkDays with List of (summer) holidays

[atoesch]

hi, 

I used the example that I found on this board to calculate net working days:

[HolidaysCA]:
LOAD
Reason,
Date(Nonworkingday) AS Nonworkingday
FROM [lib://AttachedFiles/vHolidays.xlsx]
(ooxml, embedded labels, table is Sheet1);

[tmpConcat]:
LOAD concat(chr(39) & Nonworkingday & chr(39),',') AS DateNWD
RESIDENT [HolidaysCA];

LET vCAHolidays = fieldvalue('DateNWD',1);

I apply the list of holidays here:

NetWorkDays ((Date([uca_arrival_date])), (Date([ca_date])), '$(vCAHolidays)'))

The expression actually substracts the week-ends (when I compare to the result of 

ceil(interval((Date([ca_date]))-(Date([uca_arrival_date])), 'd'))

but does not consider the holiday list in vCAHolidays ...

I verified the date formats  (dd.mm.yyyy in my region, but alos tried dd/mm/yyyy) and the content of the tmpConcat table (OK) and the content of vCAHolidays

"'01.01.2022','01.01.2023','01.07.2021','01.07.2022','01.08.2021','01.08.2022','02.01.2022','02.01.2023','02.07.2021','02.07.2022','02.08.2021','02.08.2022','03.01.2022','03.01.2023','03.07.2021','03.07.2022','03.08.2021','03.08.2022','04.01.2022','04.01.2023','04.07.2021','04.07.2022','04.08.2021','04.08.2022','05.01.2022','05.07.2021','05.07.2022','05.08.2021','05.08.2022','06.07.2021','06.07.2022','06.08.2021','06.08.2022','07.07.2021','07.07.2022','07.08.2021','07.08.2022','08.07.2021'"

which seem ok.

Any ideas?

[anthonyj · Accepted solution]

Hi @atoesch ,

I hadn't seen this concat approach to creating a list of dates. My approach was more complicated so thank you for you showing me an easier way. I tested your code and found that the only difference that allowed it to work was that you included single quotes around the variable. There's doesn't need to be because the single quotes are included in the resolution of the variable.

NetWorkDays ((Date([uca_arrival_date])), (Date([ca_date])), $(vCAHolidays))

(There was also an extra bracket on the end)

Regards

Anthony

[atoesch]

Hi Anthony,

thank you so much. I knew there was an easy solution (I probably keyed in the ' ' by mixing up two different approaches I found here).

Please find below the solution for a 5/5.5/6 workdays week. I hope you can use it - it was posted by some generous contributor, but unfortunately I do not find his/her post again.

Albert

[anthonyj]

That's awesome. Thanks Albert.