Automatically deducting 45 min breaks from time range

[bchew]

Hi there

I am currently using the following to calculate time range in one column:

=IF(ISNUMBER(FIND("/",C5)),0.5+MOD(SUBSTITUTE(RIGHT(C5,5),"-","")-SUBSTITUTE(MID(C5,FIND("/",C5)+1,5),"-",""),-0.5),0)+0.5+MOD(SUBSTITUTE(MID(C5,FIND("-",C5)+1,5),"/","")-SUBSTITUTE(LEFT(C5,5),"-",""),-0.5)



However am having trouble figuring out how to automatically deduct 45 minute breaks off any shift that is above 5 hours.

I know that it would be much simpler splitting up the shift into two columns but I am interested in finding out if there is a way to modify the current sheet than create a new one.

Any advice is much appreciated.

 

[sericom]

Can you show some sample data of what's in C5

 

[bchew]

Can you show some sample data of what's in C5

11:00-5:00
12:00-3:00

It's a time range column, with column A and B being names etc of staff.

 

[sericom]

=if(isnumber(find("/",c5)),0.5+mod(substitute(right(c5,5),"-","")-substitute(mid(c5,find("/",c5)+1,5),"-",""),-0.5),0)+0.5+mod(substitute(mid(c5,find("-",c5)+1,5),"/","")-substitute(left(c5,5),"-",""),-0.5)-if(if(isnumber(find("/",c5)),0.5+mod(substitute(right(c5,5),"-","")-substitute(mid(c5,find("/",c5)+1,5),"-",""),-0.5),0)+0.5+mod(substitute(mid(c5,find("-",c5)+1,5),"/","")-substitute(left(c5,5),"-",""),-0.5)>5/24,0.75/24,0)

 

[bchew]

=if(isnumber(find("/",c5)),0.5+mod(substitute(right(c5,5),"-","")-substitute(mid(c5,find("/",c5)+1,5),"-",""),-0.5),0)+0.5+mod(substitute(mid(c5,find("-",c5)+1,5),"/","")-substitute(left(c5,5),"-",""),-0.5)-if(if(isnumber(find("/",c5)),0.5+mod(substitute(right(c5,5),"-","")-substitute(mid(c5,find("/",c5)+1,5),"-",""),-0.5),0)+0.5+mod(substitute(mid(c5,find("-",c5)+1,5),"/","")-substitute(left(c5,5),"-",""),-0.5)>5/24,0.75/24,0)

Thank you so very much! You made my day.

 

[bchew]

Another speed hump now. As the cells with the times have to be formatted as "time" cells, my column which does the totalling doesn't work anymore (it used to be just a regular calculation and worked ok then)

=SUM(SUMIF(C5:P5,{"<12:00",">1:00"}))

I have used sum if to omit the columns without values (ie no shift) but it wouldn't present the proper sum now.

Is there a way around this please.