Hotel Rooming List

JN1982

Would someone be able to provide a template or help me with a hotel rooming list? Using Date In & Date out to put an "x" under each room night and total of nights for each date?

Also for a row to show "contracted room nights" then calculate the "variance"

Thank you!

bisaacs

Hi @JN1982,

To check if a date falls between two other dates, you could use a formula like this:

=IF(AND(@cell>=[Date In]@row, @cell<[Date Out]@row), "X")

To calculate the total number of nights, you could just do a simple subtraction formula:

=[Date Out]@row - [Date In]@row

Everything else I'm not really clear on what you mean. What have you tried doing for the contracted room nights or variance?

PhilWatTravelPlaces

I'm looking for something very similar, but in my scenario I have a pre populated grid with a table of dates set across a 2 week period as the header row and line by line (per traveller) I have the dates they're stayin infield with "1".

I would like to find a formular that looks across the row and when it finds the first "1" in the sequence, it returns the date value in the corresponding column header and populates the check in date.

I then want to do the same for "check out" where it finds the final "1" in a sequence and returns the corresponding column heading.

"Check out" is less important as I am already totalling all the "1" in the row for a "total number of nights" column.

so if I can get "check in date" auto populating I can automatically generate the check out by using a [check in date] + [total number of nights]

I've got this working in excel with an INDEX/MATCH combination but SS doesn't seem to accept the same configuration

Andrée Starå

Hi @PhilWatTravelPlaces

I hope you're well and safe!

Can you share some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help.

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!