Exclude Dates from Calculation

alec1982 — Mon, 10 Jun 2013 20:56:51 GMT

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

Re: Exclude Dates from Calculation

swuehl — Mon, 10 Jun 2013 23:10:04 GMT

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.