# Timesheet / Rosters issues - HELP

#### jayoharae

##### New Member
Dear all,

I am hoping someone can help resolved some issues with my spreadsheet.

So if D5=12:00PM and E5 = 12:00AM giving me a total of 12 hours

The formula i am using is
=IF(COUNT(D5:E5)=2,IF(MOD(E5-D5,1)*24<=5,MAX(0,ROUND((MOD(E5-D5,1)-30/1440)*24,2)),MOD(E5-TIME(1,0,0)-D5,1)*24),"")

This works a treat but if I could get it not to subtract any time if the rostered hours are less than 4 that would be awesome.
eg D5 = 6:00PM and E5 = 9:00PM it gives me a total of 2.5 hours.

Basically I need it to do the following:
rostered hours =<4 no break
" " >4 but <=9 minus 30mins
" " =>10 minus 1 hour

I have had a play around with it but I can't seem to figure out the correct way of doing this, any help would be fantastic

Jay

### Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Replies
0
Views
95
Replies
3
Views
96
Replies
1
Views
76
Replies
1
Views
127
Replies
6
Views
113

1,118,996
Messages
5,575,433
Members
412,664
Latest member
akilah03