I am working on an excel task where I had to find out if there are overlapping dates.
In my excel worksheet there are columns for startDate(column D) and EndDate(comun E), from this I had to determine if there is an overlapping date or not.
The following formula determines if this set of startDate & endDate is overlapping with any other date combination in the sheet.
=SUM(COUNTIFS($A$2:$A$100,$A2,$D$2:$D$100,"<"&$D2)-COUNTIFS($A$2:$A$100,$A2,$D$2:$D$100,"<"&$D2,$E$2:$E$100,"<"&$D2),COUNTIFS($A$2:$A$100,$A2,$E$2:$E$100,">"&$E2)-COUNTIFS($A$2:$A$100,$A2,$E$2:$E$100,">"&$E2,$D$2:$D$100,">"&$E2),IF(COUNTIFS($A$2:$A$100,$A2,$D$2:$D$100,$D2)=1,0,1),IF(COUNTIFS($A$2:$A$100,$A2,$E$2:$E$100,$E2)=1,0,1))>0
When this formula is applied to all rows, the invalid rows are seen with true value in InvalidDate column. Logical AND on these values give me the final intended value that indicates if any invalid date is present.
My excel columns look as follows:
Name(A) SD(D) ED(E) InvalidDate
B 01-03-14 04-03-14 TRUE
B 06-03-14 07-03-14 FALSE
B 05-03-14 05-03-14 FALSE
B 05-02-14 05-02-14 FALSE
B 03-03-14 03-03-14 TRUE
B 17-03-14 18-03-14 FALSE
B 21-02-14 02-03-14 TRUE
B 08-03-14 09-03-14 FALSE
B 10-03-14 15-03-14 FALSE
B 16-03-14 16-03-14 FALSE
SD: Start Date
ED: End Date
Name: Value in Column A (Dates are compared for identical values of this column)
But, I cannot have an additional column. So, I need to modify the formula so that it returns TRUE, only if all the Date values are valid.
I am not able to figure out how I do a logical AND operation without using the column. Your ideas and efforts are appreciated.
The formula used to count overlaping date ranges for a single row can be simplified:
=COUNTIFS($E$2:$E$11,">="&D2, $D$2:$D$11,"<="&E2, $A$2:$A$11,A2)
To change it to boolean, add >1 at the end.
To check all rows in a single cell, you can use aggregate functions with array version of the above formula. For example (confirm with Ctrl+Shift+Enter):
=MAX(COUNTIFS($E$2:$E$11,">="&$D$2:$D$11,$D$2:$D$11,"<="&$E$2:$E$11,$A$2:$A$11,$A$2:$A$11))