Count full hours that meet a criteria?

[jockejocke]

Hello!

I wish to enter two points in time and have the computer calculate how many hours fit the met criteria. 

Let's say I enter start time 14:00 and end time 22:00. The criteria in this case is for hours between 18:00 and 22:00. Here I want the computer to return 4, as in 4 hours fit the criteria (18:00-19:00, 19:00-20:00, 20:00-21:00, 21:00-22:00). 

What would a formula look like to accomplish this? What if I want to make an additional criteria valid for specific weekdays, say weekends? What if the times pass midnight and end on another weekday? 

[#2]

@jockejocke 

=(Endtime-Starttime)*24

It is unclear what you mean by "specific workdays".

[#3]

@Detlef Lewin Sorry, but this is not so simple.

I want for the computer to compare the range of hours from users input to a range of hours set in the criteria. As in the example above: User inputs start 14:00 and end 22:00, criteria is valid between 18:00 and 24:00. Therefore 4 hours match with the users input. Computer should output 4. 

With specific weekdays I want the user to also input the weekday and have the computer compare the weekday to another set criteria. Say criteria 1 counts between 18:00-22:00 mon-fri. Criteria 2 counts between 22:00-06:00 mon-fri. Criteria 3 counts 00:00-24:00 sat-sun. Should the user input start 19:00 friday and end 08:00 saturday, computer should output criteria 1: 3 hours, criteria 2: 2 hours, criteria 3: 8 hours. 

Hope this clarifies.  

[#4]

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

[#5]

@Peter Bartholomew Hello! 

Thank you for your suggestion, unfortunately I'm too new to understand how to apply these formulas into a single cell. I'll attach an image so you understand what I mean. In this example I have listed times, criterias, and desired output. Would the formulas you describe produce this? :)