NetWorkDays and count if expression

[samantha92]

Hi guys,

I need to create an expression to count how many assessments are within a range of amount of days open:

• 0-10 days
• 11-30 days
• 31-35 days
• 36-45 days
• 46+ days

At the moment I am calculating this like so:

=Count(if(Today()-AssessmentStartDate>=0 and Today()-AssessmentStartDate<=10, AssessmentStartDate))

This gives me a total of 77 assessments for 0-10 days open range.

However, this total includes weekends and bank holidays, which I want to exclude from this calculation. I am using the NetWorkDays function to try and exclude those dates I do not want, from which I have created the following expression:

=NetWorkDays(AssessmentStartDate,Today(), '24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')

When I try and incooporate the two expressions together it seems to just cancel one expression out (depending on which one I put first) and I am still left with 77 assessments:

=Count(if(Today()-AssessmentStartDate>=0 and Today()-AssessmentStartDate<=10, AssessmentStartDate and NetWorkDays(AssessmentStartDate,Today(), '24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')))

Is there a better way of being able to make this work so that I can exclude weekends and bank holidays whilst doing a count if the amount of days calculated fall within each of the ranges?

Any help would be appreciated, thanks.

[Gysbert_Wassenaar]

Perhaps like this:

=Count({<AssessmentStartDate={">=$(=Date(Today()-10))<=$(=Date(Today()))"}*{"=WeekDay(AssessmentStartDate)>=5"}>} AssessmentStartDate)

[samantha92]

Thanks or the reply, but this just gives me 0?

Am I best hard coding the holidays I want to exclude into my load script instead of using them in my expressions?

**EDIT**

If I load this into my script am I able to create a variable for the NetWorkDays and holidays and then create an expression using them?

LOAD

if(Today()-AssessmentStartDate >=0 and Today()-AssessmentStartDate <=10, '0 - 10 days') AS RangeOne,

if(Today()-AssessmentStartDate >=11 and Today()-AssessmentStartDate <=30, '11 - 30 days') AS RangeTwo,

*;

And then creating a variable like so:

=NetWorkDays(AssessmentStartDate,Today(), '24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')

And then doing something along the lines of this:

=Count(if(RangeOne-vNetWorkDays,RangeOne))

[samantha92 · Accepted solution]

It's ok I'ev figured it out, pretty big expression but it gives me my results I'm after

=if(NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')>=0 and

NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')<=10, '0-10 days',

if(NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')>=11 and

NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')<=30, '11-30 days',

if(NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')>=31 and

NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')<=35, '31-35 days',

if(NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')>=36 and

NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')<=45, '36-45 days',

if(NetWorkDays(AssessmentStartDate,Today(),'24/12/2015','25/12/2015','28/12/2015','31/12/2015','01/01/2016','25/03/2016','28/03/2016','02/05/2016','30/05/2016','29/08/2016','26/12/2016','27/12/2016')>=46, '46+ days',

)))))