dates breakdown

[adrienmeszaros]

Good morning all,

I need your help for the following case:

I work for a hotel company and I need to make some statistics.

I have a list of arrival dates and departure dates of guests.

Arrival date: Departure date:
21/05/2015 08/06/2015
24/05/2015 07/06/2015
30/05/2015 02/06/2015
30/05/2015 02/06/2015

From this, I would like to make a recap of the number of rooms that stayed per day. (should be something like this
21/05/2015: 1
22/05/2015: 1
23/05/2015: 1
24/05/2015: 2
25/05/2015: 2
26/05/2015: 2
etc etc...

Can anyone help me in this matter?

Thanks from Belgium!!

Adrien

[samba_ravi]

Please attach a sample file and
if a person arrived on 21-5-15 and departs on 22-5-15 and another person arrived on 22-5-15 and departs on 23-5-15
in this case how many rooms to show 2 OR 1

[mattsmith]

You could make yourself a mini-calendar, and then total up the stays for each day.

With the rows being a unique reference and the arrival and departure dates for each stay, the columns could be the dates. Each cell in the table would have a formula similar to this (adjust as necessary to the position of your table):

Please Login or Register  to view this content.

You could then count up the number of non-blank cells for a particular day using this (applied as an array formula using CTRL+SHIFT+ENTER; adjust as necessary for the position/size of your table):

Please Login or Register  to view this content.

See attached for a working example.
Hotel Calendar Dates.xlsx

[adrienmeszaros]

Please attach a sample file and
if a person arrived on 21-5-15 and departs on 22-5-15 and another person arrived on 22-5-15 and departs on 23-5-15
in this case how many rooms to show 2 OR 1

Dear Siva,

Thanks for your fast answer!

I attach a sample file.
sample file.xlsx

if a person arrived on 21-5-15 and departs on 22-5-15 and another person arrived on 22-5-15 and departs on 23-5-15
==> then the report should say: 21/5/15: 1 and 22/5/15: 1

However, if a person arrived on 21-5-15 and departs on 23-5-15 and another person arrived on 22-5-15 and departs on 23-5-15
==> then the report should say: 21/5/15: 1 and 22/5/15: 2

Thanks a lot!

Adrien

[mattsmith]

in that case you will just need to adjust my first formula to:

Please Login or Register  to view this content.

[adrienmeszaros]

in that case you will just need to adjust my first formula to:

Please Login or Register  to view this content.

Thanks a lot for your replies! This works perfectly.

[samba_ravi]

D2=COUNTIFS($A$2:$A$57,"<="&$C2,$B$2:$B$57,">="&$C2)

Please Login or Register  to view this content.

Here c2 is the date for which you are looking for count the rooms
see attached file

[Richard Buttrey]

Hi, and welcome to the forum.

With your list of incremental dates in E2 starting with 21/5/2015, enter in say F2 as an array formula i.e. with Ctrl Shift Enter

Please Login or Register  to view this content.

[adrienmeszaros]

D2=COUNTIFS($A$2:$A$57,"<="&$C2,$B$2:$B$57,">="&$C2)

Please Login or Register  to view this content.

Here c2 is the date for which you are looking for count the rooms
see attached file

Dear Siva,

I have a question regarding this formula (I attach a sample file)
sample file.xlsx

I have applied the formula successfully. However, when I count the total of room nights in column C, this should normally be equal to the sum in column F. Unfortunately it is not the case here.

Did I do anything wrong?

Thanks again for your kind help!

Adrien

[samba_ravi]

That's what I asked in Post No.2
see the attached file

[adrienmeszaros]

All good now, thanks a lot!!!