Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Date Difference Excluding weekends

Greetings All,

I have to calculate date difference between two dates but I have to exclude

"saturday" and "sunday".

For example: '2012-09-14' - '2012-09-07' = 7

but it should give me 5 as it is included saturday and sunday.

Please advise how to achieve it.

Many Thanks!

1 Solution

Accepted Solutions
Miguel_Angel_Baeyens
Not applicable

Re: Date Difference Excluding weekends

Hi,

It returns 6 because, as I mentioned above, it counts both dates specfied as parameters so the calculation is as follows:

2012-09-07 (Friday) = 1 +

2012-09-14 (Friday) = 5

Therefore the total 6 days, including both dates and excluding 2012-09-08 (Saturday) and 2012-09-09 (Sunday).

Hope that helps.

Miguel

6 Replies
Miguel_Angel_Baeyens
Not applicable

Re: Date Difference Excluding weekends

Hi,

Use the function NetWorkDays(), that returns the number of monday to friday excluding saturdays and sundays from the dates speficied, including both:

=NetWorkDays('07/09/2012', '14/09/2012')

Hope that helps.

Miguel

gerry_hdm
Not applicable

Re: Date Difference Excluding weekends

hello you can use

networdays(from,to,holidays)

networkdays ( '2007-02-19', '2007-03-01' )  is  9

networkdays ( '2006-12-18', '2006-12-31', '2006-12-18', '2006-12-18' ) is 8

holidays be passed as string

gruß gerry

Not applicable

Re: Date Difference Excluding weekends

Thanks!

=NetWorkDays('2012-09-07', '2012-09-14')

It is giving me 6 days it should be 5 days.

It is only excluding sunday I guess.

Please advise.

Thanks.

Miguel_Angel_Baeyens
Not applicable

Re: Date Difference Excluding weekends

Hi,

It returns 6 because, as I mentioned above, it counts both dates specfied as parameters so the calculation is as follows:

2012-09-07 (Friday) = 1 +

2012-09-14 (Friday) = 5

Therefore the total 6 days, including both dates and excluding 2012-09-08 (Saturday) and 2012-09-09 (Sunday).

Hope that helps.

Miguel

whiteline
Not applicable

Re: Date Difference Excluding weekends

Hi.

You can always take a paper calendar, marker and see that it's not so hard to write such expression.

Keep in mind that QV has useful functions div() and mod().

In this case you can count any number of days in a week.

Not applicable

Re: Date Difference Excluding weekends

Thanks all!