If cells are empty stop MIN MAX from returning a value.

[ashbee]

Hello, I'm hoping someone can help. I'm using =IF(C4<D4,MAX(0,MIN(D4,upper)-MAX(C4,lower)),MAX(0,upper-C4)+MAX(0,D4-lower)) to calculate the total hours that fall between two times however if cell C4 or D4 are blank my formula still returns a value. Could anyone advise what I can do so that blank cells result in either a zero or blank formula cell?

I use the above to report on overlapping times.

 

[Fluff]

How about
=IF(OR(C4="",D4,""),"",IF(C4<D4,MAX(0,MIN(D4,upper)-MAX(C4,lower)),MAX(0,upper-C4)+MAX(0,D4-lower)))

 

[ashbee]

Thanks for offering a solution - I get a #VALUE! error though.

 

[Fluff]

Typo, it should be
=IF(OR(C4="",D4=""),"",IF(C4<D4,MAX(0,MIN(D4,upper)-MAX(C4,lower)),MAX(0,upper-C4)+MAX(0,D4-lower)))

 

[ashbee]

Thanks Fluff - that worked beautifully and the typo helped me to understand how you've put the code together

 

[Fluff]

You're welcome & thanks for the feedback.