Issues in using IF & AND formula in counting room nights

Options

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

• ✭✭✭✭
edited 11/03/22 Answer ✓
Options

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

• ✭✭
Options

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.

• ✭✭✭✭
edited 11/03/22 Answer ✓
Options

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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!