Crystal Reports Holiday Exclussion

[Former Member]

Hi,

I am working on a report that obtains turnaround times for a lab. I am wanting to exclude holidays from the report. The report is already set-up to exclude weekends, but i am having issues figuring out how to also exclude holidays. Below is an example of one of formulas excluding weekends. Any feedback on how to incorporate the exclusion of holidays into this formula would be greatly appreciated.

//finds  the interval between ordered and collection of the specimen.

local numbervar response;

local numberVar weeks;

local datetimevar start;

local datetimevar end;

local numbervar startday;

local numbervar endday;

if not isnull({V_LAB_RESULTS.COLLECTED_DATETIME})

then

(

    if {?Include Weekends}='Yes'

    then datediff("n",{V_LAB_RESULTS.ORDERED_DATETIME},{V_LAB_RESULTS.COLLECTED_DATETIME})

    else

    (

        start:={V_LAB_RESULTS.ORDERED_DATETIME};

        end:={V_LAB_RESULTS.COLLECTED_DATETIME};

        startday:=dayofweek(start);

        endday:=dayofweek(end);

        weeks:=datediff('w',start,end);

        if weeks>0

        then

        (

            response:=weeks*5*24*60;

            start:=dateadd('ww',weeks,start);

        );

        if startday<endday

        then

        (

            if startday=1

            then

            (

                if endday=7

                then response:=response+5*24*60

                else response:=response+datediff('n',cdatetime(cdate(start+1),ctime(0,0,0)),end);

            )

            else

            (

                if endday=7

                then response:=response+datediff('n',start,cdatetime(cdate(end),ctime(0,0,0)))

                else response:=response+datediff('n',start,end);

            );

        )

        else if startday>endday

        then

        (

            if startday=7

            then

            (

                if endday=1

                then response

                else response:=response+datediff('n',cdatetime(cdate(start+2),ctime(0,0,0)),end);

            )

            else

            (

                if endday=1

                then response:=response+datediff('n',start,cdatetime(cdate(end-1),ctime(0,0,0)))

                else response:=response+datediff('n',start,end)-2*24*60;

            );

        )

        else //startday=endday

        (

            if startday in [1,7]

            then response

            else response:=response+datediff('n',start,end);

        );

        response

    );

)

else {@Null}

[abhilash_kumar · Accepted solution]

Hi Luis,

Please see this:

Calculating Work Days Between Two Dates in Crystal Reports

-Abhilash