Sum the number of days in one date range which fal

Question

I have two columns of dates. One is the move in date and the other the move out date. I want to figure out how many days the tenant was there during a second date range. for example: how many total "bed days" did we have in the month of July? 7/1/2016-7/31/2016

This function calculates the number of days each tenant was there each month but I would like it if I could get the entire calculation into one cell without creating a dummy column for each month.

=MAX(0,MIN(EOMONTH($B$2,0),I14)-MAX($B$2,H14))

I tried to change a few things and use it as an array function but it is not working. I am very new to array functions so I may be doing it completely wrong.

=SUM(MAX(0,MIN(EOMONTH($B$2,0),I:I)-MAX($B$2,H:H)))

any help is much appreciated! Let me know if you need more info too.

Answer 1

Bad news - you can't use MAX and MIN in an array formula like this because instead of treating H and I as two arrays it just treats them as one big long array and you only get one value out of them.

You also need to add 1 to your original formula because if they moved in on the last day of the month (say) it should still count as one day.

If you replace the MAX and MIN with IF statements you get something like this

=SUM(IF(IF(EOMONTH($B$2,0)<IF(I2:I10="Active",TODAY(),I2:I10),EOMONTH($B$2,0),IF(I2:I10="Active",TODAY(),I2:I10))-IF($B$2>H2:H10,$B$2,H2:H10)<0,0,
IF(EOMONTH($B$2,0)<IF(I2:I10="Active",TODAY(),I2:I10),EOMONTH($B$2,0),IF(I2:I10="Active",TODAY(),I2:I10))-IF($B$2>H2:H10,$B$2,H2:H10)+1))

which has to be entered using CtrlShiftEnter

A useful tip if you are new to arrays is not to include more rows in an array formula than you need to because it will be slow, and test it first on a small number of rows so that you can step through it using Evaluate Formula if you run into trouble.