Smartsheet - Formula Help

Bilawal Nisar

I have this spreadsheet, What i want to do is basically in hotel nights - I want to be able to do following.

1. The initial date for Hotel nights - I want to set it lets say May 1 2024. then i want the next rows to it to automatically update to may 2 and may 3 and may 4 and so on.
2. the bottom unparsable cell. I want to do the following - I want it to check that on this hotel Night date is any person checking in and if they are checking in it will mark it X. The checking data can be gathered from 2 left columns that say DATE IN and DATE OUT which has user going from May 1 to May 7th. I want this cell to be able to check those 2 cells and compare it with the date of Hotel NIGHT may 1 meaning user is going to be in hotel so the cell will be marked as X or green.

Any help would be great! I have tried everything.

AravindGP

Hi @Bilawal Nisar

I assume you want the subsequent columns to have the next days. So your formula in the column22 I assume, as the one visible is 21, should have the formula =[Column21]@row+1. You can then click on the bottom right corner of the cell with the formula and drag it to fill the formula. So Column23 will be Column22 + 1 day.

For the second part, you've to write a different formula in each column. For Column21, the formula would be =IF(AND([Column21]$1>=[DATE IN]@row, [Column21]$1<=[DATE OUT]@row), "X")

You will need to modify the formula for Column22, 23, and so on

AravindGP

Hi @Bilawal Nisar

I assume you want the subsequent columns to have the next days. So your formula in the column22 I assume, as the one visible is 21, should have the formula =[Column21]@row+1. You can then click on the bottom right corner of the cell with the formula and drag it to fill the formula. So Column23 will be Column22 + 1 day.

For the second part, you've to write a different formula in each column. For Column21, the formula would be =IF(AND([Column21]$1>=[DATE IN]@row, [Column21]$1<=[DATE OUT]@row), "X")

You will need to modify the formula for Column22, 23, and so on

Bilawal Nisar

Hi @AravindGP I believe that worked perfectly,

However, I want to add below Hotel nights Total nights spent per day. Because we have X lets say for Column21 we have 8 X meaning 8 people have booked. I want that to show, but it is not working no matter what i use. because its a date column, it says Date expected, etc.. i cant get a total night to show up as just 8..

Bilawal Nisar

Got it to work, I put the formula

=COUNTIF([Column21]3:[Column21]114, "X") + ""