asked on

calculating overlapping period of two date ranges (arrayformula)

hi,

Is there a way to provide the following solution in a single cell as an array formula?

https://www.experts-exchange.com/questions/28296518/Excel-calculating-overlapping-period-of-two-date-ranges.html

I want the total number of days that fall within range F. The answer is 191 in the example.

I am using Google Sheets but arrayformulae work similarly to Excel I think (or I hope!)

Thanks

xenium

ASKER

Edittable template online, feel free to post solution here:
https://docs.google.com/spreadsheets/d/1ux-f9DLOnGuO0o3gAPk160C4tOBsKci2HQGPwzg0Evk/

Edit option will be taken off once a solution is complete.

Professor J

this is the formula you can use.

for Microsoft Excel use below with Control Shift Enter

=SUMPRODUCT(MMULT((TRANSPOSE($A$3+ROW(A1:INDEX($A:$A,$B$3-$A$3+1))-1)>=$D$3:$D$8)*(TRANSPOSE($A$3+ROW(A1:INDEX($A:$A,$B$3-$A$3+1))-1)<=E3:E8),ROW(A1:INDEX($A:$A,$B$3-$A$3+1))^0))

Open in new window

for Google Spreadsheet use

=ArrayFormula(SUMPRODUCT(MMULT((TRANSPOSE($A$3+ROW(A1:INDEX($A:$A,$B$3-$A$3+1))-1)>=$D$3:$D$8)*(TRANSPOSE($A$3+ROW(A1:INDEX($A:$A,$B$3-$A$3+1))-1)<=E3:E8),ROW(A1:INDEX($A:$A,$B$3-$A$3+1))^0)))

Open in new window

Control Shift Enter is NOT required

please note that the original formula by barry, is different than this one, becuase lets say if you change the b3 date to 30/07/2013 30 of July then in barry's formula you get 0 while in my formula you get 2 and let me explain why. 1 date overlaps between 30/06/2013 to 15/10/2013 cell D3 toE3 and another dat overlaps between 30/06/2013 to 10/09/2013 D4 to E4. so my formula gives you the correct result . the formula works both with Google Spreadsheet and Excel.

xenium

ASKER

THanks but i think this is wrong. THough the technique could be useful in the answer.

The result that Barry's simple formula gives is the result I want. If the fixed interval is two identical dates then the result should be zero, as the interval length is zero.

Here is a new spreadsheet repeating the problem using my own example, and also including both ProfessorJimJam and Barry's answers.

https://docs.google.com/spreadsheets/d/1_WMNw3dNoaUacIhQT2GEmjMV-VZz4s-l1T9Hddrt0Vc/

Feel free to add your answers here in a new row, but please do not edit the original.

Thanks

xenium

ASKER

ProfessorJimJam, can you give a brief explanation of the formula you provided in case this can be tweeked easily to give the answer? Thanks a lot

Ideally though I'm looking for an array version of Barry's solution if possible, as it seems simpler.

ASKER CERTIFIED SOLUTION

xenium

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

xenium

ASKER

It works.

JRunio

Xenium,

Thoughts on how to add an additional IF statement to this concept?

In this example of Hotel Guests, if there were a 3rd set of values 'Status' set to either 'confirmed' or 'cancelled' and I only wanted to sum of the confirmed reservations...

So this doesn't work.. but i think you can see what im trying to do.

=if((status<>"cancelled")sumif(arrayformula(if(EndPeriod>CheckOutDate,CheckOutDate,EndPeriod)-if(StartPeriod<CheckInDate,CheckInDate,StartPeriod)),">0")

Thoughts?

xenium

ASKER

JRunio,

A couple of years late, but for the record, to add further conditions you can multiply the contents of the arrayformula by a 0-1 variable created with an IF statement:

if((status<>"cancelled",1,0)

add this inside the arrayformula

=sumif(arrayformula(if((status<>"cancelled",1,0)*if(EndPeriod>CheckOutDate,CheckOutDate,EndPeriod)-if(StartPeriod<CheckInDate,CheckInDate,StartPeriod)),">0")