Need to Calculate vacant days btwn 2 dates given multiple move in and move out dates

Hello, this is my first post so I don't know the editique here but here goes.

I have a dorm room with multiple students. I also have an energy bill with start and end dates. I would like to compare each move in and move out array (up to 10) to the start and end dates of the bill to determine how many day there was no one occupying the room. Basically the number of vacant days.

I would like to do this with formulas rather than a macro.

Thanks for any help.

Okay I have attached a sample of what I am working with. The cell in particular I need help with is the Vacant Days cell. On this sheet it is Z 25 to be specific but it will need to be copied to other cells since I will need this for each unit or dorm room.

I have given two different units to show how it works. What I have works for the most part but the second unit shows no vacant days since the first tenant moved in before the bill date begins. The problem is he also moves out before the bill date begins. Also the second tenant moves in after the bill date ends so if you were to delete the first tenant's dates my formula gives me 36 vacant days because it is taking the bill start date and comparing it to the move in date. It should show 30 vacant days since that is all the days between the bill's start and end dates.

Any ideas?

This is a college dorm room so you can have 4 students in a room at one time. Also you may have a student leave and get replaced. Because of this we allowed for up to then entries. Since it is a dorm room and we are looking at charging the tenant based on the amount of time they were in the room during that bill date they could have moved into the room last semester. As long as they are are in the room up to or past the bill end date there should be no days of vacancy.

For the dated the start of the billing period is 11/8/216 and the end of the billing period is 12/8/2016. Ignore column "X", it is used for something else that doesn't pertain to these calculations.

So in summary I am looking for how many days between 11/8/216 and 12/8/2016 there was no student occupying the room.

Hope I was clearer.

Originally Posted by dflak

Are these data values even realistic? The first room is occupied form 11/15/16 to 7/30/17 (6 months from now). However when this person moved in, the room had already been occupied for 18 months (since 8/12/15) and both people cohabitate for the next six months.

According to the data as I am reading it, room 1 is double occupied and room two has been vacant between 10/29/17 and 12/14/16 - am I reading that correctly?

Also the bill date is a single date (12/916) what are the significance of the dates 11/8/16 and 12/8/16? Do you bill on a "fiscal month" running from the 8th to the 7th?

I can't interpret the data and beyond the very basic, "I want to know how many days the room is empty." I do not understand the requirement.

Thanks but that's not quit it. DATEDIF for the bill start and end date only have 30 days. Also the formula needs to take all ten spaces into account in case dates are entered into them. For the first example or unit there are no vacant days since the second tenant moved in Aug 2015 and will move out July 2017

I appreciate you taking a look at it though.

Originally Posted by FlameRetired

Edit Please disregard. This is not correct. Working on another solution.

Not sure I have interpreted correctly. I tried "shadowing" the form in AB8 with this array formula.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

It returns 7 days unoccupied in that date range V4 and Y4. I copied that and pasted to AB25 and returns 31. I believe those are correct.