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

Screenshot 2022-11-03 083905.jpg

Find more posts tagged with

Accepted answers

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

All comments

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.