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