Time overlap

You'll need to edit the times in columns D and E. Times are numbers. What is in D and E is text. Try changing anything in the format 1:52pm for example to 1:52 PM. That space is important. Excel will read that as time. (Also remove the leading * in cell D2.) Additionally you'll want to add the date (it's a number too) to those dates to account for trans-midnight calculations. This can be done with a helper column inserted into your table or in formula. If done in-formula it could look like this entered into column I and filled down.

Formula:

=IF(AND((D3+A3)<(E2+A2),(D3+A3)>=(D2+A2)),"Overlap Time","")

Does this do what you want?

The only glitches I can see with the following are when all midnights are the times Start and End. For example rows 13:15 for one. In those cases the formula in column K doesn't do anything.

I added two helper columns in I:J. The formula there is:

Formula:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(D2,"pm"," PM"),"am"," AM"),"*","")+$A2

entered in I2 and filled down to the end.

Then in K2 and filled down this:

Formula:

=IF(COUNTIFS($C$2:C2,C2,$F$2:F2,F2)>1,IF(AND(I2<J1,I2>=I1),"Overlap",""),"")

Let us know if this does what you want.

Edit: I forgot to mention. You may want to custom format the adjusted time helper columns to hh:mm AM/PM if you want to retain the leading zeros in hours. It doesn't change the values. It's cosmetic and consistent with what it appears you wanted in the original.

After looking at this again it appears the RO numbers are irrelevant. A person cannot have overlapping times regardless of RO. If I have that right change the formula in column K to

Formula:

=IF(COUNTIFS($C$2:C2,C2)>1,IF(AND(I2<J1,I2>=I1),"Overlap",""),"")

This returns far more overlaps.

You're welcome. Glad to hear it.

If this answers your question please go to the top of your original post, click on Thread tools (upper right) and mark this thread [SOLVED].