4 Replies Latest reply on Aug 17, 2017 11:51 AM by Saurabh Mohanty

# Calculating public holidays between two different dates

Hi everyone,

I'm stuck in a very bad situation with the public holidays calc. I have two different dates "Open Date" and "Close Date" and need to calculate turnaround time between these two dates excluding public holidays and weekends.

What I'm up to so far:

I have calculated the turn around using a date diff.

DATEDIFF('day',[Opened Time (Closed Cases)],[Closed Time])

However, I was able to get it right only after using a LOD

i.e, { FIXED [Case Number]: MAX([Days (Case Turnaround)])}

I don't understand why! Now I need to calculate the public holidays+weekends between open date and closed date and exclude them from case turnaround.

In Order to calculate 'Public Holidays & Weekends', I have a date table in the data base there are following fields:

• all dates in chronological order
• Is weekend flag
• Is weekday flag
• Is national holiday flag

I have used these flags to calculate the count of public holidays and weekends

Public holiday Count:

IF [DATE_DT]>=[Opened Time (Closed Cases)]
AND
[DATE_DT]<=[Closed Time]
AND
[IS_HOLIDAY_NATIONAL_YN]='Y'
THEN 1
ELSE 0
END

Weekend Count:

IF [DATE_DT]>=[Opened Time (Closed Cases)]
AND
[DATE_DT]<=[Closed Time]
AND
[IS_WEEKEND_YN]='Y'
THEN 1
ELSE 0
END

This doesn't work, even after fixing it to case number level. I have gone through all the posts of public holidays but have been unsuccessful so far. Please help.

Thanks

• ###### 1. Re: Calculating public holidays between two different dates

Priyanka!

Thanks for reaching out and sharing your challenge.

Find below link as a "starter"...

• ###### 2. Re: Calculating public holidays between two different dates

Difficulty here is we cannot make relationships between two tables.

This is not fantastic way, but you can do something.

Only brings the result of

- Number of Days between start and end excluding Weekend and Holidays for specific Event

Another concern is ugly parameter name..., anyway

Create Calculated field in the event table.

[EventID_Date]

[Event ID]+"_"+str(Year([Start])*10000+month([Start])*100+day([Start]))+

str(Year([End])*10000+month([End])*100+day([End]))

Create parameter using above [EventID_Date]

Then , decompose [EventID_Date] to [Event Name], [Event_Start], and [Event_End].

[Event Name]

left([event_select],find([event_select],"_")-1)

[Event_Start]

makedate(

int(mid([event_select],find([event_select],"_")+1,4)),

int(mid([event_select],find([event_select],"_")+5,2)),

int(mid([event_select],find([event_select],"_")+7,2))

)

[Event_End]

makedate(

int(mid([event_select],find([event_select],"_")+9,4)),

int(mid([event_select],find([event_select],"_")+13,2)),

int(mid([event_select],find([event_select],"_")+15,2))

)

Now you can calculate number of days for respective selected Event through parameter.

Other way to achieve the goal is that you create all the combination of [Event] x [Date] as different table.

(See Sheet Table_3 case)

Prepare three tables

Table 1 = Event start / end

Table 2 = Holiday Calendar

Table 3 = All [Event] x [Date] combination which Includes all "day" between event start and event end.

As Table 3 primary data make relationships like.

Table 3 Event =link= Table 1 Event

Table 3 Date =link= Table 2 Date

This solution is much easier in case you can easily create Table 3.

Thanks,

Shin

• ###### 3. Re: Calculating public holidays between two different dates

I have a questions in regards to calculating Business Days. I've made a Workdays Days Completed Calculated field using:

DATEDIFF("weekday", [Start Date], [End Date])

- 2 * (DATEPART('week', [End Date]) -DATEPART('week', [Start Date]))

+ (IF DATENAME('weekday',[End Date]) = 'Saturday' OR DATENAME('weekday',[Start Date]) = 'Sunday'

THEN 0 ELSE 1 END)

The above gives me the number of workdays between the Start Date (MIN(date)) and the End Date (TODAY()-1), BUT I need to figure out the number of workdays in the month that I'm currently in.

• ###### 4. Re: Calculating public holidays between two different dates

I don't know if any one has found a solution yet. But I can give what I have done for this situation

I have a holiday excel with dates listed down. I have a custom query which gives me start date and end date.

in order to check  how many holidays are in between a start date and end date my approach will be as follows.

Define a dummy column in excel which acts as a join column and give value as 1 . Then in custom query have a join columnn defined with same value..

Join the two data sources like shown in diagram