Timesheet calculations - Overtime

[DMurray3]

Dear all,


After reviewing a response by Barry Houdini (Overtime calc on Timesheet), I kindly request help in slightly extending the case to consider the following thresholds:


Datime = start 06:00; end 19:00
Night-time = start 19:00; end 06:00
Single OT = start 06:00; end 00:00
Double OT = start 00:00; end 06:00
Shift length = 9:00


Using BH's example sample, with 2 cases, the following results are shown:

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]start[/TD]
[TD]end[/TD]
[TD]std day[/TD]
[TD]std nght[/TD]
[TD]OT Single[/TD]
[TD]OT Dble[/TD]
[TD]note[/TD]
[/TR]
[TR]
[TD]18:00[/TD]
[TD]07:00[/TD]
[TD]1:00[/TD]
[TD]8:00[/TD]
[TD]01:00[/TD]
[TD]03:00[/TD]
[TD]perfect!![/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]20:00[/TD]
[TD]9:00[/TD]
[TD]0:00[/TD]
[TD]02:00[/TD]
[TD]01:00[/TD]
[TD]OT Single s/b 3; OT Dbl s/b 0.[/TD]
[/TR]
</tbody>[/TABLE]







I trust I am not double-posting; if so please accept my apologies.

Your assistance will be highly appreciated.

Kind regards, DMurray3

 

[JALonsdale]

Here's what I would do...

Sometimes Excel does not distinguish 0:00 from one day and 0:00 from the next, unless you put in specifically, 24:00 in which it specifies the next day.

Hope this helps somewhat.

 

[JALonsdale]

Example to try out...

[TABLE="class: cms_table_grid, width: 500, align: left"]
<tbody>[TR]
[TD]start[/TD]
[TD]end[/TD]
[TD]std day[/TD]
[TD]std nght[/TD]
[TD]OT Single[/TD]
[TD]OT Dble[/TD]
[TD]note[/TD]
[/TR]
[TR]
[TD]18:00[/TD]
[TD]07:00[/TD]
[TD]1:00[/TD]
[TD]8:00[/TD]
[TD]01:00[/TD]
[TD]03:00[/TD]
[TD]perfect!![/TD]
[/TR]
[TR]
[TD]08:00[/TD]
[TD]20:00[/TD]
[TD]9:00[/TD]
[TD]24:00[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[DMurray3]

Thanks for the advice.. Unfortunately, changing 0:00 to 24:00 did not solve the reported problem.

Please note that "std day", "std night", "OT Single" and "OT Dble" are # of hours... my problem is that "OT Dble" in the "08:00-20:00" shift is not calculating correctly...

Again, any further assistance will be greatly appreciated..

Kind regards, DMurray3