I'm after a formula that will calculate overtime in 2 ways

[tammorice]

I'm after a formula that will calculate overtime in 2 ways.

1. look at a cell totalling the sum of hours (M51) and if this cell is greater than 10:00hrs then calculate the hrs greater than 10:00hrs (overtime1)

2. look at a range of cells (time) eg E1:E5, and if within that range any cell(s) is greater than 22:00, then calculate the hrs greater than 22:00hrs (overtime2)

two ways to calculate overtime

1. 05:00 - 07:30 = 2:30
08:00 - 12:00 = 4
13:00 - 14:15 = 1:15
15:30 - 19:30 = 4

M51 = 11:45



2. E1, 23:10
E2, 19:00
E3, 17:15
E5, 22:10

overtime2 = 1:20

clear as mud? sorry.

[Kaper]

OK, both values are calculated (rather simple

Please Login or Register  to view this content.

Please Login or Register  to view this content.

and what to do next - take bigger of two? list both in the same cell?

[tammorice]

Kasper. . . These results go into seperate cells for calculating overtime and penalty rates. You're the best!

Thanks a million for the super fast response.