Exclude Dates from Calculation

[alec1982]

Hi Guys,

I am trying to calculate the utilization of work done during each week of the year and I have the nr of hours that the employee logs in.

so the best way of doing this is by dividing the total logged hours/ Total Hours

The total hours is always 40 except when there is a holiday and here where I get the issue.

within the time sheet row I have the Employee name, Sum(logged Hours), Date(Last day of the week)

How can I determine the holidays based on Date(Last day of the week) and exclude them from the 40 hours.

For example:

Name               Logged Hours          Date(Last day of the week)

John Smith          20                              May 25 2013

Knowing that may 24th is a holiday how can I exclude it from the total hours in this script:

Utili:

Load

     Name,

     [Logged Hours],

     40 as [Total Hours],

     Date(Last day of the week)

From Table XYZ

Thxs,

Alec

[swuehl]

If your working days are Monday to Friday, 8h a day, you can potentially use networkdays() function:

LOAD

...

8 * networkdays([Last day of the week]-6, [Last day of the week], makedate(2013,05,24) ) as [Total Hours],

...

From Table XYZ;

If you have multiple holidays in a table, you can use it like demonstrated by John here.