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

[swuehl]

I think you might need to consider the case where d1 and d2 are on the same date differently.

And maybe also if d1 or d2 can be a holiday / weekend.

See this more general approach taking business hours into account (which you can simplify a lot if you don't care about business hours):

Calculate hours between two Date/Time strings

[swuehl]

The mentioned simplified version might look like

Interval(

rangesum(

NetWorkDays(DT1+1,DT2-1,$(vHol)) *1 // 24 hours per workday, for all days inbetween the period, excluding bounderies

,if(NetWorkDays(DT2,DT2,$(vHol)), frac(DT2),0)  // working hours last day

,if(NetWorkDays(DT1,DT1,$(vHol)),1-frac(DT1),0) // working hours first day

,if(NetWorkDays(DT1,DT1,$(vHol)) and floor(DT1)=floor(DT2),-1)  // correct for first equals last day

)

)

[gileswalker]

Thanks Stefan, I will give your simplified version a try. 

Based on your earlier link, I just used the below (in bold) and it was almost ok -but there was an inconsistent result when a DateCompleted matched a vHol date.  Some calculated correctly but some were wrong.....however all instances of DateCompleted matching weekend dates produced accurate results...

Interval((RangeMin(frac(DateCompleted)) - RangeMax(frac(date_ordered)))) + (NetWorkDays(date_ordered, DateCompleted-1, $(vStatHols)))

Anyway I will look at your simplified suggestion now and let you know how I get on.  I appreciate your involvement on helping with this and I will update to "correct answer" once I have concluded it works ok.....

[gileswalker]

Hi Stefan,

I tried your solution, and it doesn't satisfy my requirement, but I think I know why.  I implemented the code, and reconciled the scenario where the DateCompleted falls on a weekend.  The way I read my data output is that your code calculates the exact period from the date_ordered until the weekend starts, which is not quite what I need, and I think this is where I conclude that my explanation has been deficient in detail.  So please let me expand on my requirement, and maybe it will become clearer.

The operation I work in receives client orders and performs a pick, pack & ship function, shipping items to end users/retailers.

Orders are only RECEIVED during Monday-Friday weekdays (causing date_ordered to be triggered),  and orders are normally closed (triggering DateCompleted) during those week days too - this closure may be on the same day or the next day (etc) as the date_ordered, dependent on the desired service.  I am measuring the period between date_ordered and DateCompleted, and need the exact period of time in days, not just Networkdays. 

If the order is to be closed the next working day, it must be noted that Friday's orders would be closed on the following Monday, requiring Saturday and Sunday to be excluded from the working days calculations.  I also have to consider known holidays which can further extend the order processing period.  HOWEVER, exceptionally busy periods could see the business require staff to work weekends and even holiday dates to close those orders, so it is possible to see DateCompleted during weekend and holiday dates.

So the function needs to cover both scenarios ie:

• Where the orders are closed during week days (some spanning a weekend)
• Where orders are closed on weekends and holidays.

If you are able to help further I would appreciate it - I am finding this quite complex to resolve.

[gileswalker]

After deliberating the best means to move forward, I decided to change tactic as the formulas were getting too complex for me.

I also discovered that I had some orders with date_ordered dates that were not on weekdays which made things more complex still.

So I decided that the best thing to do was to 'normalise' the transaction dates, using the 'lastworkdate' function inside an IF statement, effectively pushing all the transactions that happened on weekends of holidays into the next available working day.  That way I could make a more simple formula to get my result.

For anything with date_ordered on weekends, the IF statement and 'lastworkdate' function were used to re-stamp those dates as the next working day at 8am i.e:

Date(LastWorkDate(date_ordered+1, 1,$(vStatHols)) + time('08:00:00', 'hh:mm:ss TT'), 'D/MM/YYYY hh:mm:ss TT') as date_ordered_Corrected

To support this I also used:

Weekday(date_ordered) as date_ordered_Name

Then I used a preceeding load to calculate this:

If(Match(date_ordered_Name, 'Sat', 'Sun') >0, date_ordered_Corrected, If(Match(date_ordered, $(vStatHols)) > 0, date_ordered_Corrected, date_ordered)) as Final_Corrected_Date_Ordered

I also did basically the same for anything 'DateCompleted', although the 'time' function added in was timestamped as the below, meaning that the 'adjusted' DateCompleted was just inside the next available working day:

+ time('00:00:01', 'hh:mm:ss TT')

Then in another preceeding load I scripted the below which gives the exact period of days with any relevant decimals:

Interval((RangeMin(frac(Final_Corrected_DateCompleted)) - RangeMax(frac(Final_Corrected_Date_Ordered)))) + (NetWorkDays(Final_Corrected_Date_Ordered, Final_Corrected_DateCompleted-1, $(vStatHols))) as WorkDaysToCompleteExact

I am pleased to report that this provides the correct data per my requirement.

[ellyodelly]

Massimo- I know this is a year old- but can/would you explain what the -2 is doing? and if the time is in d1 and d2 why should I add it back in?

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

[cspindley]

thanQ for your help with this,

Clive Spindley

NHS

[Keitaru]

Having the same issue however for my case would need to include weekends into the equation as well.

[vsap2000]

Even I am trying to understand the logic behind the expression.

[crispali]

NetWorkDays([Start Datetime], [EndDatetime]
-IF(NetWorkDays([Start Datetime], [Start Datetime])>0,1,0)*interval([Start Datetime] - Floor([Start Datetime]), 'd')
-IF(NetWorkDays([End Datetime], [End Datetime])>0,1,0)*interval(Ceil([End Datetime]) - [End Datetime], 'd')

Assuming [Start Datetime] <= [End Datetime],
would the above work? My approach is to minus the "tail end" hours of the period from the network days.