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!