Find the Count of Occurrences of Dates in an Array, Limited to a Date Range

Scenario:

Airtable is used to track visits to a facility.

  • The facility sells 10-pass “punch cards” that expire after 10 uses.
  • If the person doesn’t use the 10 punches before a specified end date, the punch card expires.

I have a base set up with 3 tables:

People
Visits
Punch Cards

People is linked to Visits and Punch cards

I’m pulling an array of all dates a person visited the facility via a rollup function into the People table.

In the Punch Card table, I’m using a lookup to bring in the array.

In the Punch Cards table, I want to return the count of all visits that took place within a specified date range (the valid range of the punch card before it expires).

For example, if a person visits the facility 3 times- in April, May, and Jun, but the date range the punch card is valid is for April, the count should be 1.

Example base here: https://airtable.com/shrmvP5cBpAknjW1f

1 Like

I’m looking for something very similar. Creating a database for a food pantry and they want to track new Visitors each week. I have a an Events table that tracks each Visitor and then I want to count the number of Visitors by Create Date. So Count the number of visitors where the Event Date is the same as the Visitor Create Date.

With no CountIf function, I can’t find a way.