#### scottylad2

##### Well-known Member

- Joined
- Feb 2, 2009

- Messages
- 1,919

The daily overtime, when someone works additional hours at the end of their scheduled day i've sussed by myself. And thanks to someone on here and regular contributor mgirvin, ive been able to add on a column where, if someone works a whole days extra overtime it can be calculated. I was having issues because some of the overtime days go from 1 day into the next and calculating the hours was giving me errors but mgirvin posted a solution on his website at Youtube that fixed that for me.

The last part that i need to address, is if someone works a days overtime, then i need to be able to deduct 00:45 mins from their total hours worked. Using the formula Mike posted, i'm getting formula error messages if i try and include the subtraction in the same formula. I've no available space to add new columns on my actual sheet so i really need an informula solution, but one that also does what it can do just now which is count hours correctly when the shift spans day 1 into day 2

Your help would be appreciated

Deek

Excel Workbook | ||||||||
---|---|---|---|---|---|---|---|---|

A | B | C | D | E | F | |||

1 | ||||||||

2 | rostered duty | DATE | Start | End | Overtime | |||

3 | Day off | 01/03/2009 | 06:45 | 17:55 | 11:10 | |||

4 | Day off | 08/03/2009 | 07:45 | 16:00 | 8:15 | |||

5 | Day off | 11/03/2009 | 16:00 | 2:08 AM | 10:08 | |||

6 | Day off | 20/03/2009 | 18:45 | 6:00 AM | 11:15 | |||

7 | Day off | 28/03/2009 | 22:45 | 6:45 AM | 8:00 | |||

8 | Total Hours | 48:48 | ||||||

9 | ||||||||

10 | =MOD(D3-C3,1) | copied down | ||||||

11 | =SUM(E3:E7) | |||||||

12 | E3-E7 Formatted [h]:mm | |||||||

13 | ||||||||

Sheet1 |