exclude time entries following company holiday rules

[jcllc]

Hello all, I have a view that consists of time entries that employees make while working on service orders. Management is developing a compensation plan based on employee utilization (each employee has a percentage goal to reach of billable versus non-billable time).

The problem is company holidays need to be excluded.

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 480 minuters per holiday 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.

 

[drlex]

Is there a question there?



More helpfully, I'd use a calendar table that had the company holidays that fell on weekends 'recognized' on the required weekdays and use that it the reporting. It could be as simple as a date field and a numeric field(0=non-working, 1 = working, 0.5 = half day).

From experience, it's wise to get clarification of how work performed on these weekday holidays is to be treated - is it regular hours or is there an uplift (time and a half, double time etc)? You can bet that someone, somewhere, will log a job card on an 'observed' holiday.

soi là, soi carré

 

[markros]

Just to add on the Calendar table approach, check script from this blog post

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/passing-multiple-ranges-to-stored-proced

It has a script to create Numbers and Calendars table at the top. Once you created that tables, you manually update IsHoliday column in the Calendar table for known years.

Then you use this table in your calculations.

PluralSight Learning Library