Forum Discussion
Calculating time conflicts
Hi all ! I am so close but no cigar. Please view attached file. I am trying to count shifts that conflict with a specified time frame. All data is imaginary but moving through all potential cou...
The basic formula is
= COUNTIFS(
ShiftStart, "<"&CriterionEnd,
ShiftEnd, ">"&CriterionStart)It is still possible to hit problems with rounding error so you could build in an allowance of 1sec overlap which would be ignored.
As always thankyou Peter. I don't understand the 1 second thing. Would that resolve row 14 ? Also I must thank you for introducing me to defined ranges and references it makes nutting out formulae so much easier. Please find worksheet with your formula.
Kindest regards
In the attached, I have reduced the criteria range by 1 ms at either end to resolve the inequality issue.
Since the solution uses COUNTIFS, it is also possible to perform the calculation for multipl criteria ranges simultaneously by using an array formula.
