Issues in using IF & AND formula in counting room nights
Hi there.
I have a rooming list for an event in which I am trying to work out a formula below to count the number of rooms required per night, based on hotel check-in and check-out dates.
I tried the below formula to return with 1s.
=IF(AND([Hotel Check-in Date]@row <= [28/01/2023]$1, [Hotel Check-Out Date]@row > [28/01/2023]$1), 1, 0)
The above returns an error INVALID OPERATION
Best Answer
-
Hi Estela,
As per my understanding, you want to know how many rooms are booked for particular dates.
First create 2 columns to for your check in and check out dates, now you can use this formula to calculate
=COUNTIF([Hotel Check-in Date]:[Hotel Check-in Date], Date1)
When changing the date in the Date column, it calculates how many Rooms are booked on this date.
Thanks,
Shubham Umale, Smartsheet Engineer, Ignatiuz Software
Answers
-
The reason you are having trouble is that smartsheet can't read your date. Try this:
=IF(AND([Hotel Check-in Date]@row <= DATE(2023, 1, 28), [Hotel Check-Out Date]@row > DATE(2023, 1, 28)), 1, 0)
This returned a 1 for me with the dates being 28.1.23 and 29.1.23
Hope this helps.
-
Hi Estela,
As per my understanding, you want to know how many rooms are booked for particular dates.
First create 2 columns to for your check in and check out dates, now you can use this formula to calculate
=COUNTIF([Hotel Check-in Date]:[Hotel Check-in Date], Date1)
When changing the date in the Date column, it calculates how many Rooms are booked on this date.
Thanks,
Shubham Umale, Smartsheet Engineer, Ignatiuz Software
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!