Hotel Rooming List


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
    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?

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • 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å
    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!


    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!


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

    W: | | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!