Need more help pertaining to the hotel occupancy worksheet

**TheVodkaDude** · 06-10-2013, 04:57 PM

Look at the sample worksheet and in cell B19. Look at the formula, I need to remove the last day of the formula which is the checkout day. I ultimately need to see who's in-house on any given day/ date from the check in and out date ranges BUT I forgot to mention I do NOT need the checkout date to count towards in house or not... Am I making sense? The occupancy should always only be as high as 100% also which takes in account the total in house...

Sample Book.xlsx

**ConneXionLost** · 06-10-2013, 05:12 PM

Have you tried removing the equal sign?

Change this:

=SUMPRODUCT(--($C$2:$C$15<=A19),--($D$2:$D$15>=A19))

to this:

=SUMPRODUCT(--($C$2:$C$15<=A19),--($D$2:$D$15>A19))

Cheers,

**TheVodkaDude** · 06-10-2013, 05:17 PM

Originally Posted by ConneXionLost

Have you tried removing the equal sign?

Change this:

=SUMPRODUCT(--($C$2:$C$15<=A19),--($D$2:$D$15>=A19))

to this:

=SUMPRODUCT(--($C$2:$C$15<=A19),--($D$2:$D$15>A19))

Cheers,

Didn't seem to remove the checkout date from the sum of the in house total

**TheVodkaDude** · 06-10-2013, 05:43 PM

No I just removed the =

**ConneXionLost** · 06-10-2013, 05:20 PM

Did you copy the formula down?

**TheVodkaDude** · 06-11-2013, 07:20 PM

Is anyone else able to help take a stab at this?

**Faylinn** · 07-06-2013, 02:30 AM

Hello TheVodkaDude,

Had to edit this as it just occurred to me that you are a MAC user so none of this may apply!

I'm new to this forum. I've an answer to your question that may be of some use to you. If not then, my stab was in vain!

I have Excel 2010 which may make a difference. I tried two things.

First, I subtracted the dates and subtracted one for the last night occuppied: