Checkbox for consecutive dates (3 or more days and 2 or less)

kaanz

Hi! I have a form that is used to track days a store location is closed. Everytime a store closes, the manager submits a form entry with the location name and the date of the closure. I would like to track consecutive closures for 2 days or less and 3 days or more. For example, Asheville, NC should be checked for 10/18-10/26. Any help would be amazing!

Paul Newcome

Try something like this…

2 Days or Less:

=IF(COUNTIFS([Clinic Name]:[Clinic Name], @cell = [Clinic Name]@row, [Date of Closure]:[Date of Closure], AND(@cell >= [Date of Closure]@row - 1, @cell <= [Date of Closure]@row + 1)) <= 2, 1)

3 Days or More:

=IF([2 Days or Less]@row <> 1, 1)

kowal

Hey,

I would use two formulas for that and store the results in different sheet or in Sheet Summary.

a) countif

=countif([Clinic Name][Clinic Name], "Asheville, NC") - the result of this formula will be 9 and you can keep it in the sheetsummary field called lets' say City1

b) if (for the columns with checkboxes)

=if(City1< = 2, 1, 0)

=if (City1 > = 3, 1, 0)

Let me know if that works.

kaanz

@kowal Thank you! The only issue is I want it to be automated. I want it to search for the Clinic Name@Row in the Clinic Name column and then count if they have Date of Closures within the 2 or less and 3 or more time frames.

Paul Newcome

Try something like this…

2 Days or Less:

=IF(COUNTIFS([Clinic Name]:[Clinic Name], @cell = [Clinic Name]@row, [Date of Closure]:[Date of Closure], AND(@cell >= [Date of Closure]@row - 1, @cell <= [Date of Closure]@row + 1)) <= 2, 1)

3 Days or More:

=IF([2 Days or Less]@row <> 1, 1)

kaanz

PaulNewcome Any idea why it wouldn't have checked random days that fall within the first or last dates? I attached an image of the Asheville, NC closures

Screenshot 2024-10-28 at 10.17.11 AM.png