Forum Discussion
Count of date overlap
Hi! I have a series of start dates and a series of end dates with an order number and location like the following
Order Number | Start Date | Finish Date | Location |
1105 | 5/1/2023 | 5/20/2023 | Location 1 |
1206 | 5/4/2023 | 5/16/2023 | Location 1 |
1252 | 5/15/2023 | 5/18/2023 | Location 1 |
I'm looking to account for all dates between the start and finish, and then get a count of how many orders overlap on each given day.
So in example with this data set 5/15/2023 would have a count of 3 orders active, and 5/17 would have a count of 2 active.
I would want to be able to find the largest count and how many days had that large of a number.
Let's say you have a range named Holidays that lists public holidays.
And let's say you want to compare Net Asset Value (column C) with the previous year.
For row 2, the previous year's value is
INDEX($C$2:$C$5000, XMATCH(1, ($B$2:$B$5000=$B2)*($L$2:$L$5000=WORKDAY(EDATE($L2, -12)+1, -1, Holidays))))
So the increase/decrease is
=C2/INDEX($C$2:$C$5000, XMATCH(1, ($B$2:$B$5000=$B2)*($L$2:$L$5000=WORKDAY(EDATE($L2, -12)+1, -1, Holidays))))-1
2 Replies
- TubolardCopper Contributor
This is perfect, thank you so much! HansVogelaar