check box within date range

Julien Lopez

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

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.

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. 👍️