Count full hours that meet a criteria?

jockejocke 

There are a couple of approaches I use for such problems.

The first is to define effective start and finish times by

= MEDIAN(UserStart, Criterion1Start, Criterion1End)

= MEDIAN(UserFinish, Criterion1Start, Criterion1End)

and then the qualifying hours is

= End - Start

This will not work if UserStart and UserFinish are arrays so then I use

= CHOOSE( 1+(UserStart>Criterion1Start)+(UserStart>Criterion1End),

Criterion1Start, UserStart, Criterion1End)

= CHOOSE( 1+(UserFinish>Criterion1Start)+(UserFinish>Criterion1End),

Criterion1Start, UserFinish, Criterion1End)

and calculation the qualifying hours under Criterion1 as an array.

p.s. There are simpler formulas that work if you are sure that the user hours and the criterion hours will always overlap.  These use MAX/MIN or IF and simple inequalities but they may return negative times if the conditions are not met.