Bank holiday Exclusion in calculation

[mugdhaojha]

Hi,

I have a requirement where I need to calculate the Resolution timing i.e Date Finish - Date Start.

From this calculation I need to exclude the bank holidays and weekends.

I know I need to use NetworkDays function to exclude the holidays but I am confused as to where to apply this?

Should I use it in the script while loading the Date Finish and Date Start and then use the Set analysis on the chart?

Any idea how I can implement this?

[datanibbler]

Hi,

afaIk, Networkdays() will exclude the weekends, but not bank holidays.

For these - well, what I do is, I have a master_calendar (as a qvs file to INCLUDE) which I use in every one of my apps, and I have a list of all the bank holidays of one year which is fed into that calendar.

I imagine if you have such a list and you have some kind of date in your table, you could also join the list (with a LEFT JOIN, the main table being the primary one) and see where you have a match - those should be the bank holidays.

=> Then you can calculate a flag_field like "workday/ holiday" (or simply 1 and 0) based on that.

HTH

P.S.: This is the third post by me - in a row - that goes into moderation before being published. There must be something wrong with the admins'  random generator or something ...

[jonathandienst · Accepted solution]

You can run a code snippet like this to populate a variable with a list of holidays

This uses the website timeanddate.com, but you could use an Excel file source or database. It also covers several years, so if you only need the current year, you don't need the For loop.

//----------------------------------------------------------------------------------------------------

Set zPriorYears = 5; // Number of prior years to include in calendar

Set zFutureYears = 3; // Number of future years to include in calendar

Let zToday = Today(1); // Date parameter (for unit testing, use other dates here)

//----------------------------------------------------------------------------------------------------

// Get holiday dates from timanddate.com

//----------------------------------------------------------------------------------------------------

Let zMinYear = Year(YearStart(AddYears(zToday - 5, -zPriorYears)));

Let zMaxYear = Year(YearEnd(AddYears(zToday, zFutureYears)));

For zi = zMinYear to zMaxYear

  tmpHolidays:

  LOAD Date(Date#(Date & ' ' & $(zi), 'MMM D YYYY')) As Date

  FROM [http://www.timeanddate.com/holidays/uk/$(zi)]

  (html, codepage is 1252, embedded labels, table is @1)

  Where [Holiday type] = 'Public Holiday';

  ;

Next

tmpConcatHolidays:

LOAD chr(39) & Concat(Date, chr(39) & ',' & chr(39)) & chr(39) As HolidayList

Resident tmpHolidays;

Let vHolidayList = Peek('HolidayList');

DROP Tables tmpHolidays, tmpConcatHolidays;

Now you have a variable vHolidayList that contains the list of holidays. Use this in networkday() like this:

=NetWorkDays(start, finish, $(vHolidayList))

[jonathandienst]