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

[qlikcook]

Hi Maxgro.

Thanks for providing the answer.

I find myself in a similar position and have now realised that I need to rework many of the measurements - but at least now I am certain that it's giving the exact interval.

One question to your formular:
NetWorkDays(d1, d2) -2 +(1-frac(d1))+ frac(d2)

Why subtract 2 from the number of NetWorkDays?
I might have an idea why but I am looking for the actual explanation (in layman terms please).

Thanks,
Johan

[Malini]

Hi, 

your solution works perfectly thank you very much ! Could you explain the last row please ? 

[hadari]

Hi Masimo- I know several years have passed but how can I add calcualtion to count only working  hours?

( 08 am- 5 pm) 

and how can I exclude friday and saturday ( instead saturdays and sundays)? in Networkdays funcion?

hope you will see this massage  🙂

[YS1]

Hello Maxgro,

What exactly does the -2 , does it refer to some holiday as described below

I am using the function and it works in general fine but I also get some negative values,

I tried removing the -2 and still not better

Do you maybe have some other proposal ,

Many thanks 

Auditor cycle 2

Auditor Action Timestamp

Expense Report Timestamp

-1.457504769

2023-04-23 : 8:32 AM

2023-04-22 :7:30:48 PM

YS1

[tw_wipro]

Hi guys,

I might be late to the party but as I saw several times the question about the "-2" in the formula I would like to provide the reason behind that:

networkdays() gives you full days rounded up (=including start and end date) - but we want the exact time. We subtract 2 to get rid of the start and the end date and add the exact times for these two days (frac-part) afterwards.

example:
Start time is friday, 3 p.m.
End time is monday 2 p.m.
expected result: 23 hours

networkday(start,end) -> will give 2 days (full friday and monday)
-2 ->  remove start and end day from the networkdays
+1-frac(start) -> 1 = 24 hours / full day. we subtract the decimal part which equals time already passed on the start day so we get the hours and minutes left on the start day:  24hours - 15 hours =  9 hours
+frac(end) -> we add the hours and minutes that past on the end day: +14 hours

This leads in the end to:
2 - 2 + 9 hours + 14 hours = 23 hours (expected result)

Hope, this helps in case someone stumbles here in the future and wonders why "-2" 🙂

Best regards
Tamara