Looking for Conditional Formatting formula to alert overlapping times

**lzuke** · 08-16-2012, 11:26 PM

I THINK this should be a simple formula, but I'm not good at formulas.

I have a tiny group of three rows x two columns. The first column is my "In" time and the second column is my "OUT" time.

Into these cells I enter a staffperson's In/Out times scheduled for a single day, and may only use one row or may use all three. In/OUT times can also be entered in any order. So a correct version would be:

This would be okay
9:00 / 11:00
11:00 / 5:00

This would be okay, even though time is not entered in order of earliest to latest
11:00 / 3:00
9:00 / 11:00
4:00 / 5:00

This I'd want CF to highlight something because there is a time overlap
11:00 / 5:00
9:00 / 12:00

- These same 6 cells are repeated for each staff person for every day of a month, but if I can get a formula for one set, I can paste the format throughout.

So I GUESS if any of my IN or OUT times are BOTH <= than the other OUT times in its group, and >= than the other IN times in that group. I THINK that's how it would go.

**Spencer101** · 08-17-2012, 02:30 AM

Hi,

Could you explain a little bit about why the rows wouldn't necessarily be in chronological order?
That bit makes no sense to me and it's the difference between a really simple formula and one that is far from simple if you're not used to dealing with formulas.

S

**lzuke** · 08-17-2012, 08:39 AM

I thought someone might wonder. Yeah, I can see how it might add complexity to the problem.

The third row is designated for time spent at another site. We need the scheduler to see it so she doesn't schedule the staffperson to work at our building during that time, but time entered on the third row is excluded from some of the linked worksheets.

I'm trying to get a TRUE for either of the below scenarios:
--if time entered in any cell is (BOTH >= to A AND <= to B) in either of the two other rows.

and I just thought of this
--if B<= A on any row
(in case scheduler accidentally selects 8am instead of 8pm for an "out" time, for example.)- but perhaps I should do this with data validation. Can you have two types of data validation in a cell? The B cells already have a DV list for selecting times.)

I JUST taught myself how to use data validation and conditional formatting while creating this Staff Schedule, so I'm rather new to the finer points.

Thanks again for taking a look!

**Spencer101** · 08-17-2012, 01:08 PM

Is there any chance you could post an example workbook (an actual Excel file) with a few dummy examples?
I'm still not 100% sure I understand what you're after, although I do have an idea about it.
A couple of dummy examples with brief notes explaining will help no end.