Sorting out night and Sunday hours

[R2D2]

Hi !

In my job we are working 24/7 days. We have a different salary for hours during night time (i.e. 9 p.m. - 7 a.m.) and for the weekends (sat + sun) and general holidays.

When calculating salaries I should somehow sort out how many hours an employee has worked during night time and during weekends.

The input from each day is currently "Name", "Start_day", "End_day", "Start_hour", "End_hour".

Any suggestions or solutions allready done ?

Thanks in advance

Arttu

Version: Developer v6

Platform: Windows XP

[RalphL]

Do the weekends and holidays also have a day/night rate?

[R2D2]

Hi !

Yes they do. Generally you can add 30% to day time salary.

Arttu

[R2D2]

Somebody ? Anybody ?

Arttu

[Ender]

I'll give it a try.

I'm not sure how this could be done with calc fields but it probably can. I'll try the scripted approach:

Create a time field in there for each category of time you want to count, Weekday Hours, WeekNight Hours, Weekend Hours, and Holiday Hours.

Create a script that checks if the time range starts in, ends in, or overlaps the time range for each of those categories, like:

if[ dayofweek(Start Day) ? 2 and

dayofweek(Start Day) ? 6 and

Start Hour > time(7,0,0) and

Start Hour < time(17,0,0) ]

if[ End Day = Start Day ]

if[ End Hour < time(17,0,0) ]

Set Field [ Weekday Hours, End Hour - Start Hour ]

Else

Set Field [ Weekday Hours, time(17,0,0) - Start Hour ]

End If

...

Keep going through all the possibilities. Not pretty, I know.

[R2D2]

Hi !

Thanks for trying. I have come to same solution, but it's sure not pretty:

If((EndDay > StartDay) and (StartTime <= ThresholdHour2) and (StartTime >= ThresholdHour1) and (EndHour >= ThresholdHour1) and (EndHour <= ThresholdHour2);(Midnight - ThresholdHour2 + ThresholdHour1);

If((EndDay > StartDay) and (StartTime >= ThresholdHour2) and (EndHour >= ThresholdHour1) and (EndHour <= ThresholdHour2);(Midnight - StartTime + ThresholdHour1);

If((EndDay > StartDay) and (StartTime <= ThresholdHour2) and (StartTime >= ThresholdHour1) and (EndHour <= ThresholdHour1);(Midnight - ThresholdHour2 + EndHour);

If((EndDay > StartDay) and (StartTime >= ThresholdHour2) and (EndHour >= ThresholdHour2);(Midnight - StartTime) + ThresholdHour1 + ((Midnight-ThresholdHour2) - (Midnight - EndHour));

If((EndDay > StartDay) and (StartTime <= ThresholdHour1) and (EndHour <= ThresholdHour1);ThresholdHour1 - StartTime + (Midnight - ThresholdHour2) + EndHour;

If((EndDay > StartDay) and (StartTime <= ThresholdHour1) and (EndHour >= ThresholdHour1);(ThresholdHour1 - StartTime) + (Midnight - ThresholdHour2) + ThresholdHour1;

If((EndDay > StartDay) and (StartTime >= ThresholdHour2) and (EndHour <= ThresholdHour1);(Midnight - StartTime + EndHour);

If((EndDay = StartDay) and (StartTime <= ThresholdHour2) and (StartTime > ThresholdHour1) and (EndHour >= ThresholdHour1) and (EndHour <= ThresholdHour2);0;

If((EndDay = StartDay) and (StartTime < ThresholdHour1) and (EndHour >= ThresholdHour1) and (EndHour <= ThresholdHour2);(ThresholdHour1 - StartTime);

If((EndDay = StartDay) and (StartTime < ThresholdHour1) and (EndHour >= ThresholdHour1) and (EndHour >= ThresholdHour2) and (EndHour <= Midnight);(ThresholdHour1 - StartTime) + (EndHour - ThresholdHour2);

If((EndDay = StartDay) and (StartTime <= ThresholdHour1) and (EndHour < ThresholdHour1);(EndHour - StartTime);

If((StartDay = EndDay) and (StartTime >= ThresholdHour2) and (EndHour <= Midnight);EndHour - StartTime;

If((StartDay = EndDay) and (StartTime >= ThresholdHour1) and (StartTime <= ThresholdHour2) and (EndHour >= ThresholdHour2);EndHour - ThresholdHour2;

If((EndDay = StartDay) and (StartTime > ThresholdHour1) and (EndHour < ThresholdHour1);0;"Error"))))))))))))))

This is VERY slow way to do this. Is there any other way to go around ?

Arttu