check box within date range

Hi community

im looking for a formula to automatically check boxes related to dates from a table containing a start date and a end date

table 1 is the dates source:

table 2 (boxes to be checked)

i'm trying this formula but there is no response:

=IF(COUNTIFS({Sherbrooke - Time Off Tracker name}, [Main Tech]@row, {Sherbrooke - Time Off Tracker start}, >=Start@row, {Sherbrooke - Time Off Tracker end}, <=Start@row) > 0, 1, 0)

thank you for your help

Find more posts tagged with

Accepted answers

Paul Newcome

Try switching the arguments. You want the {Start Date} to be less than or equal to Start@row and the {End Date} to be greater than or equal to Start@row.

All comments

Paul Newcome

Try switching the arguments. You want the {Start Date} to be less than or equal to Start@row and the {End Date} to be greater than or equal to Start@row.

ktcran

You need another condition to check against. It also depends on when and how often the formula is getting run - for example, the formula below can check the box if today's date is between the start and end date, indicating the employee is currently on vacation. Make it a column formula to capture everyone and continuously update (Smartsheet recalculates formula columns whenever the sheet is opened, edited, or refreshed). Also of note: the below formula assumes the output formula and the source data are in the same sheet.

=IF(AND(TODAY() >= [Start Date]@row, TODAY() <= [End Date]@row), 1, 0)

Julien Lopez

Thank you @Paul Newcome! so simple…

Paul Newcome

Happy to help. 👍️

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.