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
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.6K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 301 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!