Issues in using IF & AND formula in counting room nights

Estela Galicia

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

Screenshot 2022-11-03 083905.jpg

Shubham

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) 

Screenshot 2022-11-03 174851.png

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 

Wolfram

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.

Shubham

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) 

Screenshot 2022-11-03 174851.png

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