I have the check in date and the check out date for a large group. I would
like to see how many rooms are used per day for the period.
Column A = Check in date (ex: 2-feb)
Column B = Check out date (ex: 10-feb)
How can I tell how many rooms were used on 6-feb, how many on 7-feb, etc?

Thanks so much for your help!

Put the date of interest (e.g. 6-feb-2008) in D2 then in E2 use this formula

=SUMPRODUCT((A$2:A$100<=D2)*(B$2:B$100>=D2))

You can copy the formula down to calculate for more date in column D

"peabody" wrote:

> I have the check in date and the check out date for a large group. I would
> like to see how many rooms are used per day for the period.
> Column A = Check in date (ex: 2-feb)
> Column B = Check out date (ex: 10-feb)
> How can I tell how many rooms were used on 6-feb, how many on 7-feb, etc?
>
> Thanks so much for your help!

I tried this and it does not work. Close, but not quite. As you can see
from below, it returns 4 for 2/4/07 when the correct number of rooms for that
night is 2.
A B D E
2/1/2007 2/4/2007
2/1/2007 2/5/2007 2/4/2007 4
2/3/2007 2/5/2007
2/2/2007 2/4/2007



1-Feb 2-Feb 3-Feb 4-Feb 5-Feb
1 1 1
1 1 1 1
1 1
1 1


"daddylonglegs" wrote:

> Put the date of interest (e.g. 6-feb-2008) in D2 then in E2 use this formula
>
> =SUMPRODUCT((A$2:A$100<=D2)*(B$2:B$100>=D2))
>
> You can copy the formula down to calculate for more date in column D
>
> "peabody" wrote:
>
> > I have the check in date and the check out date for a large group. I would
> > like to see how many rooms are used per day for the period.
> > Column A = Check in date (ex: 2-feb)
> > Column B = Check out date (ex: 10-feb)
> > How can I tell how many rooms were used on 6-feb, how many on 7-feb, etc?
> >
> > Thanks so much for your help!

It is assuming the check out date is counted as occupied. Change the >= to >


On Sun, 20 Jul 2008 17:59:01 -0700, peabody <peabody@discussions.microsoft.com>
wrote:

>I tried this and it does not work. Close, but not quite. As you can see
>from below, it returns 4 for 2/4/07 when the correct number of rooms for that
>night is 2.
>A B D E
>2/1/2007 2/4/2007
>2/1/2007 2/5/2007 2/4/2007 4
>2/3/2007 2/5/2007
>2/2/2007 2/4/2007
>
>
>
>1-Feb 2-Feb 3-Feb 4-Feb 5-Feb
>1 1 1
>1 1 1 1
> 1 1
> 1 1
>
>
>"daddylonglegs" wrote:
>
>> Put the date of interest (e.g. 6-feb-2008) in D2 then in E2 use this formula
>>
>> =SUMPRODUCT((A$2:A$100<=D2)*(B$2:B$100>=D2))
>>
>> You can copy the formula down to calculate for more date in column D
>>
>> "peabody" wrote:
>>
>> > I have the check in date and the check out date for a large group. I would
>> > like to see how many rooms are used per day for the period.
>> > Column A = Check in date (ex: 2-feb)
>> > Column B = Check out date (ex: 10-feb)
>> > How can I tell how many rooms were used on 6-feb, how many on 7-feb, etc?
>> >
>> > Thanks so much for your help!
--
Dave Mills
There are 10 type of people, those that understand binary and those that don't.