Difference between dates - with no holidays

[#1]

Hi Guys,

I need to calculate the time in days/hours/minutes between two dates, (substracting holidays).

I need to be able to set which day is a working day, and which one is a holiday.

Which should be the best way to achieve that?

Thanks in advance,

Aldo.

[boorgura]

You can get the number of days easily using the NetWorkDays() function.

Where you can list the holidays as well.

Let me know if this is what you are looking for.

[#3]

Its a start, but I still need some changes:

The first one is that I need to set which days are Neto Work days. I also need to insert holidays.

The second thing is that i need to be able to calculate hours also.

Thanks,

Aldo.

I was trying:

let d1 = makedate(2010,1,1);

let d2 = makedate(2010,1,31);

let DiffDays = d2 - d1;

Let DiffWorkDays = NetWorkDays(d1, d2);

load

makedate(2010,1,1) as Date1,

makedate(2010,1,31) as Date2,

$(DiffDays) as GrossDiff,

$(DiffWorkDays) as NetoDiff

autogenerate 1;

[boorgura]

you can include list out the holidays in the NetWorkDays() function.

Once you have the number of days - you can derive the number of hours - right?

[#5]

yes, but how do I tell to the function NetWorkDays() that muy working days are only Mondays and Wednesadys?

[johnw · Accepted solution]

You either list all Tuesdays, Thursdays and Fridays as holidays (impractical), or you don't use networkdays(). So... you need to know the number of Mondays and Wednesdays between two dates? With holidays removed?

I'd probably build a Workdays table with all the workdays you want to count in it. Then count({<Workday={">=YourFirstDate <=YourLastDate"}>} Workday). You'll likely need something more interesting in place of YourFirstDate and YourLastDate, but I'm not sure exactly how you're using this functionality, so I don't know what to put there.

[jonathandienst]

Aldo

John's suggestion should work in your case, but if you want to look at NetwokDays in more detail, I have written a short series on working day calculations in my blog at http://yahqblog.blogspot.com/.

Jonathan

[#8]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/Script-syntax-_2D00_-Calculate-Neto-Working-Time-between-Date1-and-Date2.qvw]

Hi Guys,

Attached an example of what I am trying to achieve.

Thanks in advance,

Aldo.

Below an explanation:

What I need to achieve, is to calculate the number of working days between Date1 and Date2, so if

Date1= 07/10/2010 - Thursday - Working day

08/10/2010 - Friday - NOT Working day

09/10/2010 - Saturday- NOT Working day

10/10/2010 - Sunday

11/10/2010 - Monday - Holiday

12/10/2010 - Tuesday

13/10/2010 - Wednesday

14/10/2010 - Thursday

15/10/2010 - Friday- NOT Working day

16/10/2010 - Saturday- NOT Working day

17/10/2010 - Sunday

Date2= 18/10/2010 - Monday

And if I defined 11/10/2010 as Holiday, then I should get:

Neto Working Days = 6 Days.

[#9]

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.02.89.41/NetoWork3.qvw]

I used John's suggestion with IntervalMatch, and it seems to be working.

Thanks a lot,

Aldo.