Datedif of two dates, limited to a specific period

[jessecain]

Hi team,

I have a list of dates for when a number of apartments were occupied, and I'm trying to work out how many days they were occupied during each quarter. E.G.:

Apartment 1
Occupied from 1/1/2008 (date in cell A1) to 27/4/2008 (B1)
Occupied from 27/6/2008 (A2) to 9/9/2009 (B2)

(I'm using the non-American date syntax of dd/mm/yyyy)

So I can manually count that the apartment was occupied for 91 days in Q1 of 2008 (from 1 Jan to 31 Mar), and 31 days in Q2 (1 Apr to 30 Jun). But is there a formula that can do a DATEDIF count of A1:B2, but limited to a date range, e.g., 1/1/2008-31/3/2008?

I hope that makes sense...

Cheers,
Jesse

[samba_ravi]

It is confusing, please attach sample examples with some more example and expected result

[ashishmehra2010]

Hi Jesse,

Please explain

limited to a date range, e.g., 1/1/2008-31/3/2008?

Regards,
AM

[jessecain]

Of course, I'll do my best.

I'd like to know how many days in each Quarter an apartment was occupied. The data I have to work with is the start and end dates of the occupancy. Here's a screenshot of the data range I'm working with.
Screen Shot 2015-04-22 at 2.57.30 pm.png

Here's a screenshot of the eventual data I'd like to fill out. Cell C6 is correct, but it's a simpler calculation. The others are incorrect...
Screen Shot 2015-04-22 at 3.05.50 pm.png

Manually, I can count that the results look like this:
Q4 (1 Oct-31 Dec) in 2007 is 87 days (the occupancy started 6/10/2007, ending at the end of the Quarter)
Q1 (1 Jan-31 Mar) in 2008 is 91 days (occupancy took up the entire Quarter)
Q2 (1 Apr-30 Jun) in 2008 is 31 days (first occupancy ended on 27/4/2008, then empty until a new occupancy started 27/6/2008 to the end of the Quarter)
Q3 (1 Jul-30 Sep) in 2008 is 92 days (occupancy took up the entire Quarter)

But I'd like a formula to calculate it automatically.

Does this clarify things at all?

[samba_ravi]

don't mind instead of image post excel file

[jessecain]

Here's the full Excel file - sorry, I didn't realise I could attach it directly.

Apartment Occupancy example.xlsx

[samba_ravi]

see the attached file two helper columns created for period

The give formula is an array formula

[jessecain]

nflsales - you're amazing!

I kind of understand what's going on, but clearly I need to expand my knowledge of Excel, especially regard arrays. In the meantime, this does exactly what I was after, and I sincerely thank you for it!

[samba_ravi]

You are welcome and thanks for adding reputation