May 19 2019 01:32 PM
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?
May 19 2019 02:28 PM
May 19 2019 02:41 PM
@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.
May 20 2019 01:52 AM - edited May 20 2019 02:30 AM
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.
May 20 2019 02:26 PM
@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? :)