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

  • Shubham
    Shubham ✭✭✭✭
    edited 11/03/22 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

  • Wolfram
    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
    Shubham ✭✭✭✭
    edited 11/03/22 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 

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!