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 counting/not counting scenarios. I have highlighted errors. It is these last 2 scenarios that is eluding me...
With thanks in advance to the Excel community 🙂
3 Replies
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.
