Date ranges in formula

[ShoYnn]

I have a sumproduct formula that I am trying to figure out. It is for conditional formatting to ensure the same person does not get scheduled for multiple jobs during the same date range. The formula is:

=sumproduct((start date<=range of end dates)*(end date>=range of start dates)*(name=range of names))>1

It works sometimes, but I did some researching and found something interesting. If you throw each individual part in its own formula you find some neat things. The (start date<=range of end dates) always returns a 0, or false. So I simplified it and made a cell that just had =(range of end dates) and it gave me January 0 1900. Does anyone know why excel does this, and maybe a work around for it? Thanks!

 

[Fluff]

The formula I suggested takes care of blanks as you can see in the mini-sheet I posted.

 

[ShoYnn]

I did see that, and it takes care of the blank name cells, so perhaps a dynamic range won't be needed for that, but the date ranges still calculate out to 0, so the first part where it sees if the one start date is less than the any date in the end date range will always be false

 

[ShoYnn]

Either that or I am super over thinking this!

 

[Fluff]

In what way did my suggestion not work?

 

[ShoYnn]

I mean, it totally worked, it just worked exactly how it did without the B4<>"", where the part where it compares the start date to the end date always comes out false

 

[Fluff]

If it worked, then what's the problem?

 

[ShoYnn]

The other issue I am having is that there is another section right under the top one, and I am trying to have the top cross reference the bottom and vice-versa, but if the top and bottom overlap they only highlight if something on the top matches something on the top. Maybe I am just trying to do too much...

 

[Fluff]

You would be far better off keeping it all in one set of rows, not multiple regions.

 

[ShoYnn]

Yeah, that does seem like the best course of action. I do greatly appreciate your help!

 

[Fluff]

You're welcome & thanks for the feedback.