Count whether a particular date is between two dates

Hi there,

I am hoping I can get some help with this.

I am trying to calculate the number of rooms that will be needed on each night of this event I'm running. I need to look at the date range (checkin/checkout) for each guest to see if that particular date falls between in. I would like to repeat this for every day of the event. The guest list will get very long.


Please help! Attachement below

test.xlsx

One way to go:

F12=SUMPRODUCT(($B$3:$B$5<=F2)*($C$3:$C$5>F2)) and drag to the right.

I think the best way to do this is to create a formula inside your grid of dates, have a conditional format to automatically fill those cells with colour, and then simply count the values (or sum them) below.

The following formula returns a 1 if the guest will be staying that night, and a 0 if they won't.

Please Login or Register  to view this content.

See the attached file as an example. Hope that helps!

test (1).xlsx

Hi,

As pepe said, you can get the total number of rooms using SUMPRODUCT, though I would start it on 8 Oct, by putting this in E12:

Formula:

Please Login or Register  to view this content.

You might also want your table to fill in automatically, using Conditional Formatting. Select your whole table (not the date row - so E3:O9 in your test file), click Conditional Formatting, click 'New Rule' then 'Use a formula to determine which cells to format'. Put this in the formula box:
=AND(E$2>=$B3,E$2<$C3)
Then choose the format you want (green fill). Now your table will colour the booked dates automatically.
(I don't know why you had two cells containing '1' with red fills. If you need this to happen as well, use Cond Form again, choose 'Highlight Cells Rules' then 'Equal To...', enter '1' and choose a Red Fill.)

Hope that helps.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.