Validate and Exclude Holidays

[alec1982]

Hi Guys,

I've been struggling with this issue for a week and would appreciate any help!

I am in a need to calculate the sum of hours in a week and exclude the holiday hours.

I have the dates stored into a variable as follow:

VHoliday= '1/1/2013','5/27/2013','9/2/2013','11/28/2013'

Within my table i have a date called ([Effective Date])

and within the table script I am using

(40-(NetWorkDays(weekStart([Effective Date]),

           weekEnd([Effective Date]),$(vholidaylist)))*8),0) as Holiday Hours

The error that happens for the date 1/1/2013. There are two effective dates in that week, one occurs on 12/31/2012 and the second occurs on 1/5/2013

The nrs of  hours for the date 1/5/2013 is correct which is 8

but the hours for the effective date 12/31/2012 should be 0 and it shows 8 due to the fact it is in the same week where the holiday occurs.

How can I fix that? I really appreciate any suggestions!

Alec

[alec1982]

Thxs guys for all your help! it is working!