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!
=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!