find Sum of # of days w/o counting overlapping days twice with multiple paramaters

[civ1979]

I a was able to locate the below formula to count days without including the overlapping counts, however this only to only work for situations where the specific cell range is known. =SUM(1*(MMULT(((MIN(A2:A5)-ROW(A2)+ROW(OFFSET(A2,0,0,MAX(B2:B5)-MIN(A2:A5)+1,1)))>=TRANSPOSE(A2:A5))*((MIN(A2:A5)-ROW(A2)+ROW(OFFSET(A2,0,0,MAX(B2:B5)-MIN(A2:A5)+1,1)))<=TRANSPOSE(B2:B5)),C2:C5)>0))-1

I currently have 1000+ rows of start/end dates that belong to only about 200 unique "files". I am looking to do a vlookup from one sheet, where the file has one occurrence, to the sheet with all of the dates. Any thoughts on how to incorporate that look up?

[mehmetcik]

First Step

Use Text Join to Concatenate your dates.

Insert this into cell E2 using Ctrl Shift Edit and then fill Down

Please Login or Register  to view this content.

That Gives you the List Of Dates for each file.

You can feed that list into a userdefined function using the formula in E2:-

Please Login or Register  to view this content.

Entered using Ctrl Shift Enter

Paste the Userdefined Function in to a Macro Module to make it all work:-

Please Login or Register  to view this content.

This Formula is probably better:-

Please Login or Register  to view this content.

[civ1979]

That doesn't work for some reason. Example: File 'dft' has one date itself with greater than thr return result with that formula of 26 days. Row 20 dates for 'dft' are 2/25-3/28 which is 32 days itself. So that plus the other non overlapping date in row 10 of 7/10-7/10 should have resulted in the total days being 33.

[civ1979]

Would there be an easier way of using this formula below, expanding it to include all data, then adding an additional formula around it to search similar to you have above by looking for the unique id using the file#?

=SUM(1*(MMULT(((MIN(A2:A5)-ROW(A2)+ROW(OFFSET(A2,0,0,MAX(B2:B5)-MIN(A2:A5)+1,1)))>=TRANSPOSE(A2:A5))*((MIN(A2:A5)-ROW(A2)+ROW(OFFSET(A2,0,0,MAX(B2:B5)-MIN(A2:A5)+1,1)))<=TRANSPOSE(B2:B5)),C2:C5)>0))-1

[mehmetcik]

Try this version.

Please Login or Register  to view this content.

[FlameRetired]

That doesn't work for some reason. Example: File 'dft' has one date itself with greater than thr return result with that formula of 26 days. Row 20 dates for 'dft' are 2/25-3/28 which is 32 days itself. So that plus the other non overlapping date in row 10 of 7/10-7/10 should have resulted in the total days being 33.

Mine comes up with 34 for that one. Array-entered.

Please Login or Register  to view this content.

Applying filters to Sheet2 I come up with same. What am I missing?

[civ1979]

Ok, back at this again. The formula listed just above this entry worked last month, however I had to go in to each reference entry and adjust the formula to capture the dates only applicable to each reference number. attached is actual data I am using this on. There are 'begin' and 'end' dates for which I need to capture any dates that there is part of the time there was an open issue(opened/closed). For example, reference# *1205083 would have 99 actual days(excluding duplicates) between begin/end that the file was in an open status. Is there a way to have this formula added to automatically reference ONLY the data within the columns and rows pertaining to JUST the reference number in the applicable row?

[FlameRetired]

Are the rows always going to be grouped contiguously as in the upload?

[FlameRetired]

civ1979,

This seems to work for everything but duplicate dates and maybe row 4. For example I get 17 instead of 16 in row 8. I haven't found a way to resolve the 'look-backs' yet.

Also I get 0 for row 4 ... 4/6/2016 -- 4/6/2016. Is that what you expect?

In F2 and filled down.

=MIN(E2,B2)-MAX(D2,A2)

[civ1979]

yes, I can always have the data sorted by reference# and opened date. The formula you gave works for the specific row of data, however I am trying to count all days for all occurrences of the reference number in column C. So for reference# *1205083, the total of all columns days(excluding duplicate days) is 99.

[FlameRetired]

So for reference# *1205083, the total of all columns days(excluding duplicate days) is 99.

Ok. That's clearer.

[civ1979]

Thanks. Sorry. Any help is appreciated. I am sure there is smart ways than using formulas(like the above function comments), but I am not skilled enough for that yet.

[FlameRetired]

civ1979,

I finally resorted to laying out parallel time lines.

I get 103 for Reference# *1205083, duplicate dates counting as 1.

Would you double check that 99 figure you have?

[civ1979]

I get 99 ----- The date range to search would be 10/31/15-5/26/16(start vs end)


Start Date End Date Reference# Opened Closed
10/31/2015 5/26/2016 *1205083 10/22/2015 12/28/2015 -- 58 --- start date is greater than "opened date" so this is E2-A2
10/31/2015 5/26/2016 *1205083 12/29/2015 1/12/2016 -- 14 --- this is e2-d2
10/31/2015 5/26/2016 *1205083 1/12/2016 1/29/2016 -- 16 --- this is e2-d2(minus 1 since 1/12/16 is part of the count in the row above)
10/31/2015 5/26/2016 *1205083 1/14/2016 1/19/2016 -- --- no count as ALL are duplicates of the row above this
10/31/2015 5/26/2016 *1205083 5/5/2016 5/16/2016 -- 11 --- e2-d2

[FlameRetired]

I'm stumped. Calling for community help.

[millz]

civ1979, you may try running this on your worksheet.

Please Login or Register  to view this content.

There are some ambiguity, especially for those where start and end dates are the same day; should that be 1 or 0 days?