Formula for Hotel Check Out and Check Out

BrookeDawn

Hi, I created this hotel spreadsheet. There has to be a way for Smartsheet to create a formula to auto add the check in date and check out day so we arent manually entering. The columns with stars are the check in date.

The columns with checks are check out dates.

The columns with check in and check out dates is old data from last year.

If anyone knows a formula, please let me know!

I also have a unique ID for each person, the row is hidden but its called "unique ID" I was wondering if would need that to make this happen.

Thanks in advance!

Jgorsich

What is the benefit of having seperate columns per day? Why not just a single column for checkin and a single column for checkout?

If you want a visual display afterward, that is one thing, but doing it in reverse - having the multiple columns be the input and the single column be the output, is much more complex.

BrookeDawn

I know its a lot of data, the hotels wanted to know the rooms per night and I didnt know how to compute that, so this was the only way I knew how.

BrookeDawn

I can add a column for check in and check out dates now that i added all that info and manually enter it but i still need to figure out a way to know the per night count. ugh. still new at this!

kowal

hi @BrookeDawn,

I agree with @Jgorsich it would be much more optimal to have those check in / check out dates in 2 columns instead of so many - as formulas are not dynamic and you need to always include new columns in them. But if you are going in this direction I think the only solution would be to use workflow: "Record Date" so you can add two columns extra - one check in data and one checkout date - ech time there will be any chcnages for the row you shall just start the workflow to record the date when it happened.

BrookeDawn

Ok I will add those two columns, manually add the info and try again. Thanks everyone, still learning! The journey is always bumpy as you go. :)

Jgorsich

For the rooms per night, on another sheet have 2 columns: Nights and RoomsOccupied.

In Nights have it be a date column and just drag and fill to cover whatever dates you want addressed.

In RoomsOccupied have a formula like this (curly brackets are references to your primary sheet with names that are hopfully clear):

=countifs({checkIn},<=[Nights]@row,{checkOut},>[Nights]@row)

This will give you a count of everyone that checked in that day or before with a checkout date of the next day or farther in the future (I presume that you are not wanting to count checkouts on that date as the bed will be unoccupied that night).