Count number of unique overlap days - Meeting criteria

[spydey]

I have scoured online for hours now and tried a number of things. Each one seems to get me close, but no cigar.

I am turning to you all for some assistance.

I have 2 sheets.

First sheet, Items, contains 4 columns:

• Item
• Start Date
• End Date
• # Days

The Item is unique and will only appear once.

Second sheet, Events, contains 4 columns:

• Item
• Event Name
• Event Start Date
• Event End Date

The Item in the second sheet could be there multiple times. The combination of Item, Event Name, and Event Start/Ending date will always be unique.

I am trying to get a count of unique # of days for each item in the Items sheet (results to be placed in column "# of Days"), from the Events sheet where the event start and/or end dates for that item in the Events sheet, fall on or between the item's start/end dates from the Items sheet.

I say unique because I don't want to double count days from the events.

Example:

If one event started on 01/01/2018 and ended on 01/10/2018, that would be 10 days (counting the starting day/date).
If a different event but same item, started on 01/05/2018 and ended on 01/15/2018, that would be 11 days (counting the starting day/date)
But in total, between the two events, it would be 15 unique days, not 21 days, due to the overlap of 01/05/2018 to 01/10/2018. I don't want to double count. I hope that makes sense.

I have tried a number of things but can't seem to get exactly what I what/need.

Here are some things I have tried:

https://www.get-digital-help.com/2015/05/18/count-overlapping-days-in-multiple-date-ranges/
https://www.get-digital-help.com/2015/08/12/count-overlapping-days-across-multiple-date-ranges/
https://www.get-digital-help.com/2013/04/26/mmult-function-matrix-multiplication/
https://wmfexcel.com/2014/10/25/how-to-calculate-number-of-overlapping-days-for-two-periods/


I have a sample file shared in my GDrive.

https://drive.google.com/file/d/1HBgr-KITlXvxL_n_5i1GWOj5SwoZeI3I/view?usp=sharing

Any help, suggestion, etc, that anyone can provide is greatly appreciated.

My actual file has several thousand unique items, and several thousand events, so I need something that will allow me to apply it with that in mind.

Again, thank you for your time!!

-Spydey

 

[Nishant94]

Is this what you are looking for?

Book1
	A	B	C	D
1	Item	Start Date	End Date	# Days
2	Shoes	05-01-2018	30-06-2018	177
3	Hat	25-09-2017	27-09-2018	368
4	Socks	09-05-2018	25-10-2018	170
5	Pants	01-01-2018	31-12-2018	46
6	Shirt	13-08-2016	23-01-2018	529
7
Items

Cell Formulas
Range	Formula
D2	{=SUM(--(MMULT((ROW(INDIRECT(B2&":"&C2))>=TRANSPOSE(IF(Events!$A$2:$A$19=A2,Events!$C$2:$C$19)))*(ROW(INDIRECT(B2&":"&C2))<=TRANSPOSE(IF(Events!$A$2:$A$19=A2,Events!$D$2:$D$19))),Events!$C$2:$C$19^0)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[spydey]

@Nishant94

Thank you for the assistance.

I think that something like this will work.

I do have a few more questions, if I may.

My actual data file I am working with doesn't have the Items tab start and end dates in adjoining columns. They are not right next to each other. They are separated by several columns.

I see in the formula you provided (thank you again) that you have referenced Indirect(B2&":"&C2) for the start and end dates.

With there being several columns between them, how can I adjust this part of the formula so that I am capturing the correct start & end date cells/row/columns?

Also, my actual data file, the events tab, the events are not ordered neatly by the events columns. They are all mixed around. Will that matter? Will the formula still function if the events tab data is not in any particular order, meaning the items are not in any order?

Thank you again for your help and assistance.

-Spydey

 

[spydey]

@Nishant94

So I tested out the formula to see if the order of the data in the Events tab mattered, and it doesn't.

I guess it is because of your IF statements in the formula. They are only using the event start and event end dates if the corresponding item from the events tab matches the item from the items tab for the row in the items tab where we are placing the results. Is that right?

I am trying to dissect the formula. It is very similar to one that I found online and tried to use but it required that data be continuous and didn't account for subsets (meaning different items).

The remaining thing I need to figure out is, how to change the formula so that it will capture the correct item start date and end date from the items tab, when those two dates are not side by side. They will always be on the same row but not in columns side-by-side.

Example:

For the Items tab.

Items could be in column B
Start could be in column G
End could be in column W

Any ideas on that?

-Spydey

 

[spydey]

The remaining thing I need to figure out is, how to change the formula so that it will capture the correct item start date and end date from the items tab, when those two dates are not side by side. They will always be on the same row but not in columns side-by-side.

Example:

For the Items tab.

Items could be in column B
Start could be in column G
End could be in column W

I wonder if this even matters?

-Spydey

 

[spydey]

I wonder if this even matters?

-Spydey

Just tested it and it doesn't seem to matter which columns the start & end dates are in from the Items tab, as long as I correctly reference them in within the indirect function. MAGNIFICENT!!!

Would you mind breaking down the formula and explaining it to me? I have been trying to but am a little stuck on a few things. I would really like to understand how it is all working together.

Thank you again!!

-Spydey