To find the overlapped dates

[Tharabai]

Hi All,

Am new to macros.

I want to find the dates which are overlapped considering the fields like ID, type and date ranges.

for the all the ID's in column A, I have to check the overlapping dates.

Can someone help me on this.

-Tharabai

 

[Nebu]

Hi:

Use the following formula

=IF(COUNTIFS($A$2:A3,A3,$H$2:H3,H3,$I$2:I3,I3,$J$2:J3,J3)>1,"Overlap","")

This formula will give Overlap in the Comments column when,ID, From Date, To Date and Type are reapeating.

Thanks

 

[Tharabai]

Thank you for your help. Can you please explain this formula.
And also please let me know whether we can try this using macro instead of formula

 

[Nebu]

Hi:

I do not think you need a macro for this. The formula basically count row-by-row and will give you a count of >1 if the same combinations are repeating. Since the countifs function is wrapped with if condition whenever the countifs gives a count >1 it will give you overlapped else blank, hope I explained it for you.

Thanks