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