Using IF, COUNTIFS, and HAS functions to locate double booked equipment

Hello!

I am using this formula in a checkbox "helper"

column. =IF(COUNTIFS(Resources:Resources, HAS(@cell, "Gray Van"), [Start Date]:[Start Date], @cell <= [End Date]@row, [End Date]:[End Date], @cell >= [Start Date]@row) > 1, 1)

My hope would be if a piece of equipment is selected in the Resources (dropdown) column in two different rows AND the Start Date and End Date of those rows overlap it checks the box to notify of an overlapped booking.

Currently, it doesn't check the box for the right row when there is an overlap. Can anyone help me find out what I am doing wrong?

Thanks in advance!

Screen Shot 2023-02-25 at 11.29.20 PM.png

Find more posts tagged with

Accepted answers

All comments

Lucas Rayala

Hi @hockenberry2d, does this work?

=IF(COUNTIFS(Resources:Resources, Resources@row, Resources:Resources, <>"", [Start Date]:[Start Date], <=[End Date]@row, [End Date]:[End Date], >=[Start Date]@row)>1,1, 0)

hockenberry2d

Hi @Lucas Rayala, sort of. So the problem is if I have Gray Van and Tilt Trailer selected in that row it won't check the box unless another row has exactly Gray Van and Tilt Trailer. I need it to check the box if EITHER the Gray Van OR Tilt Trailer are in another row that have dates that overlap.

Hopefully that makes sense.

Lucas Rayala

Hi @hockenberry2d, I updated this to give a slightly more compact solution (originally I added in some helper columns which aren't going to buy you much). It's kind of an ungainly problem in almost any environment. These are nested IF statements, I used "Gray Van" and "Tilt Trailer" as examples. You need to repeat these if statements for each resource type. Your cell can only contain 4000 characters, so that's your limit.

=IF(AND(CONTAINS("Gray Van",Resources@row), COUNTIFS(Resources:Resources, Resources@row, Resources:Resources, CONTAINS("Gray Van", @cell), [Start Date]:[Start Date], <=[End Date]@row, [End Date]:[End Date], >=[Start Date]@row))>1,1,

IF(AND(CONTAINS("Tilt Trailer",Resources@row), COUNTIFS(Resources:Resources, Resources@row, Resources:Resources, CONTAINS("Tilt Trailer", @cell), [Start Date]:[Start Date], <=[End Date]@row, [End Date]:[End Date], >=[Start Date]@row))>1,1,

Just keep adding the statements and updating the resource name. The last "IF" statement ends with a "0". Then add end parenthesis until the formulas says you're good. I would create this in a Notepad (or in this case Word would work, because there's no quotes, but generally Word changes the quotes so they can't be read by Smartsheets).

Feasible?

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.