Network Days

[#1]

Hi,

I have an Opened Date and a Closed Date. I need the exact difference between the two excluding Weekends and Holidays in num format.

Using N/w days gives me value in whole numbers.

Eg:

Open Date	Closed date	Diff
8/9/12 11:08 AM	8/9/12 2:57 PM	0.16

Regards.

[swuehl]

You can try something like this:

Set TimestampFormat = 'M/D/YY hh:mm TT';

LOAD *,

num(interval(

NetWorkDays(DT1+1,DT2-1)

+(

if(NetWorkDays(DT2,DT2),frac(DT2)+(floor(DT1)<>floor(DT2))*-1,1)

-if(NetWorkDays(DT1,DT1),frac(DT1),1)

)

)) as Diff;

LOAD * INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

];

You'll need to add your list of holidays to each single Networkdays() function as third argument (check the Help for more details).

Regards,

Stefan

[#3]

Thanks Stefan.. But the values are one day lesser... The correct value has to be 2.94, but gives 1.94...

Secondly,

if(NetWorkDays(DT2,DT2),frac(DT2)+(floor(DT1)<>floor(DT2))*-1,1)

-if(NetWorkDays(DT1,DT1),frac(DT1),1)

)

should i be adding the holiday list like below in the other 2 n/w days after defining  a variable fr holidays.

if(NetWorkDays(DT2,DT2),frac(DT2)+(floor(DT1)<>floor(DT2))*-1,1,$(vhol))

-if(NetWorkDays(DT1,DT1),frac(DT1),1,$(vhol))

)

[swuehl · Accepted solution]

Thanks Stefan.. But the values are one day lesser... The correct value has to be 2.94, but gives 1.94...

The correct value for which pair of timestamp values?

Secondly,

if(NetWorkDays(DT2,DT2),frac(DT2)+(floor(DT1)<>floor(DT2))*-1,1)

-if(NetWorkDays(DT1,DT1),frac(DT1),1)

)

should i be adding the holiday list like below in the other 2 n/w days after defining  a variable fr holidays.

if(NetWorkDays(DT2,DT2),frac(DT2)+(floor(DT1)<>floor(DT2))*-1,1,$(vhol))

-if(NetWorkDays(DT1,DT1),frac(DT1),1,$(vhol))

)

You need to add the variable as argument to the NetworkDays function, not to the if() statement.

[#5]

Eg:

id	open time	closed time	Diff from qvw	actual diff to be
1234	6/27/12 12:41 PM	7/6/12 4:38 PM	6.164675926	7.164675926
23445	6/29/12 4:45 PM	7/6/12 4:19 PM	3.981863426	4.981863426

[swuehl]

I do get 7.16 and 4.98 with the code I posted above. Could you double check your script? Or post the code snippet you're using?

[#7]

Thank you.. It works fine..

Using the same n/w days, i also need to calculate the difference b/w 2 dates excluding sundays for RegionA and excluding Mondays for RegionB and of course national holidays as well as holidays based on regions. any ideas?

Regards.

[#8]

Any ideas on atleast difference between two days exclusing only Sundays and Holidays??

[swuehl]

Networkdays function will always exclude saturday and sundays.

If your holidays are never on a saturday, you can maybe modify above code to check the weekday for the begin and end date and add the number of saturdays inbetween. But I believe you could have saturdays as holiday, right?

You can also create a calendar with all sundays and holidays flagged. Then... Hm, probably needs a rather complex script to check the calendar for each date in your interval.

Or browse the internet and search for a VBScript or JavaScript function that might help you. I am pretty sure you should be able to find something.

For example, this looks quite promising (for a start):

http://forums.devshed.com/visual-basic-programming-52/calculate-time-difference-between-two-dates-21...

You can create a QV macro code from this and call the functions in the script, just like QV functions.

Just some ideas.

[swuehl]

Another idea:

Set TimestampFormat = 'M/D/YY hh:mm TT';

Set vHol = '41130,41140';

INPUT:

LOAD *, recno() as ID INLINE [

DT1, DT2

8/9/12 11:08 AM,8/9/12 2:57 PM

8/18/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/20/12 01:13 PM

8/17/12 09:20 AM,8/19/12 01:13 PM

6/27/12 12:41 PM,    7/6/12 4:38 PM

6/29/12 4:45 PM,    7/6/12 4:19 PM   

];

TMP:

LOAD  ID,

daystart(DT1)+iterno()-1 as Date,

if(iterno()=1, frac(DT1), 0) as Start,

if(daystart(DT1)+iterno()-1=daystart(DT2), frac(DT2),1) as End

Resident INPUT

while daystart(DT2) >= daystart(DT1)+iterno()-1;

left join (INPUT) LOAD

ID,

sum(End-Start) as Duration

Resident TMP where WeekDay(Date)<6 and not match(Date,$(vHol)) group by ID;

drop table TMP;

in above where clause, you can set your requirements. If you want to exclude sat and sun, use where Weekday(Date)<5.  I put in some few fake holidays in variable $(vHol), but you can build the variable also from a holiday table, there are some samples here in the forum on how to do this.