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]

What version f Excel are you suing?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Are you using actual ranges, or a named range? The fact that you get a 0 from =(range of end dates) would suggest blank cells

 

[ShoYnn]

I am not using a named range, just used words in this example to simplify some things, and I have Excel 2016. Thanks for fixing me on the Account details, I didn't even know that was a thing, but I totally get how helpful it is!

So yeah, I am using an actual range as it will get filled in as jobs come up. Would a dynamic named range for those 3 ranges fix everything?

 

[Fluff]

Thanks for updating your account.
Can you post the actual formula you are using.

 

[ShoYnn]

=Sumproduct((B$2<=$B$3:$BZ$3)*(B$3>=$B$2:$BZ$2)*(B4=$B$4:$B$11))>1

Where row 2 is the start date row, row 3 is the end date row, and rows 4 through 11 are the names. The jobs get built out sideways, and I used BZ as a generic end column since I was unsure of how many jobs would actually be added. I apologize that I can't get super fancy with these posts, I have to do them from my phone. The government computers don't like us going to forum sites, so I have to do all this from my phone

 

[Fluff]

I apologize that I can't get super fancy with these posts, I have to do them from my phone. The government computers don't like us going to forum sites, so I have to do all this from my phone

Not a problem, i fully understand. However I'm struggling to figure out what your data looks like based on that formula.
Is there any way you could upload a sanitised image?

 

[Fluff]

Thinking about it, is this the sort of thing you are trying to do.

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1
2		01/10/2021	07/10/2021	21/10/2021	28/10/2021
3		10/10/2021	15/10/2021	30/10/2021	05/11/2021
4		A	B	A	H
5		C	G	C	F
6		F	H	G	A
7		H
8
9
10
11
Master

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B4:K11	Expression	=SUMPRODUCT((B$2<=$B$3:$K$3)*(B$3>=$B$2:$K$2)*(B4=$B$4:$K$11)*(B4<>""))>1	text	NO

 

[ShoYnn]

Absolutely yes! Sorry, was at lunch. So as mentioned above, this will continue to get built sideways and the jobs won't necessarily be in any specific date order, so a newly added job could happen before one of the previously added jobs. And the formula does work, but currently it only works because whatever the end date is is always higher than 0 as that is apparently what the start date range I have set calculates out to

 

[Fluff]

Have you tried the formula I suggested in post#7?

 

[ShoYnn]

I just did, and it seems to work about the same. I think dynamic ranges will be the way to go so that there wont be any blanks, the hard part will be getting a good dynamic range for the names as they wont always have the same amount of people assigned