Smartsheet  Formula Help
I have this spreadsheet, What i want to do is basically in hotel nights  I want to be able to do following.
 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.
 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.
Best Answer

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
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com
Answers

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
Thanks,
Aravind GP Principal Consultant
Atturra Data & Integration
M: +61493337445
E:Aravind.GP@atturra.com
W: www.atturra.com

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

Got it to work, I put the formula
=COUNTIF([Column21]3:[Column21]114, "X") + ""
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 442 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 289 Events
 34 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!