delay calculation with configurable business days

[#1]

Edit by Community Team member:

Please only post in English on the Community unless in a language specific area.

Post translated by Google Translate.

---

Hello everyone,

I need your help 🙂

Indeed, I have to calculate a delay in working days between two dates. So far, nothing too bad because Networkdays function is specifically made for this.

Where it gets complicated is that the days are not closed on Saturday and Sunday, but rather, in my case, on Sunday and Monday.

In other words, I would like a QlikView function that does the equivalent of Excel NB.JOURS.OUVRES.INTL function!

Have you any idea how I can do please?

Thank you in advance,

[maxgro]

Re: Get num of days between 2 dates

maybe with

networkdays(DateStart -1, DateEnd -1)

or with a calendar where you add a Flag (1,0) for your working days

network days = the sum of the flag between start and end date

[swuehl]

Christophe,

there are multiple options like

• create a master calendar with your custom weekends and optional holidays, if you then create reference dates for your intervals and link to your master calendar, getting the number of workdays should be easy

Master Calendar with Working days & Holidays Flag

Creating Reference Dates for Intervals

• Do something like shown here (one of my last answers in this thread):

Network Days

• Calculate the plain date difference, then count and subtract the number of weekend days in that range, like

Re: Count the number of saturdays & sundays between two dates

Every proposed solution surely needs some adaption to your setting, these should just give you some ideas how you can approach a solution.

Regards,

Stefan

[#4]

Stefan, Max,

Thanks for your help.

I prefer the third option (plain date difference, then substract the number of WE days). I haven't succeeded in doing it yet. I guess that my set analysis (on the date range on which I want to focus) is wrong... Anyway, I'll work on it in the next days!

Regards,

Christophe

[swuehl]

For third option, you could try something like

EndDate-StartDate+1-

(Div(WEEKDAY(StartDate-1)+1-StartDate+EndDate,7)+Div(WEEKDAY(StartDate-7)+1-StartDate+EndDate,7))

               as NetWorkingDays