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]

In a way of hard coding that I have addded the following to the formula:

-if([Effective Date]>'1/1/2013',

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

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

This way works for the Holiday Date 1/1/2013

But for another holiday date such as 27/5/213 it makes issues!

Thxs,

Alec

[#3]

Why don't you create a flag in the calendar so that you know which dates belong to the holidays group... then you just sum the hours with a set analysis that disregards those days through the flag. I think something like:

Sum ( {$<HolidayFlag={0}>} HolidayHours)

I think it's easier that way. Hope it helps.

[alec1982]

The problem here is that the calculation is made based on an effective date that occurs once or twice a week.and not based on a calender where I can set a flag for it.

The effective date occurs every saturday of each week and at the end of each month. so if the end of the month occurs in the middle of the week then I will have two effective dates for that week and here where the issue happens.

[alec1982]

I think that the best way of accomplish this task is by uaing match within the if statament

If Effective Date or Effective Date -1 or Effective Date -2 or Effective Date -3 or Effective Dare -4 or Effective Date -5 match $(VHoliday)

And

Effective Date or Effective Date -1 or Effective Date -2 or Effective Date -3 or Effective Dare -4 or Effective Date -5 <> Previous Effective Date,

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

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

I am not sure how can I do that and would appreciate any help!

Thxs,

Alec

[shree909]

Hi,could u try this

Use this in the script;

Date

((WeekStart(Effective_date)),'MM/DD') &'-'& Date((WeekEnd(Effective_date) - 2),'MM/DD')&' )' AS Weekof

this one will give the  date ranges between the week

40-(

networkdays(Date((WeekStart(Effective_date)),'MM/DD/YYYY') , Date((WeekEnd(Effective_date) - 2),'MM/DD/YYYY'),$(vholidaylist))*8) AS HolidayHours,

This will calcualte the Holiday working hours.

in the weekof if u find any holidays  it  will display the holidy hours respectively.

Thanks

[alec1982]

Hi,

Thanks for your reply but this is giving 40 hours all time which is wrong

40-(

networkdays(Date((WeekStart(Effective_date)),'MM/DD/YYYY') , Date((WeekEnd(Effective_date) - 2),'MM/DD/YYYY'),$(vholidaylist))*8) AS HolidayHours,

Thxs,

Alec

[swuehl · Accepted solution]

alec1982 wrote:

The problem here is that the calculation is made based on an effective date that occurs once or twice a week.and not based on a calender where I can set a flag for it.

The effective date occurs every saturday of each week and at the end of each month. so if the end of the month occurs in the middle of the week then I will have two effective dates for that week and here where the issue happens.

But how do you want to handle the effective dates in the middle of the week (i.e. these must be end-of-month)?

Do you want to skip them completely? Then test for end-of-month:

if( [Effective Date] <> daystart(monthend([Effective Date])),

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

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

as Holiday Hours

[alec1982]

Hi,

Yes these why the error is happening..

when I have a week that has two effective dates those appear in two different rows in the data source..

The effective date is the saturday of each week unless it is the end of the month. if end of the month occur in the middle of the week then that week will have two effective days, the end of month date and the followinfg saturday.

and when I calculate the holidays based on the effective date I want only one of them to pick it up.

for example:

Holiday of Jan 1 2013

the week of this holiday has two effective dates Dec 31 2012 and Jan 5 2013 and we need to have only Dec 31 to exclde the holiday

Another example:

Holiday May 27 2013

The week of this holiday has two effective dates May 31 2013 and June 1 2013 and in this case May 31 should have the holiday and not june 1..

Thxs for your help!

[swuehl]

This should give you the number of holidays per effective date:

=

networkdays(

     rangemax(weekStart([Effective Date]),monthstart([Effective Date]))

     ,rangemin(daystart(weekEnd([Effective Date])),daystart(monthend([Effective Date])))

)

-

NetWorkDays(

                         rangemax(weekStart([Effective Date]),monthstart([Effective Date])),

        rangemin(weekEnd([Effective Date]),daystart(monthend([Effective Date])))

         ,$(vholidaylist) )

)