exclude time entries for company holidays

[jcllc]

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 ti discard any minutes worked above 240).
I hope that makes sense.

 

[Madawc]

I think the least work would be a table of all relevant dates, with a flag saying 'holiday' or not. You might be able to automatically generate it from Crystal, exporting the output as a text file or Excel table.

You can find the day of the week using

Datepart("w", {your.date})

Sunday is 1, Monday 2.

You might also be able to do it as a complex test, or a 'boolian' test for each date that will return 'True' or 'False' and can be tested just by citing the formula field name.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP