Networkdays Calculation - Exact Decimals Required

[gileswalker]

Hi Community Forum,

Hope you can help with this one please?

I have what I thought was a simple query, but after scouring the community page, no-one else seems to be asking this one.

I work in a business where we want to calculate the exact number of working days between a 'StartDateTime' and an 'EndDateTime', taking holidays into account.  However because this is driving KPI reporting, we want to be exact. Networkdays isn't doing it for me....here are some details of my experience.

Here is how I currently approached it.  Due to the requirement to manage weekdays and holidays, I started with the following function:

NetWorkDays(date_ordered, DateCompleted, $(vStatHols)) as WorkDaysToComplete

....however of course, I only get a whole number returned using this function.

Example - one order looked like this

date_ordered =           5/01/2015 11:51:36 AM

DateCompleted =      7/01/2015 10:28:13 AM

Networkdays =           3

The actual correctly calculated working days is 1.942 days. 

The difference from a KPI performance perspective is quite significant (1.058 days) and it magnifies once you spread that logic across a year of order processing and customer reporting.

I will also need the function to round up and down the exact result.........so in the above, it would of course be 2 days.

If anyone can assist me in how I can achieve this, I would really appreciate it.  I don't know what other function will allow the calculation of working days and holidays other than networkdays.  Hoping there is an easy solution to get the exact decimal.

Thanks

Giles

[Anonymous]

Hi Giles,

Network days gives difference in days only not with decimal points.

If you are looking for exact days difference with decimal points than you should go with datediff or simple date1-date2 formula.

Thanks,

Pooja

[gileswalker]

Thanks Pooja, but this is not a workable solution for my requirement.  I am using QlikSense, so the datediff function will not work there, and the simple date1-date2 scenario doesn't dynamically take weekends and holiday dates into account.

[vishsaggi]

Did you try using Num(yourexpresssion, '#0.00')

[gileswalker]

Thanks for your suggestion, however on testing this doesn't work either.  It still returns the same number of working days, just with the decimal on the end, e.g. what was showing as 3, is now showing as 3.00

[maxgro]

Try (exclude the first and the last then add the time part of the first and the last)

NetWorkDays(d1, d2) -2 + (1-frac(d1)) + frac(d2)

Maybe you have add a check for holidays in the bold part

[gileswalker · Accepted solution]

Hi Massimo - you are a legend!  This is exactly what I need!!  Wow!  Awesome.  Thanks so much.

Just 1 question on the NetWorkDays function, do you think it will be ok to insert my holidays variable, i.e. to go from your example of:

NetWorkDays(d1, d2) -2 + (1-frac(d1)) + frac(d2)

....to:

NetWorkDays(d1, d2, $(vStatHols)) -2 + (1-frac(d1)) + frac(d2)

Thanks again Massimo

Giles

[maxgro]

Yes, I think you're right, add the holidays to the networkdays function.

[gileswalker]

Massimo, mate you are right on the money my friend.  Its working - great!!! 

Thanks so much.

[vishsaggi]

IF you think few of the replies and of Massimo's are correct mark them helpful.