=IF(AND) formula issue

Hi All,

I'm hoping that there may be somebody who can assist with a rather strange formula issue i'm having.

We currently run a Smartsheet for a client who book large hotel room blocks. To ensure we are across the multiple changes that are made and that the allocation we have booked is met, we use the Smartsheet to count based on the check in and check out date as per Image 1

When the user adds the check in and check out date, it will prefill the 1's and then SUM at the very bottom of the Smartsheet across the 100-150 rows so we receive a total SUM for each night. (Image 2)

To pre-fill the 1's, we use the following formula. This reads the check in and checkout date automatically so we don't have to prefill the 1's each time. The Formula below is based on Row 1 under 22nd January...

=IF(AND($[Check In Date]1 <= [Sat 22 Jan]$127, $[Check Out Date]1 > [Sat 22 Jan]$127), 1)

This formula works perfectly until we have a rooming allocation that spreads across two different months. For those individuals wishing to check out on February 1st the formula will simply not calculate if the checkout date is listed as 01/02

In Image 3 I have shown what happens when we cross between two separate months

Image 1

Image 2

Image 3

Thank you in advance if anybody can solve this issue!!

Find more posts tagged with

Accepted answers

All comments

Devin Lee

Hey @Matt Wood

The reason this is happening is because the value the formula is comparing is text so it's just using the first two digits. From the looks of it the column type isn't set to Date which I can understand given the layout. To fix this issue you will need to convert the data into a date so it can include the Month as part of the calculation. It looks like the Check In and Check Out column is only Day and Month so the formula I'm going to provide will have the same issue when you go from one Year to the next. The formula below should solve your issue.

=IF(AND(DATE(2022, VALUE(RIGHT([Check In Date]@row, 2)), VALUE(LEFT([Check In Date]@row, 2))) <= DATE(2022, VALUE(RIGHT([Sat 22 Jan]$127, 2)), VALUE(LEFT([Sat 22 Jan]$127, 2))), DATE(2022, VALUE(RIGHT([Check Out Date]@row, 2)), VALUE(LEFT([Check Out Date]@row, 2))) > DATE(2022, VALUE(RIGHT([Sat 22 Jan]$127, 2)), VALUE(LEFT([Sat 22 Jan]$127, 2)))), 1)

I can't see the [Sat 22 Jan]$127 so you might need to play with that if it's not the same Day/Month. For example it might include the Year so you would need to alter the formula so you are just grabbing the Month for the equation.

Matt Wood

https://community.smartsheet.com/discussion/comment/316097#Comment_316097

Hi, Thank you so much for your response. I can see how that would perfectly however I think the limiation is potentially with Smartsheet.

Am I right in thinking that if we change the 'Check In Date' and Check Out Date' Columns to a Date Format, it won't work as the dates it reads are at the bottom of the sheet and have to be a Text format to ensure the 1's populate correctly above? I've popped another image below to show what I mean.

The Formula is linked to the date across the bottom rows which is a Text. I believe that is why I'm receiving the Invalid Operation Error?

Matt Wood

https://community.smartsheet.com/discussion/comment/316179#Comment_316179

Just as an update. I've created an additional date field in Sheet Summary and tried to point the formula to that and i'm now getting UNPARSABLE rather than Invalid Operation.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.