Timesheet / Rosters issues - HELP

jayoharae

New Member
Joined
Nov 11, 2009
Messages
22
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
 

Some videos you may like

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.

Watch MrExcel Video

Forum statistics

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