Formula to check a box depending on start & end date

jamieyuhasz

Hi all,

I have 2 date columns (start date & end date) and 2 checkbox columns (live & concluded).

I want to have the checkboxes automatically check and uncheck depending on the dates. For example:

I have an event that runs from 12/2 through 12/4. On 12/2 I want the "Live" column checkbox to check and stay checked until the end of 12/4. From there, I want the "Live" checkbox to uncheck and the "Concluded" checkbox to check.

I've been playing around with formulas and cannot get it to work through a date range. I'm using conditional formatting to show active events on the calendar view and felt like this would be the best way to automate this.

Any ideas/is this possible/is there a better way?

Thank you so much in advance!

Kelly Moore

Hey @jamieyuhasz

In the Live checkbox column:

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

In the Concluded checkbox columnn:

=IF(TODAY() > [End Date]@row, 1)

If there are times when either column may be missing a date then you should consider first checking to ensure both dates are there.

=IF(AND(ISDATE([start date]@row, ISDATE([end date]@row), IF(AND(TODAY() >= [start date]@row, TODAY() <= [End Date]@row), 1))

=IF(AND(ISDATE([start date]@row, ISDATE([end date]@row), IF(TODAY() > [End Date]@row, 1))

Will the formulas work for you?

Kelly

Kelly Moore

Hey @jamieyuhasz

In the Live checkbox column:

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

In the Concluded checkbox columnn:

=IF(TODAY() > [End Date]@row, 1)

If there are times when either column may be missing a date then you should consider first checking to ensure both dates are there.

=IF(AND(ISDATE([start date]@row, ISDATE([end date]@row), IF(AND(TODAY() >= [start date]@row, TODAY() <= [End Date]@row), 1))

=IF(AND(ISDATE([start date]@row, ISDATE([end date]@row), IF(TODAY() > [End Date]@row, 1))

Will the formulas work for you?

Kelly