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:
The Item is unique and will only appear once.
Second sheet, Events, contains 4 columns:
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
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
Last edited: