If blank ignore formula

[crompton1980]

Looking for help with the following:-

i have the following formula which looks at a colleagues start and finish times and calculates how many hours he/she has worked within different time/pay brackets. The formula works as it should however if no times are entered into shift start and finish times it is still returning a number instead of blank.

i have uploaded the sheet for you to play around with.

example:-

=MAX((MIN($R$5+($R$5<$R$4),F5+(F5<=E5))-MAX($R$4,E5))*24,0) (this formula is in cell h5 - it looks at the shift band time zone within cells R4&R5 and then looks at the colleague start and finish times in cells E5&F5 then calculates how many hours colleague has worked between those times.)

as you can tell by the attached sheet, Tue through to Sat is displaying data in cells H,I&J (day,late&night prem) when it should be blank.
this appears to be something to do with the first part of the search in column R?

Thanks

Phil

[AliGW]

Something like this in H11:

=IF(COUNTIF(C11:F11,"")=4,"",MAX((MIN($R$5+($R$5<$R$4),F11+(F11<=E11))-MAX($R$4,E11))*24,0))

and in H12:

=IF(H11="","",H11*$U$4)

[crompton1980]

Thanks again, you have been really helpful. that works really well

enjoy your day

Phil

[AliGW]

No worries.

If you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.