Room List with Nightly Occupancy Count

[ChriseeWissee]

Hi All,

I am trying to develop a rooming list with a nightly occupancy count. I need a list that I can input the 'check-in' and 'check-out' dates and it then puts a '1' in the respective nights column. I have designed a spreadsheet in the format that I need it in but I am struggling with the formulas needed to populate the '1'. Attached is a screenshot of the spreadsheet.

Thank you

Chris

https://www.dropbox.com/s/3fizsarkbkbi29p/2018-11-15 (7).png?dl=0

 

[mrshl9898]

How about this?

=COUNTIFS($F8,"<="&H$7,$G8,">="&H$7+1)

 

[jtakw]

Hi,

Please, next time if you're going to post a link, please post a sample file, Not a picture.

This will do what you described:

Book1
	F	G	H	I	J	K	L	M	N	O	P	Q	R
7	Check In	Check out	11/11/2018	11/12/2018	11/13/2018	11/14/2018	11/15/2018	11/16/2018	11/17/2018	11/18/2018	11/19/2018	11/20/2018	11/21/2018
8	11/10/2018	11/15/2018	1	1	1	1
9	11/13/2018	11/19/2018			1	1	1	1	1	1
Sheet361

Cell Formulas
Range	Formula
H8	=IF(AND($F8<=H$7,$G8>H$7),1,"")

Formula copied down and across as far as needed.

 

[ChriseeWissee]

Thank you so much for your help.

 

[ChriseeWissee]

Thank you for your help and I take on board posting a link to the actual document rather than a picture.

 

[jtakw]

You're welcome, welcome to the forum.

Yes, posting a table that can be Copied and Pasted into Excel is preferable, making it easier for helpers to test their solutions with your data.