Crystal Report exclude time entries based on holid

Question

Working on report to determine an employees utilization (utilization is defined as number of billable versus non billable hours in a given report period).

The issue is I need to exclude holidays from my equation. While much has been written on identifying holidays, i need some additional help.

I do not have access to the backend MS. SQL database in order to create a holiday table, so I need to filter dates in the report.

Holidays I need to exclude are

• New Year's Day (January 1)

• Memorial Day (last Monday in May)

• Independence Day (July 4)

• Labor Day (first Monday in September)

• Thanksgiving (fourth Thursday in November)

• 1/2 Day Christmas Eve (December 24)

• Christmas (December 25)

• 1/2 Day New Year's Eve (December 31)

Here are the rules I need to follow:

A recognized holiday that falls on a Saturday will be observed on the preceding Friday.

A recognized holiday that falls on a Sunday will be observed on the following Monday.

currently I have the report working by calculating total available minutes (each workday = 480 minutes) so for normal holidays I need to remove them from total hours worked, and from the total hours available). For the half day holidays I need to remove 240 minutes from total available and to discard any minutes worked above 240). I hope that makes sense.

Answer 1

Create a custom-function named 'Observance' with the following text:

//Correct date to match business rules
Function (Datevar value)

    Select DayOfWeek(value)
    //Sunday; add a day
    Case 1: Date(DateAdd("d", 1, value))
    //Saturday
    Case 7: Date(DateAdd("d", -1, value))
    //no change
    Default: value
    ;

Create a custom-function named 'FullHolidays' with the following text:

//create a list of full-day holidays, calculated dynamically
Function (Numbervar yyyy)

    Datevar Array holidays;
    Datevar holiday;

    //New Year's day
    holiday:=Date(yyyy, 1, 1);
    Redim Preserve holidays[Ubound(holidays)+1];
    holidays[Ubound(holidays)]:=Observance(holiday);

    //Memorial Day (last Monday in May)
    //TODO

    //Independence day
    holiday:=Date(yyyy, 7, 4);
    Redim Preserve holidays[Ubound(holidays)+1];
    holidays[Ubound(holidays)]:=Observance(holiday);

    //Labor Day (first Monday in September)
    //TODO

    //Thanksgiving (fourth Thursday in November)
    //TODO

    //xmas day
    holiday:=Date(yyyy, 12, 25);
    Redim Preserve holidays[Ubound(holidays)+1];
    holidays[Ubound(holidays)]:=Observance(holiday);

    holidays;

Create a custom-function named 'HalfHolidays' with the following text:

//create a list of half-day holidays, calculated dynamically
Function (Numbervar yyyy)

    Datevar Array holidays;
    Datevar holiday;

    //xmas eve
    holiday:=Date(yyyy, 12, 24);
    Redim Preserve holidays[Ubound(holidays)+1];
    holidays[Ubound(holidays)]:=Observance(holiday);

    //new year's eve
    holiday:=Date(yyyy, 12, 31);
    Redim Preserve holidays[Ubound(holidays)+1];
    holidays[Ubound(holidays)]:=Observance(holiday);

    holidays;

Use in a formula like:

If {Table.DateField} IN FullHolidays(Year({Table.DateField})) Then
    0
Else If {Table.DateField} IN HalfHolidays(Year({Table.DateField})) Then
    240
Else
    480

I'll leave the Thanksgiving (and other such holidays) calculation in your capable hands (I'm too busy watching House).