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:

start	end	std day	std nght	OT Single	OT Dble	note
18:00	07:00	1:00	8:00	01:00	03:00	perfect!!
08:00	20:00	9:00	0:00	02:00	01:00	OT Single s/b 3; OT Dbl s/b 0.

<tbody>
</tbody>

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...

start	end	std day	std nght	OT Single	OT Dble	note
18:00	07:00	1:00	8:00	01:00	03:00	perfect!!
08:00	20:00	9:00	24:00

<tbody>
</tbody>

 

[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