Distributings days across intervals

**pacharbo** · 05-31-2007, 08:00 PM

I am trying to creat a formula that looks at 2 Booking date intervals (IN/OUT dates) and calculates and distributes the number of calendar days in several other 2 date intervals (Availability dates). For example:

Booking dates
In date = 1/Sept/07
Out date = 25/oct/07

Availability Dates
01/01/07 - 14/04/07
15/04/07 - 30/06/07
01/07/07 - 31/08/07
01/09/07 - 14/10/07
15/10/07 - 31/10/07
01/11/07 - 14/12/07
15/12/07 - 31/12/07
How many Booking days fall in each Availability date interval .

The problem I have is that the Booking dates can overlap more than 1 Availability interval and the Availability interval dates can overlap calendar months. I have tried some of the date formulas for distributing dates across intervals that are posted on cpearson.com but I can't seem to hit on the right formula.

Can anyone help, Thanks

**NBVC** · 05-31-2007, 11:31 PM

Have a look at the attached....

formula in E1:

=COUNT(IF(ISNUMBER(MATCH(ROW(INDIRECT($A$1&":"&$A$2)),(ROW(INDIRECT(C1&":"&D1))),0)),1))

which was confirmed with CTRL+SHIFT+ENTER not just ENTER. It is an array formula.. The {} brackets appear after you confirm with the CSE key combo.

Note: reconfirm with the CSE key combo after any changes in the formula. Then copy down.

Also, the dates in the attached sheet are formatted to North American standards...so you may have to change the dates to European to see the actual results (if you see errors or nonsensical results).

**d_jitendra** · 06-01-2007, 05:24 AM

Dear,
I want to post my query regarding calculation of day. being new to this sending this you, pl help me in this regard. u can also send me reply on [email protected]
From the formula given on the web site in Excel I have culculated persons age on a specific date.
But again I want to Add or Substacting some years, months and days from the calculated age.

Date of Age as on Life in Hospital. Actual Life lived
Birth as on 30.06.2007 Year -Month- Days Year -Month- Days
Year -Month- Days

01-07-1984 22-11-29 1 - 2 -25 ? - ? - ?

I want to calculate the Actucal Life lived ( Last Col )

Pl help me in this regard.

Thanks in advance....

---- Jitendra Dixit.
PS :- having no other email ID than this sending the mail to yourgoodself. If there is any other email id or other procedure pl let me know ..... Thanks

**pacharbo** · 06-01-2007, 01:06 PM

NBVC....

Your formula works great. I am impressed. When either of the booking dates (IN or OUT dates) are blank, the formula produces 1. Is there a way to test if the cells are blank without altering the formula or have it produce a 0 instead of 1.

Many thanks for your help

**NBVC** · 06-01-2007, 01:28 PM

Originally Posted by pacharbo

NBVC....

Your formula works great. I am impressed. When either of the booking dates (IN or OUT dates) are blank, the formula produces 1. Is there a way to test if the cells are blank without altering the formula or have it produce a 0 instead of 1.

Many thanks for your help

Replace the formula in E1 (in my sample) with the following and reconfirm it with the CSE key combo.

=IF(OR($A$1="",$A$2=""),0,COUNT(IF(ISNUMBER(MATCH(ROW(INDIRECT($A$1&":"&$A$2)),(ROW(INDIRECT(C1&":"&D1))),0)),1)))

**pacharbo** · 06-01-2007, 03:28 PM

I should have thought of that. I am new to Array Formulas and it threw me off guard..

Many Thanks again

**NBVC** · 06-01-2007, 03:57 PM

You're welcome pacharbo.

d_jitendra, please post your question in a new thread... I think it is unrelated to the question in this thread.... Please also explain better what you need. I didn't understand your question or your sample. Thanks.