3

I want to count hours as a time range in given time intervals. It is a bit difficult for me to put what I want to words in English so let me explain waht I want with an example:

  A                 B               C        D          E
1 Start of Fault    End of Fault     Time Ranges        Number of Faults
2 12:00             13:00           00:00    01:00      X
3 12:00             14:00           01:00    02:00      X
4 08:30             23:00           02:00    03:00      X
5 00:00             24:00           03:00    04:00      X
. ...               ...             ...      ...        ...

As you can see, I have starting and ending time of a fault. On C and D coloums, I have time ranges starting from 00:00 and ending at 24:00. Also,

X = How many faults did happen between a time range (00:00-01:00)?

X must scan A and B columns and if a given time range (say 12:00-13:00) is included in any time intervals (say 12:00-13:00, 08:30-23:00), X is the number of intersections between a given time range and time intervals.

I tried to calculate X using this Excel folmula:

=IF(AND($A$2>=C2;$B$2<=D2);1;0)+IF(AND($A$3>=C2;$B$3<=D2);1;0)+IF(AND($A$4>=C2;$B$4<=D2);1;0)+IF(AND($A$5>=C2;$B$5<=D2);1;0)

But this formula works only if time range and time insterval are equal.

Is there a way for me to get value of X by using an Excel formula?

Thank you in advance.

PS: My native language is not English.

  • have a look at countifs function.. – Aprillion Nov 1 '16 at 8:13
  • A few things aren't clear. 1) are cols A and B independent of cols C and D (A&B = fault data, C&D = summary ranges)? 2) Are you counting events or hours (most rows show multi-hour faults; is that counted as 1 fault regardless of duration)? 3) The faults show overlapping times. Is each row considered a different fault regardless of the time frames of other faults? 4) Faults span multiple C:D time ranges. Does a fault get recounted during each C:D period in which it falls (e.g., 4 hr fault counted as 4 faults, 1 in each period)? (cont'd) – fixer1234 Feb 15 '18 at 1:22
  • 5) Is a fault counted if any portion of the fault time falls within the C:D interval, regardless of how short? – fixer1234 Feb 15 '18 at 1:22
  • @fixer1234, 1) Yes. 2) I am counting events. 3) Each row is considered a different fault. 4) Yes, a fault is get recounted if it falls in any C:D period. 5) Yes. – numand May 29 '18 at 6:37
1

Your formula would be:

=COUNTIFS(A:A,"<"&D2,B:B,">="&C2)

The only issue with your "Time Range" is that the start and end times overlap, meaning some values may be counted twice. To fix this, I chose to use A<D but B>=C. If this is not your expected behavior and you want them to be counted twice for the same "minute" then you can change A<D to A<=D.

enter image description here

Place the formula in E2 then fill down.

| improve this answer | |
  • your formula is working as expected. Thank you. As you say, start and end times are overlapping. I changed end times like fallows: 00:59, 01:59, 02:59 ... – numand May 29 '18 at 6:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.